OK, I keep getting a syntax error on insert. But the statement is fine, (I think). Can anybody help???




Dim myCommand As New OleDb.OleDbCommand()
Dim myTrans As OleDb.OleDbTransaction

Dim myConnection As OleDb.OleDbConnection
Dim sConnString As String
Dim sSQL as String

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\City1.mdb;"


sSQL = "insert into Real_Estate (real_estate, subdivision, land_district,Block, Lot,range,section,township) values ('" & rsEDI.Fields(0).Value & "','" & rsEDI.Fields(1).Value & "','" & rsEDI.Fields(2).Value & "','" & rsEDI.Fields(3).Value & "','" & rsEDI.Fields(4).Value & "','" & rsEDI.Fields(5).Value & "','" & rsEDI.Fields(6).Value & "','" & rsEDI.Fields(7).Value & "')"


myConnection = New OleDb.OleDbConnection(sConnString)
myConnection.Open()


myCommand.Connection = myConnection

myTrans = myConnection.BeginTransaction()

myCommand.Transaction = myTrans

Try

myCommand.CommandText = sSQL
myCommand.ExecuteNonQuery()
myTrans.Commit()

Catch i As Exception
MsgBox(Err.Description)
Finally
myConnection.Close()
End Try