VBA is Mangling a Query

K

kahoar

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=Stability")

' 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,
 
K

kahoar

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 !!!!!
 
Top