Passing 'date' parameter to Jet 4.0 linked server - collation problem?

Hello,
I've attached Access MDB as a linked server to SQL 2000 server. Now I
want to run distributed queries. I set 'collation compatible' option,
so when I reference an Access MDB table in WHERE clause, the parameter
is passed to Access data provider. But I can't do the same for
Datetime columns! :-((
Unfortunately I have a large legacy application which uses dynamic SQL
creation, so it is VERY painful for me to rewrite&optimize all SQL
statements in order to use OPENQUERY statements :-((
Example:
Select * From MyMDB...Orders Where OrderDate Between '1/1/5' and
GETDATE()
This statement results in scanning all of the Orders table by SQL
server :-(
Please help me!> Select * From MyMDB...Orders Where OrderDate Between '1/1/5'
What the heck kind of date is that? Try '20050101', assuming that is the
date you meant. The fact that *I* don't know what date you are passing
should be some kind of clue as to why the software doesn't understand it.

Pass Through query with property ReturnsRecords set to FALSE

Whenever I try to make a pass through query in Access 2002 for Oracle
sql server I get this fault
"The microsof Jet engine cannot find table or query" and then mentions
the connection string as table or query name.
The query is not supposed to return records, because it is DROP TABLE
<table_name>
When I run the syntax in Oracle SQL plus it works fine.
If I set the ReturnRecords property to TRUE it correctly says that the
query doesn't return records, but the query is correctly executed.
Here the problem is, it stops the Macro I use to run a whole serie of
pass throughs.
Anyone got a solution? I have the latest jet engine service pack
installed but no improvement.
DiPass-through queries pass SQL statements directly to the data source
-- they are not parsed by Access, Jet or ODBC. I'm not sure why you're
getting the Jet engine error message, or where the problem lies. You
might want to consider kicking off your pass-through queries from VBA
instead of from macros. That way you can get more information through
structured error handling, which macros lack. You can create or modfiy
pass-through queries by coding to the DAO.QueryDef object.
--Mary
On 24 Aug 2006 01:34:40 -0700, "Di" <dianne.timmerman@.gmail.com>
wrote:

>Whenever I try to make a pass through query in Access 2002 for Oracle
>sql server I get this fault
>"The microsof Jet engine cannot find table or query" and then mentions
>the connection string as table or query name.
>The query is not supposed to return records, because it is DROP TABLE
><table_name>
>When I run the syntax in Oracle SQL plus it works fine.
>If I set the ReturnRecords property to TRUE it correctly says that the
>query doesn't return records, but the query is correctly executed.
>Here the problem is, it stops the Macro I use to run a whole serie of
>pass throughs.
>Anyone got a solution? I have the latest jet engine service pack
>installed but no improvement.
>Di