hi there,
I have a question regarding insert multiple data into multiple table
The program work fine when i only insert into 1 table but inserting another set of data into another table the program won't do it
this is what i wrote

Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim path As String

Private Sub Command2_Click()
Dim a As String

path = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\present\vb\maintenance.mdb"

a = "INSERT INTO MachineStatus(machineCode,machineInfo,expiretype," & _
"hourCode,expireCode)VALUES('" & machcode & "','" & machinfo & _
"','" & maintenancetype.Text & "','" & hourcod & " ','" & expirecod & "')"

rs1.Open a, path, adOpenStatic, adLockOptimistic
'rs1.Update

'rs1.Close

a = "INSERT INTO MachineName(machineName,MachineCode)VALUES('" _ & name1.Text & "','" & machcode & "')"
rs2.Open a, path, adOpenStatic, adLockOptimistic
'rs2.Update
'rs2.Close

Note: I've try use update and close adding to it but it still won't work
many thanks
sun

Using a recordset with an INSERT statement doesn't make much sense because the recordset will not return any data. Instead, do something like this:

Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim path As String

Private Sub Command2_Click()
Dim a As String, n as Long

path = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\present\vb\maintenance.mdb"

Set con = New ADODB.Connection
con.ConnectionString = path
con.Open

a = "INSERT INTO MachineStatus(machineCode,machineInfo,expiretype," & _
"hourCode,expireCode)VALUES('" & machcode & "','" & machinfo & _
"','" & maintenancetype.Text & "','" & hourcod & " ','" & expirecod & "')"

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = con
'I might be wrong about adCommandText,
'I don't have VB on the machine I'm writing this on
.CommandType = adCommandText
.CommandText = a
'After execution n contains the number of records processed
.Execute n
End With

Etc. for the second INSERT statement

Hope this helps.