I'm trying to use SQL to access an MS Access DB and insert values in a table, and get a "syntax error in INSERT INTO" message, and I can't figure out what's wrong with my syntax... This is what I try to do:
Dim conAcdb As ADODB.Connection
Dim cmdAcdb As ADODB.Command
Set conAcdb = New ADODB.Connection
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:DevSourcemiscatlas.mdb;Persist Security Info=False"
Set cmdAcdb = New ADODB.Command
Set .ActiveConnection = conAcdb
[Some code to calculate values]
.CommandText = " INSERT INTO Places (Place, AltName, LngDeg, LngMin, LngDir, LatDeg, LatMin, LatDir, Country, Zone) VALUES ('" & sName & "', '" & sAltName & "', '" & sLngDeg & "', '" & sLngMin & "', '" & sLngDir & "', '" & sLatDeg & "', '" & sLatMin & "', '" & sLatDir & "', '" & sCountry & "', '" & sZone & "'); "
.CommandType = adCmdText
I even try to hard code values like this:
.CommandText = "INSERT INTO Places (Place, AltName, LngDeg, LngMin, LngDir, LatDeg, LatMin, LatDir, Country, Zone) VALUES ('AachimAllgaeu', 'AachimAllgu', '9', '58', 'E', '47', '31', 'N', 'D', '4')"
and still get same error, also experimenting leaving out the ; at end of commandtext. All fields in db are text. I'm not an expert on SQL but afaik I can't see anything wrong with the syntax.
Thanks for any input,