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

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

  1. #16
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,834

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

    Code:
    Select * from Table Where MSSV='" & myValue &"'"
    where MyValue is the variable that holds the value you are searching for and is a text field. If the field is a numeric field then it would be

    Code:
    Select * from Table Where MSSV=" & myValue
    Always use [code][/code] tags when posting code.

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

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

    Forget that. Get them ALL, and then apply the FILTER property to your query results. Instant, as they are all in memory. Make sure not to use TOO MANY RECORDS, though.
    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!

  3. #18
    Join Date
    Sep 2012
    Posts
    16

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

    I can not use UPDATE Statement and I do not know Find Statement is working or not. Because I get the error "Syntax error in UPDATE Statement" .The code is as below:

    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 & "\TEST2.mdb;Persist Security Info=False"
    con1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TEST2.mdb;Persist Security Info=False"
    Set wb = ex.Workbooks.Open(tb_upload.Text, , True)
    Set ws = wb.Worksheets(1)
    Call dbConnection
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
    Do: i = i + 1

    cmd.CommandText = "select * from SV "
    Set rs = cmd.Execute()
    If rs.RecordCount > 0 Then
    con.Execute "UPDATE SV " & _
    "SET " & ",MaLop = '" & ws.Range("A" & i) & "'" & ",Ho = '" & ws.Range("D" & i) & "'" & _
    ",Ten = '" & ws.Range("F" & i) & "'" & ",HanhKiem = '" & ws.Range("G" & i) & "'" & ",[User]= '" & tenUser & "'" & ",Status = '" & "0" & "'" & ",[Date] = '" & NgayGioDangNhap & "Where Status = '" & "-1" & "'"
    Else
    con.Execute "INSERT INTO SV(MSSV,MaLop,Ho,Ten,HanhKiem,[User],Status,[Date]) VALUES ('" & ws.Range("C" & i).Value & "','" & ws.Range("A" & i).Value & "','" & ws.Range("D" & i).Value & "','" & ws.Range("E" & i).Value & "','" & ws.Range("F" & i).Value & "','" & tenUser & "','" & "-1" & "','" & NgayGioDangNhap & "')"

    con1.Execute "INSERT INTO Lop(MaLop,TenLop) VALUES ('" & ws.Range("A" & i).Value & "', '" & ws.Range("B" & i).Value & "')"
    End If
    Loop Until ws.Range("A" & (i + 1)) = Empty
    ex.Quit
    Set ex = Nothing
    con.Close
    con1.Close
    End Sub

    Can you check the UPDATE Statement ? Why is the UPDATE Statement is error ?

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

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

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

    As I have mentioned before if your date field is an actual date field then access requires you to use # signs around the date value rather than ' characters.

    You have a comma in your update statement right after SET which should not be there.

    That is just what I see at a quick glance. You should:

    1: Dim a String variable to hold your SQL statement
    2: Assign your SQL Statement to the string
    3: Use debug.Print or Msgbox to display the content of that SQL Statement once it is built
    4: Use the string variable holding the sql statement for your execute statement

    This allows you to see the actual string rather than trying to dechiper it from your code, also if you use debug.print you can copy the actual update statement and paste it into access query designer then execute it there to get a better idea of what is wrong and how to fix it.
    Always use [code][/code] tags when posting code.

  5. #20
    Join Date
    Sep 2012
    Posts
    16

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

    Now I Stuck in FIND Statement because my homework is required if MSSV is update in the first time, the ID Number = 1. Then if MSSV is update the second time, the ID Number = 2 and the Status from MSSV is convert -1(Yes) to 0(No)
    Call dbConnection
    cmd.CommandText = "SELECT MAX(ID Number) FROM SV where MSSV ='" & ws.Range("C" & i) & "'", con, adOpenDynamic, adLockBatchOptimistic
    Set rs = cmd.Execute()
    If rs.RecordCount > 0 Then
    con.Execute "UPDATE SV SET Status = 0 WHERE Status = -1"

    I think it is wrong. Can you fix it for me, please?

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

  6. #21
    Join Date
    Sep 2012
    Posts
    16

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

    I have 2 problem in my homework. Firstly, I have convert ID field from AutoNumber to Number. I want to start ID = 1.Because I try to fix it but ID is starting 0. When I insert repeatly the second time, ID = 2.
    Secondly, I try to write INSERT Statement and Update Statement but the program is running the Update Statement.

    Could you tell me how to write the code.

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

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

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

    Keep the old AutoUpdate field, and add a NEW FIELD... As far as Homework, you should be talking to a teacher, not posting in a forum that he'll be able to see the answer IF WE POST IT for you...
    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!

Page 2 of 2 FirstFirst 12

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