SQL Work in Access but not in Excel via VBA

[mjbigelow]

Hello,

I have some VBA that programmatically writes a SQL query. When I paste
the query into Access, it runs just fine. When I try to run it using
ADO via Excel, it gives me the "too many parameters. x expected". The
number that x represents is the number of values that I'm trying to
pass in the values clause of my SQL.

The field names and the order matches up and so do the value/field
types.

Please see the code and the SQL below.

Thanks so much for your help!

Mark

****VBA Code****
Sub Run_Query()

Dim objConn As ADODB.Connection, objRes As ADODB.Recordset
Dim objData As New DataObject

Set objConn = New ADODB.Connection
objConn.Open "st2000"

objData.SetText strSQL
objData.PutInClipboard

Set objRes = objConn.Execute(strSQL)

objConn.Close
Set objRes = Nothing
Set objConn = Nothing

End Sub

***SQL Code***
INSERT INTO TABLE VALUES (62, "1", "2", "3", "4", "5", "6", "", "", "",
"", "", "", "", "", "Other:", "", "", "", "", "", "", "", "", "", "",
"", "Other:", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "");

 

[Tim Williams]

Try removing the trailing semicolon.

Tim

 

[mjbigelow]

Thanks for the suggestion. Unfortunately, it didn't work. Any else?

Thanks!
Mark

 

[Tim Williams]

Try single quotes not double.

Tim

 

[Guest]

1. SQL statements SHOULD be terminated by ; semi-colon.
2. SQL statements should use single NOT double quotes although some
drivers/providers allow sounle quotes-I'd change to single quotes.
3. You do not say what where TABLE is or what TYPEs the columns you are
assiging "" to are. Assigining "" to a numeric or date column will fail. If
you do not want to assign values to some columns, you have two options:
a. use the keyword NULL instead of "" (NULL is acceptable with columns of
any type).
b. Construct yor SQL as follows:

SQL = "INSERT INTO TABLE (FIELDX,FIELDY,FIELDZ) VALUES(VALUE1,VALUE2,VALUE3);"

where

(FIELDX,FIELDY,FIELDZ) is a list of all the fields m in any order, for which
you want to specify values

(VALUE1,VALUE2,VALUE3) are the corresponding values for
(FIELDX,FIELDY,FIELDZ) ; (FIELDX,FIELDY,FIELDZ) does NOT have to be in the
order they appear in TABLE.

This will work UNLESS there are CONSTRAINTS on the TABLE that requires
values for columns not included in (FIELDX,FIELDY,FIELDZ).

 

[Tim Williams]

1. SQL statements SHOULD be terminated by ; semi-colon.

Not always - a terminating semicolon will cause an Oracle SQL query to break when submitted via ADO.
In many cases it serves only as a flag to indicate you're at the end of a statement.

Tim