I am new to Access. I am trying to import data from a spreadsheet into Access. I wrote a Java application that converts the data into a series of SQL commands. I looked for examples of how to run multiple SQL statements because I do not want to enter and run 2200 SQL statements by hand. I started with an example and coded this:
Code:
Public Function TestUpdate()
On Error Resume Next
Dim ws As Workspace
Dim db As Database
Dim strSQL As String
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
On Error GoTo Proc_Err
'start a transaction to ensure all updates are run or rolled back
ws.BeginTrans
'1st statement
strSQL = "INSERT INTO incidents(ID, incident_date, state, city, dead, wounded, cause, category)"
VALUES(1,0/0/1657,8,Farmington,2,1,5,1);"
db.Execute strSQL, dbFailOnError
'2nd statement
strSQL = "INSERT INTO incident_weapon(incident_id, weapon_id) VALUES (1,1);"
db.Execute strSQL, dbFailOnError
'3rd statement
strSQL = "INSERT INTO incidents(ID, incident_date, state, city, dead, wounded, cause, category);"
db.Execute strSQL, dbFailOnError
'4th statement
strSQL = "INSERT INTO incident_weapon(incident_id, weapon_id) VALUES (2,4);'"
'commit all changes
ws.CommitTrans
Proc_Exit:
Set ws = Nothing
Set db = Nothing
Exit Function
Proc_Err:
ws.Rollback
MsgBox "Error updating: " & Err.Description
Resume Proc_Exit
End Sub
Private Sub Command0_Click()
End Sub
When I execute it, none of the commands are executed (at least not updating my tables), nor are there any error messages.