Access - Dates and ADO

Hello,
this question has probably been asked a million times, but I have never had cause to query dates from VB - ADO to Access, and it is causing me some hassle.

In short, I generate a simple select statement, assign this to the commandtext object of a command and execute it. The string contains a date, which I pass as for example April 4th would be #04/04/00#

The string executes perfectly well if copied from the VB environment ( from immediate window ) and ran directly as a query in Access, however executing the same string via VB / ADO returns -1 as recordcount. Can anyone help me out? I am presuming that ADO adds "something" to the query string which Access has a problem with.
Is there a way around this issue?

Many thanks in Advance.





Source below:

Private Sub cmdRun_Click()
'
' Declare command and SQL String, and ADO Recordset
'
Dim lc_Query As ADODB.Command
Dim ls_SQL As String
Dim lrs_results As New ADODB.Recordset
'
' Instantiate the object
'
Set lc_Query = New ADODB.Command

'
' Initialise String
'
ls_SQL = "Select Detail, StartTime, StartDate, EndTime, EndDate, Outcome from All1 where "

'
' Check Date options
'
If optBeforeDate = True Then
ls_SQL = ls_SQL & "StartDate < '" & DTPicker1.Value & "'"
ElseIf optAfterDate = True Then
ls_SQL = ls_SQL & "StartDate > #" & DTPicker1.Value & "#"
Else
ls_SQL = ls_SQL & "StartDate = #" & DTPicker1.Value & "#"
End If


'
' Associate Command object with Active Connection
' Associate the commandText object with the SQL String.
'
lc_Query.ActiveConnection = lconn
lc_Query.CommandText = ls_SQL
lc_Query.CommandType = adCmdText

'
' Associate the results from the query with the recordset.
'
Set lrs_results = lc_Query.Execute

lrs_results.MoveFirst
MsgBox lrs_results(0)

The reason you are getting -1 as a recordcount is because you are opening your recordset with a forward only cursor, where you would want to use a static, keyset, or dynamic cursor. A forward only cursor won't return a recordcount, while the other 3 cursors will.

I'm not sure how to do it in the data environment, but here is a regular example:

Code:

---

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
   
   
    Set cn = New Connection
   
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWind2k.mdb"
   
    Set rs = New Recordset
   
    rs.Open "Select * from Employees where EmployeeID = 2", cn, adOpenStatic, adLockReadOnly
   
    MsgBox rs.RecordCount
   
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

---

HTH

Tom

That's a good point Ienin...

You may also want to consider doing something different
that you don't depend on the RecordCount property. It can
sometimes be unreliable. I, at least will go a faster
route if I just want the RecordCount. (even if I want the
records AND the recordcount, I will process this before-
hand)

Code:

---

"Select Count(FieldName) as RecCount From Table
WHERE Field=#01/01/00#"

---

Note:
1) Don't use "Select Count(*) FROM ..." because it will
Look through all the fields in the fields collection to
process the query when it doesn't have to. That's extra
work that you don't want the engine to have to do. Just
use one field name.

2) After you recieve the total count and you want to
process through all the records in a RecordSet, instead of
using the common "Do While (Not rs.eof)" use a "For i = 1
to lngRecsCount" loop. This can be more efficient because
every record processed will have to examine rs.eof where as
the other loop doesn't. Plus, you have the total amount of
records your are processing for a Status bar or Progress
bar.

Hope this makes sense.

If you are using SQL Server, you would want to use Select Count(*) FROM instead of Select Count(FieldName) FROM

But in this situation, why would you want to do this anyway? This is an extra query on your database -- Just open the recordset as static and you will get the recordcount along with your recordset, all in 1 query.

Before opening your recordset, change CursorLocation to use adUseClient

Code:

---

Private Sub Command1_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
   
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWind2k.mdb"
   
    rs.CursorLocation = adUseClient
    rs.Open "Select * from Employees where EmployeeID = 2", cn, adOpenStatic, adLockReadOnly
    rs.MoveLast
   
    MsgBox rs.RecordCount
   
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

---

I understand all that, but (as with every program) you should be heavily testing performance and what will work best. AND, if the record count really does come back to you. In some cases it may not. Why not go with something that DOES work.

I work with SQL Server as well, and you don't always want to use "Select Count(*)" because of performance issues.

AGAIN, test, test, test. I'm not saying DON'T, I'm saying, my experience tells me to try different alternatives for the best result coupled with great performance.