Re: Access having run time errors while selecting from odbc linked table

From: Rance (ntg-rhall_at_nctc.net)
Date: 03/25/04 

Date: Thu, 25 Mar 2004 09:16:25 -0800

Ok Frank, here ya go:

strSQL = "Select * from [SCHEMA_LOAD_HEADER] where [LOAD_DATE] = '"
strSQL = strSQL & Date & "' AND [LOAD_NO] = " & Load

Date and Load are variables that are collected by an input box so the sql statement can be dynamic in nature.

once those are collected and processed an example resulting sql statement looks like this:

Select * from [SCHEMA_LOAD_HEADER] where [LOAD_DATE] = '3/24/2004' AND [LOAD_NO] =1

Not sure why, but it appears that brackets around the table and column names are required by Access.

LOAD_DATE is formated on DB2 as a DATE field, and LOAD_NO is an Integer field

     ----- Frank Hickman wrote: -----
     
     Could you please post the {condition} portion of the SQL statement?
     
     --
     Frank
     
     
     "Rance" <ntg-rhall@nctc.net> wrote in message
     news:216A3338-A3D7-4CEF-91D8-30FD9F4B54B7@microsoft.com...
> 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 {tablename} where {condition}"
> rs.Open strSQL, con, 1 '1=adOpenKeyset
>> 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"
>> buttons shown:
> Continue (inactive-gray)
> End
> Debug
> Help
>> 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 poll, 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
>> Rance