Thread:

I am having a problem with an ADO recordset where a date restriction is in
the where clause.  I am desperate for a solution to this mystery.  Here is
the code

dim rstPr as ADODB.recordsetdim UpLoaddate as datedim acctID as integer
cnn = "ODBC;DATABASE=Sandbox;UID=Peter;PWD=;DSN=SQL server"UpLoaddate = #10/9/2001#
Set rstPr = New ADODB.RecordsetrstPr.Open "select * from tblPrices where tblPrices.Pricedate = " &
UpLoaddate, cnn, adOpenDynamic, adLockOptimistic

I get no records even though some exist.  (UpLoaddate is a date variable in
VB and tblPrices is an SQL Server table.)

When I use the DAO object, i get the recordset I expect.

dim rsttest as DAO.recordsetdim UpLoaddate as datedim acctID as integerdim db as DAO.database
UpLoaddate = #10/9/2001#
Set db = OpenDatabase("d:\PolarTools\Sandbox.mdb")set rsttest = db.openrecordset("select * from tblPrices where
tblPrices.Pricedate = #" & UpLoaddate & "#", dbOpenDynaset, dbSeeChanges)


In this case tblPrices is a link in access to the tblPrices table in SQL
Server.  What's going on?  I hope you could help me.

Perplexed
Peter Vanderploeg

Peter Vanderploeg wrote:
> 
> I am having a problem with an ADO recordset where a date restriction is in
> the where clause.  I am desperate for a solution to this mystery.  Here is
> the code
> 
>         dim rstPr as ADODB.recordset
>         dim UpLoaddate as date
>         dim acctID as integer
> 
>         cnn = "ODBC;DATABASE=Sandbox;UID=Peter;PWD=;DSN=SQL server"
>         UpLoaddate = #10/9/2001#
> 
>         Set rstPr = New ADODB.Recordset
>         rstPr.Open "select * from tblPrices where tblPrices.Pricedate = " &
> UpLoaddate, cnn, adOpenDynamic, adLockOptimistic
> 
>  I get no records even though some exist.  (UpLoaddate is a date variable in
> VB and tblPrices is an SQL Server table.)
> 
> When I use the DAO object, i get the recordset I expect.
> 
>         dim rsttest as DAO.recordset
>         dim UpLoaddate as date
>         dim acctID as integer
>         dim db as DAO.database
> 
>         UpLoaddate = #10/9/2001#
> 
>         Set db = OpenDatabase("d:\PolarTools\Sandbox.mdb")
>         set rsttest = db.openrecordset("select * from tblPrices where
> tblPrices.Pricedate = #" & UpLoaddate & "#", dbOpenDynaset, dbSeeChanges)
> 

DAO changes the above query before passing the query to the
ODBC driver whereas ADO passes the query to the ODBC driver
exactly. You can see the result in the backend log if you
the postmaster with -d option.

regards,
Hiroshi Inoue