Access confusing wildcards when using ADO...

[Andrew Kidd]

Hi,

I'm using Access XP and have the following code in the class module behind a form (error handling has been removed for clarity:
Private Function m_GetNumberOfRecords(strQueryName As String) As Long
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

m_GetNumberOfRecords = 0

With rs
.ActiveConnection = CurrentProject.Connection
.Open strQueryName, , adOpenDynamic, adLockOptimistic, adCmdStoredProc
If Not .BOF = True And Not .EOF = True Then
.MoveFirst
Do While Not .EOF
m_GetNumberOfRecords = m_GetNumberOfRecords + 1
.MoveNext
Loop
Else
m_GetNumberOfRecords = 0
End If
.Close
End With

' Tidy up.
Set rs = Nothing

End Function
The purpose of the function is to return the number of records for any query, which is passed in as a string value from a combo box selected by the user.

Now, the odd thing that is happening is that when a user uses the normal Access wildcard of an asterisk (e.g. Like "Company*"), although Access shows the correct number of records, the above code returns 0. However, when the user uses the equivalent SQL wildcard (e.g. Like "Company%"), although Access now shows no records, the above function returns the correct number of records.

Can anyone shed any light on this please? Or should I tell my users to use SQL wildcards?

Thanks in advance,

Andy

 

[Douglas J. Steele]

The wildcard for ADO is %, regardless of what database you're running
against.

BTW, there's no need to go record by record to determine how many records
are in the recordset.

If Not .BOF = True And Not .EOF = True Then
.MoveLast
m_GetNumberOfRecords = .RecordCount
Else
m_GetNumberOfRecords = 0
End If


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Hi,

I'm using Access XP and have the following code in the class module behind a
form (error handling has been removed for clarity:
Private Function m_GetNumberOfRecords(strQueryName As String) As Long
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

m_GetNumberOfRecords = 0

With rs
.ActiveConnection = CurrentProject.Connection
.Open strQueryName, , adOpenDynamic, adLockOptimistic, adCmdStoredProc
If Not .BOF = True And Not .EOF = True Then
.MoveFirst
Do While Not .EOF
m_GetNumberOfRecords = m_GetNumberOfRecords + 1
.MoveNext
Loop
Else
m_GetNumberOfRecords = 0
End If
.Close
End With

' Tidy up.
Set rs = Nothing

End Function
The purpose of the function is to return the number of records for any
query, which is passed in as a string value from a combo box selected by the
user.

Now, the odd thing that is happening is that when a user uses the normal
Access wildcard of an asterisk (e.g. Like "Company*"), although Access shows
the correct number of records, the above code returns 0. However, when the
user uses the equivalent SQL wildcard (e.g. Like "Company%"), although
Access now shows no records, the above function returns the correct number
of records.

Can anyone shed any light on this please? Or should I tell my users to use
SQL wildcards?

Thanks in advance,

Andy

 

[david epsom dot com dot au]

The wildcard for ADO is %, regardless of what database you're running
against.

Unless you are using stored procedures (for example, Access Querydefs),
in which case the stored procedure itself is in the native SQL of whatever
database engine is connected.

This means that if you suck the SQL out of an ADO connection to an
Access QueryDef, you CANNOT turn around and use that SQL to create
a new ADO SQL Statement.

(david)

 

[Andrew Kidd]

Hi Doug,

Thanks for the reply. I'll tell the users to use the % wildcard only.

Also, I tried your code but I just get the result of -1, telling me that
there are records, but not the record count.

Regards,

Andy

 

[Andrew Kidd]

Hi David,

Thanks for the reply. I'm just passing in the name of the Access query that
resides within the same database, not the actual SQL text of a query.
i.e. I pass "qryGrabSomeData" rather than "SELECT * FROM tblContactRecords".

Regards,

Andy

 

[Scott McDaniel]

You need to set the Cursortype and, possibly, the Locktype in your recordset
object in order to return actual counts:

rst.Open "SQL", connection, adOpenKeyset,adLockPessimistic

An ADODB recordset defaults to adOpenForwardOnly, which cannot contain valid
record counts.

 

[Andrew Kidd]

Hi Scott,

Thanks for your input, you were almost there. The culprit was
"CursorLocation" with "adUseClient" and it worked.

Regards,

Andy

 

[david epsom dot com dot au]

If the query is created by ADO, you should expect it to be in
ADO syntax. If it is created by Access, you should expect it
to be in Access syntax.

(david)