If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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"

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.