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

[DataMiser]

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

Select * from Table Where MSSV=" & myValue

[dglienna]

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.

[thedeathnotes210x]

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 !!!!!!!!!!!!

[DataMiser]

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.

[thedeathnotes210x]

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 !!!!!!!!!!!!

[thedeathnotes210x]

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 !!!!!!!!

[dglienna]

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...