Run-time error from Access after VBA 6 tries to send sql to linked table
Posted on 2004-03-25
First, Im not an Access developer, and dont want to be.
But I have a major access database with linked tables that I need to maintain for awhile
here is the basis for what I am doing so far:
Dim con As Object
Dim rs As Object
Dim strSQL As String
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
strSQL = "select * from [SCHEMA_LOAD_HEADER] where [LOAD_DATE]='3/24/2004' AND [LOAD_NO] = 1"
rs.Open strSQL, con, 1 '1=adOpenKeyset
NOTES: the actual date and load number in the sql query come from user input so that the sql is dynamic, I took that part out for simplicity sake.
LOAD_DATE is formated on DB2 as a DATE field, and LOAD_NO is an Integer field
The exact same code sequence is used in the Database to select items from the switchboard table which is one of only two or three actual access tables, all the rest are linked to a DB2 database server.
Using the DoCmd.RunSQL (strSQL) method I can insert into, and update the database with no problem
but when I want to select from a linked table I get the following error message:
msgbox title "Microsoft Visual Basic"
msgbox message "Run-time error '-2147217913 (80040e07)'
msgbox message line 2 "Date type mismatch in criteria expression"
Clicking on Debug takes you to the "rs.Open strSQL, con, 1" line in the code for the procedure
Clicking on Help takes you to a Microsoft Access Help screen with no content, only a panel with a titlebar
Notes, the sql statement plugged directly into the database by a db2 client command center pulls the correct data.
layout of the table has not changed since the table link was created.
If I open the table in the table view and get all the data, everything works, can add, update, select and filter records.
I get this error message right after the rs.Open strSQL, con, 1 line actually opens the connection to the database and I am prompted for a userid and password (if not already given)
any help greatly appreciated