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