SQL Insert SYntax

[Todd Huttenstine]

Hey guys

Ive been struggling with the below code for an hour. I
keep getting the error:

Syntax error in INSERT INTO Statement.


Dim objConn As ADODB.Connection
Dim szConnect As String
Dim szSQL As String
' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\QTD\Sales.xls;" & _
"Extended Properties=Excel 8.0;"
' Create the SQL statement.
szSQL = "INSERT INTO Sheet1 " & _
"VALUES('Val1', 'Val2', 'Val3', 'Val4');"
' Create and open the Connection object.
Set objConn = New ADODB.Connection
objConn.Open szConnect
' Execute the insert statement.
objConn.Execute szSQL, , adCmdText Or
adExecuteNoRecords
' Close and destroy the Connection object.


Can anyone please help me?

Thank you
Todd Huttenstine
objConn.Close
Set objConn = Nothing

 

[Tom Ellison]

Dear Todd:

Put a breakpoint on the "Set objConn" line and examine the SQL string
szSQL in the immediate pane. Does the actual SQL look right? Paste
it into Query Analyzer and see if it works. If there are no errors at
that point, the problem is somewhere else. If there is an error (and
I didn't see one, but I don't know the names of your tables and views,
or your column datatypes) they you can fix it in Query Analyzer, then
make the same changes to your code. Repeat untill well done.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Todd Huttenstine]

Ok thank you, now it works but... The following code only
specifies the sheet to update. Is there anyway I can
specify the sheet and the cell to update?

Dim objConn As ADODB.Connection
Dim szConnect As String
Dim szSQL As String
' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\QTD\Sales.xls;" & _
"Extended Properties=Excel 8.0;"
' Create the SQL statement.
szSQL = "INSERT INTO [Sheet3$] VALUES('TestValue1'); "
' Create and open the Connection object.
Set objConn = New ADODB.Connection
objConn.Open szConnect
' Execute the insert statement.
objConn.Execute szSQL
' Close and destroy the Connection object.
objConn.Close
Set objConn = Nothing

 

[Jamie Collins]

...

Ok thank you, now it works but... The following code only
specifies the sheet to update. Is there anyway I can
specify the sheet and the cell to update?

See reply to your post in .Excel.Programming.

Jamie.

--