| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Work in Access but not in Excel via VBA
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:", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""); |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL Work in Access but not in Excel via VBA
Try removing the trailing semicolon.
Tim wrote in message oups.com... 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:", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""); |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL Work in Access but not in Excel via VBA
Thanks for the suggestion. Unfortunately, it didn't work. Any else?
Thanks! Mark |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL Work in Access but not in Excel via VBA
Try single quotes not double.
Tim wrote in message ups.com... Thanks for the suggestion. Unfortunately, it didn't work. Any else? Thanks! Mark |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL Work in Access but not in Excel via VBA
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). " wrote: 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:", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""); |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL Work in Access but not in Excel via VBA
"AA2e72E" wrote in message ... 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 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). " wrote: 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:", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""); |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I format data from access to work in excel calculations? | Excel Worksheet Functions | |||
| Excel to Access - formula does not work... | New Users to Excel | |||
| Access query does not work in Excel | Excel Programming | |||
| Excel Macros do not work with Outlook Web Access | Excel Programming | |||
| Access data -work in Excel- save in Access | Excel Programming | |||
Linear Mode
