Solved

Run-time error from Access after VBA 6 tries to send sql to linked table

Posted on 2004-03-25
1
465 Views
Last Modified: 2008-02-01
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"

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 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
 
 
 
 
0
Comment
Question by:Rance_Hall
1 Comment
 
LVL 50

Accepted Solution

by:
Steve Bink earned 75 total points
ID: 10679729
strSQL = "select * from [SCHEMA_LOAD_HEADER] where [LOAD_DATE]=#3/24/2004# AND [LOAD_NO] = 1"

Date criteria need to be delimited by #.

Good luck to you.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now