Export Excel to 2 tables Access ??????
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Export Excel to 2 tables Access ??????

Hybrid View

  1. #1
    Join Date
    Sep 2012
    Posts
    16

    Export Excel to 2 tables Access ??????

    I have a problem about INSERT INTO with 2 tables access.
    My Excel file have a few paramater : StudentID, ClassID,ClassName,FirstName,LastName
    My Access have 4 tables : Student, Class, Status,UserName
    Student (ID Number, StudentID,ClassID, FirstName,LastName,UserName,Status,Date)
    Class(ClassID,ClassName)

    I want to insert data from excel to access with some requirement:
    Field UserName show the user is logged in
    Field Date show date/time which the user is logged in
    Field Status is have 2 condition:
    First, if you have updated StudentID, the Status is Active and the rest of parameter is update.
    Second, if you do not update StudentID, you update all of them.

    I hope everybody help me to resolve my lesson!!!!!!!!!!
    Thanks for everybody!!!!!!!!!!

  2. #2
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,830

    Re: Export Excel to 2 tables Access ??????

    Are you using VB6 or are you coding in Excel or Access?

    What code have you written and what problem are you running into?
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,982

    Re: Export Excel to 2 tables Access ??????

    Sounds like HOMEWORK to me...
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  4. #4
    Join Date
    Sep 2012
    Posts
    16

    Re: Export Excel to 2 tables Access ??????

    On Error Resume Next
    Dim ex As New Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim i As Long: i = 1
    Dim con As New ADODB.Connection
    Dim con1 As New ADODB.Connection

    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TEST1.mdb;Persist Security Info=False"
    con1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TEST1.mdb;Persist Security Info=False"

    Set wb = ex.Workbooks.Open(tb_upload.Text, , True)
    Set ws = wb.Worksheets(1)
    Do: i = i + 1

    sUser = (String)Login.tbUser.Text
    sdate = Format(Now(), "dd/mm/yyyy hh:mm:ss AM/PM")
    con.Execute "INSERT INTO SV(MSSV,MaLop,Ho,Ten,HanhKiem,User,Status,Date) VALUES ('" & ws.Range("A" & i).Value & "', '" & ws.Range("B" & i).Value & "','" & ws.Range("D" & i).Value & "','" & ws.Range("E" & i).Value & "','" & ws.Range("F" & i).Value & sUser(Login.tbUser.Text) & ws.Range("G" & i).Value & ws.Range("H" & i).Value & sdate(Format(Now(), "dd/mm/yyyy hh:mm:ss AM/PM")) & ws.Range("I" & i).Value & "')"
    con1.Execute "INSERT INTO Lop(MaLop,TenLop) VALUES ('" & ws.Range("B" & i).Value & "', '" & ws.Range("C" & i).Value & "')"

    Loop Until ws.Range("A" & (i + 1)) = Empty

    ex.Quit
    Set ex = Nothing
    con.Close
    con1.Close


    I am stuck in how to insert user and date when the user is logged in and show the date/time when the user is updated.

    I appreciate for your help !!!!!

  5. #5
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,722

    Re: Export Excel to 2 tables Access ??????

    You should first remove (or outcomment) the On Error Resume Next statement and then run the program to see where an actual error occurs.
    With the On Error Resume Next you never find out anything.
    After having done so, please report the error and explain what is happening or is not happening.

  6. #6
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,830

    Re: Export Excel to 2 tables Access ??????

    It looks like there are a few issues.

    1: As mentioned above do not use On Error Resume Next
    2: You are using 2 connections, no need to use more than 1 as they are both pointed to the same database
    3: Your are not using delimiters around your date value
    4: It looks as though the value you are passing for date is not a valid date
    5: Your first insert is trying to insert 8 fields but only looks to have 5 values
    Last edited by DataMiser; September 17th, 2012 at 08:01 AM.
    Always use [code][/code] tags when posting code.

  7. #7
    Join Date
    Sep 2012
    Posts
    16

    Re: Export Excel to 2 tables Access ??????

    My Excel have just 5 value : MSSV,MaLop,TenLop,Ho,Ten,HanhKiem
    My table Access have just 10 value : ID Number, MSSV,MaLop,Ho,Ten,HanhKiem,User,Status,Date.
    I am trying to fix INSERT Statement but it is not still working.
    Dim ex As New Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim i As Long: i = 1
    Dim con As New ADODB.Connection
    Dim con1 As New ADODB.Connection

    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TEST1.mdb;Persist Security Info=False"
    con1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TEST1.mdb;Persist Security Info=False"

    Set wb = ex.Workbooks.Open(tb_upload.Text, , True)
    Set ws = wb.Worksheets(1)
    Do: i = i + 1

    con.Execute "INSERT INTO SV(MSSV,MaLop,Ho,Ten,HanhKiem,User,Status,Date) VALUES ('" & ws.Range("A" & i).Value & "', '" & ws.Range("B" & i).Value & "','" & ws.Range("D" & i).Value & "','" & ws.Range("E" & i).Value & "','" & ws.Range("F" & i).Value & tenUser & "','" & "0" & Ngaygiodangnhap & "')"
    con1.Execute "INSERT INTO Lop(MaLop,TenLop) VALUES ('" & ws.Range("B" & i).Value & "', '" & ws.Range("C" & i).Value & "')"

    Loop Until ws.Range("A" & (i + 1)) = Empty

    ex.Quit
    Set ex = Nothing
    con.Close
    con1.Close

    First, I am stuck in Status Field in INSERT Statement because I want to use Find Statement .
    If MSSV is found, the Status is shown Deactive
    Else MSSV is not found, the Status is shown Active
    Second, user and date in INSERT Statement does not working.

    I appreciate for your help !!!!!!!

  8. #8
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,830

    Re: Export Excel to 2 tables Access ??????

    This would still apeear to not be a valid date
    Code:
    & "0" & Ngaygiodangnhap
    Also Access uses # signs for date delimiters

    I do not work with Excel so I do not know what those range() calls may be returning.
    You should create a variable to hold your SQL Insert strings and then use debug.print to print out the content of the sql string once it has been built so you can see exactly what you are sending in your query. You could copy and paste the output string to a post so others can see what you are executing and maybe see the issue better.
    Always use [code][/code] tags when posting code.

  9. #9
    Join Date
    Sep 2012
    Posts
    16

    Re: Export Excel to 2 tables Access ??????

    tenUser = tbUser.Text 'the user is logged in
    Ngaygiodangnhap = Format(Now(), "dd/mm/yyyy hh:mm:ss AM/PM") 'Ngaygiodangnhap is date/time where the user is logged in
    con.Execute "INSERT INTO SV(MSSV,MaLop,Ho,Ten,HanhKiem,User,Status,Date) VALUES ( _
    '" & ws.Range("A" & i).Value & "', _ '''IS THIS RANGE MSSV?
    '" & ws.Range("B" & i).Value & "', _ '''IS THIS RANGE MaLop?
    '" & ws.Range("D" & i).Value & "', _ '''IS THIS RANGE Ho?
    '" & ws.Range("E" & i).Value & "', _ '''IS THIS RANGE Ten?
    '" & ws.Range("F" & i).Value & "', _ '''IS THIS RANGE Hanhkiem?
    '" & tenUser '"', _ '"' IS THIS RANGE USER
    '" & "0", _ '''IS THIS Status?
    "' & Ngaygiodangnhap & "')" ''' IS THIS Date??????

    It is not working. How can you help me???/?

  10. #10
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,982

    Re: Export Excel to 2 tables Access ??????

    Try this:
    Code:
    Dim ConStr as String
    ConStr =  "INSERT INTO SV(MSSV,MaLop,Ho,Ten,HanhKiem,User,Status,Date) VALUES ( _
    '" & ws.Range("A" & i).Value & "', _ '''IS THIS RANGE MSSV?
    '" & ws.Range("B" & i).Value & "', _ '''IS THIS RANGE MaLop?
    '" & ws.Range("D" & i).Value & "', _ '''IS THIS RANGE Ho?
    '" & ws.Range("E" & i).Value & "', _ '''IS THIS RANGE Ten?
    '" & ws.Range("F" & i).Value & "', _ '''IS THIS RANGE Hanhkiem?
    '" & tenUser '"', _ '"' IS THIS RANGE USER
    '" & "0", _ '''IS THIS Status?
    "' & Ngaygiodangnhap & "')" ''' IS THIS Date??????
    Debug.Print ConStr
    Paste the result into Access, and it will point to the spot it finds first that is wrong. When it works in Access, re-write THAT into VB6
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  11. #11
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,830

    Re: Export Excel to 2 tables Access ??????

    Also remember that when you are using date fields in Access you must use # signs as delimiters where you are using ' instead.

    Follow the advice in the post above and if you still have a problem, don;t tell us it is not working, tell us what it is doing or not doing and any messages that you receive. Not working could mean almost anything. You need to be clear as to what problems you experience in order to get accurate help.
    Always use [code][/code] tags when posting code.

  12. #12
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,982

    Re: Export Excel to 2 tables Access ??????

    Access will POINT to the spot, and show an example that uses # in the HELP section. Then again for the closing...
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  13. #13
    Join Date
    Sep 2012
    Posts
    16

    Re: Export Excel to 2 tables Access ??????

    con.Execute "INSERT INTO SV(MSSV,MaLop,Ho,Ten,HanhKiem,User,Status,Date) VALUES ( _
    '" & ws.Range("A" & i).Value & "', _
    '" & ws.Range("B" & i).Value & "', _
    '" & ws.Range("D" & i).Value & "', _
    '" & ws.Range("E" & i).Value & "', _
    '" & ws.Range("F" & i).Value & "', _
    '" & tenUser '"', _
    '" & "0", _
    "#" & str(Ngaygiodangnhap) & "#)"

    I am trying to run the application, but the error I have got "Type mismatch". I think the problem is str() and I delete str(). But I have got the another error "Syntax error Insert Statement". How can you help me ????

  14. #14
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,830

    Re: Export Excel to 2 tables Access ??????

    Type mismatch most likely means that one or more of the fields you are working with is a numeric field in the database. You are passing the first 6 fields as text so if one of those is defined as numeric in the database you will get that error.

    Did you do what was suggested in Post #10?

    There may also be an issue with one of your field names Date is a reserved word and should not be used as a field name
    If you do use such words as field names then you should put them in brackets to avoid issues
    Code:
    con.Execute "INSERT INTO SV(MSSV,MaLop,Ho,Ten,HanhKiem,User,Status,[Date]) VALUES ( _
    Always use [code][/code] tags when posting code.

  15. #15
    Join Date
    Sep 2012
    Posts
    16

    Re: Export Excel to 2 tables Access ??????

    I am stuck in Find Statement. You can help me using IF Statement.
    Example : If MSSV is found, you use INSERT Statement

    rs.Open " SELECT * FROM SV where MSSV ='", con, adOpenDynamic, adLockBatchOptimistic
    If rs.RecordCount > 0 Then
    con.Execute "INSERT INTO SV(MSSV,MaLop,Ho,Ten,HanhKiem,theUser,Status,theDate) VALUES ('" & ws.Range("A" & i).Value & "','" & ws.Range("B" & i).Value & "','" & ws.Range("D" & i).Value & "','" & ws.Range("E" & i).Value & "','" & ws.Range("F" & i).Value & "','" & tenUser & "','" & "0" & "','" & NgayGioDangNhap & "')"
    con1.Execute "INSERT INTO Lop(MaLop,TenLop) VALUES ('" & ws.Range("B" & i).Value & "', '" & ws.Range("C" & i).Value & "')"

    But I do not know how to find MSSV. I hope you will help me to solve the problem.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center