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

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

What code have you written and what problem are you running into?

Sounds like HOMEWORK to me...

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

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.

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

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

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.

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???/?

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

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.

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

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

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 ( _

---

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.

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

---

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.

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

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.

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

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

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