Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA is Mangling a Query
Here is the query being sent from Excel VBA to MS_Access:
' Open Stability connection Set conStab = wrkODBC.OpenConnection("Stability", dbDriverNoPrompt, True, _ "ODBC;DBALIAS=Stability;UID=;PWD=;DSN=Stabilit y") ' Build SQL Insert string SQLString = "" SQLString = SQLString + "INSERT INTO T_Request" + gCRLF SQLString = SQLString + " VALUES ( " SQLString = SQLString + gQ + "Smith, Ronny Bob" + gQ SQLString = SQLString + ", " + "#11/01/2007#" SQLString = SQLString + ", " + gQ + "54321" + gQ SQLString = SQLString + ", " + gQ + "55" + gQ SQLString = SQLString + ", " + gQ + "2" + gQ SQLString = SQLString + ", " + gQ + "Dry" + gQ SQLString = SQLString + ", " + gQ + "Food Aroma is OK" + gQ SQLString = SQLString + ", " + gQ + "Test Batch" + gQ SQLString = SQLString + ", " + gQ + "Hi, low, Vhi, Vlow" + gQ SQLString = SQLString + ", " + gQ + "100070" + gQ SQLString = SQLString + ", " + gQ + "4020095" + gQ SQLString = SQLString + ", " + gQ + "Shelf Life Extension Testing" + gQ SQLString = SQLString + ", " + gQ + "Comment goes Here" + gQ SQLString = SQLString + " )" ' Insert invoice T_Request Table Application.Range("J6").Value = SQLString Set rsInsert = conStab.OpenRecordset(SQLString, dbAppendOnly, 0) Here is what I captured from the ODBC Trace file (I added line breaks for readibility). excel 1414-1418 ENTER SQLExecDirect HSTMT 02D02678 UCHAR * 0x02E32DCC [ -3] "SELECT * FROM INSERT INTO T_Request\ d\ a VALUES ( 'Smith, Ronny Bob', #11/01/2007#, '54321', '55', '2', 'Dry', 'Food Aroma is OK', 'Test Batch', 'Hi, low, Vhi, Vlow', '100070', '4020095', 'Shelf Life Extension Testing', 'Comment Goes Here' )\ 0" SDWORD -3 excel 1414-1418 EXIT SQLExecDirect with return code -1 (SQL_ERROR) HSTMT 02D02678 UCHAR * 0x02E32DCC [ -3] "SELECT * FROM INSERT INTO T_Request\ d\ a VALUES ( 'Smith, Ronny Bob', #11/01/2007#, '54321', '55', '2', 'Dry', 'Food Aroma is OK', 'Test Batch', 'Hi, low, Vhi, Vlow', '100070', '4020095', 'Shelf Life Extension Testing', 'Comment Goes Here' )\ 0" SDWORD -3 DIAG [37000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause. (-3506) I have used this format hundred of times against many types of DBs, but this is the first time against Access. Does anybody have any idea why the MS-ODBC driver is adding the "SELECT * FROM" to the submitted query. Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA is Mangling a Query
I switched the macro to use ADO rather than DAO and after a good deal of
rework I am able to insert/add rows to a Access table. I guess MS-Access can't do ODBC or MS-ODBC can't do Access. Either way it another fine MS product. I really don't like ADO but I guess it will have to done until I can port the application to a "real" database. Oh Well !!!!! "kahoar" wrote: Here is the query being sent from Excel VBA to MS_Access: ' Open Stability connection Set conStab = wrkODBC.OpenConnection("Stability", dbDriverNoPrompt, True, _ "ODBC;DBALIAS=Stability;UID=;PWD=;DSN=Stabilit y") ' Build SQL Insert string SQLString = "" SQLString = SQLString + "INSERT INTO T_Request" + gCRLF SQLString = SQLString + " VALUES ( " SQLString = SQLString + gQ + "Smith, Ronny Bob" + gQ SQLString = SQLString + ", " + "#11/01/2007#" SQLString = SQLString + ", " + gQ + "54321" + gQ SQLString = SQLString + ", " + gQ + "55" + gQ SQLString = SQLString + ", " + gQ + "2" + gQ SQLString = SQLString + ", " + gQ + "Dry" + gQ SQLString = SQLString + ", " + gQ + "Food Aroma is OK" + gQ SQLString = SQLString + ", " + gQ + "Test Batch" + gQ SQLString = SQLString + ", " + gQ + "Hi, low, Vhi, Vlow" + gQ SQLString = SQLString + ", " + gQ + "100070" + gQ SQLString = SQLString + ", " + gQ + "4020095" + gQ SQLString = SQLString + ", " + gQ + "Shelf Life Extension Testing" + gQ SQLString = SQLString + ", " + gQ + "Comment goes Here" + gQ SQLString = SQLString + " )" ' Insert invoice T_Request Table Application.Range("J6").Value = SQLString Set rsInsert = conStab.OpenRecordset(SQLString, dbAppendOnly, 0) Here is what I captured from the ODBC Trace file (I added line breaks for readibility). excel 1414-1418 ENTER SQLExecDirect HSTMT 02D02678 UCHAR * 0x02E32DCC [ -3] "SELECT * FROM INSERT INTO T_Request\ d\ a VALUES ( 'Smith, Ronny Bob', #11/01/2007#, '54321', '55', '2', 'Dry', 'Food Aroma is OK', 'Test Batch', 'Hi, low, Vhi, Vlow', '100070', '4020095', 'Shelf Life Extension Testing', 'Comment Goes Here' )\ 0" SDWORD -3 excel 1414-1418 EXIT SQLExecDirect with return code -1 (SQL_ERROR) HSTMT 02D02678 UCHAR * 0x02E32DCC [ -3] "SELECT * FROM INSERT INTO T_Request\ d\ a VALUES ( 'Smith, Ronny Bob', #11/01/2007#, '54321', '55', '2', 'Dry', 'Food Aroma is OK', 'Test Batch', 'Hi, low, Vhi, Vlow', '100070', '4020095', 'Shelf Life Extension Testing', 'Comment Goes Here' )\ 0" SDWORD -3 DIAG [37000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause. (-3506) I have used this format hundred of times against many types of DBs, but this is the first time against Access. Does anybody have any idea why the MS-ODBC driver is adding the "SELECT * FROM" to the submitted query. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import New Database Query (Union Query) in Spreadsheet | Excel Discussion (Misc queries) | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |