Counting an ADO recordset

[stepone]

Hello all,

I am using the following code to count records in an ADO recordset ;


Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.Open sqlstring

If rs.recordCount = 0 Then
MsgBox "No matching retailers were found.", vbExclamation
rs.Close
Exit Sub
Else

This works perfectly with the following sqlstring ;

sqlstring = "select [Urn] from tblretailers;"

but when I use the LIKE operator, e.g.

sqlstring = "select [Urn] from tblretailers where [URN] like '9501*'"

it always returns ZERO. !! Can anyone point out what I am doing wrong ?

Thanks,
StepOne

 

[ByteMyzer]

Is it possible that this is because there are no records in tblretailers where the value in [URN] begins with "9501"?

 

[stepone]

Unfortunately its not that simple.

I've tried lots of different SELECT statements, and they all work fine, until I put a LIKE clause in them. In fact I have just noticed that if I use the following SQL ;

sqlstring = "select * from tblretailers where [URN] like '95014'"

Then ALL the 1300 retailers in the table get returned - not just the one where URN = 95014.

Am I going crazy ?

StepOne.

 

[Pat Hartman]

Is URN a numeric field? If it is, you cann't use Like to compare to part of it. Like works properly only with text strings.

 

[stepone]

Hi Pat,

No, the URN is a text field. I had all this working in DAO, it is just while trying to create an ADO version that it stopped working.

Do you have any idea - I'm getting really frustrated by it.

Thanks,
StepOne

 

[Uncle Gizmo]

Forget that it used to work in DAO, go back to basics and substitute your like condition for an actual value you know is in the database, get that working and gradually increase the complexity from a sound foundation.

And I know you know that already, it's just that you get stuck with the wrong mindset thinking this should work. If you're like me you forget the obvious thing to do is to debug right from the beginning.

 

[dan-cat]

ADO has different syntax for wildcard characters - try the following...

sqlstring = "select [Urn] from tblretailers where [URN] like '9501[COLOR=Red]%[/COLOR]'"

This will work just as well on a number field as well as a text field.

There are lots of little differences with ADO but keep working on it because if you ever get bored with Access ADO will let you branch out big time.

 

[stepone]

Hi Dan - thanks for that - it seems to work, however it causes another problem. After I have counted the records I then open a form using the same SQL criteria, but that does not work with the '%' syntax !

The purpose of this code is to search for retailers (based on URN, town, or postcode). So I build an sql string, based on the users entries in 3 search field. Then I use ADO to count the number of records which satisfy the criteria. If there are none, display a message. If there is one, open the retailer details form. If there are more than one, display a list of retailer to allow the user to select one. The code looks like ;

(first of all I build an SQL statement called stlinkcriteria based on the users entries)

If stlinkcriteria = "" Then
sqlstring = "SELECT [URN] FROM tblRetailers;"
Else
sqlstring = "SELECT [URN] FROM tblRetailers WHERE " & stlinkcriteria & ";"
End If

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.Open sqlstring
If rs.recordCount = 0 Then
MsgBox "No matching retailers were found.", vbExclamation
rs.Close
Exit Sub
Else
If rs.recordCount = 1 Then
DoCmd.OpenForm FormName:="frmRetailerDetails", wherecondition:=stlinkcriteria
Else
DoCmd.OpenForm FormName:="frmRetailerList", wherecondition:=stlinkcriteria
End If
End If

So, stlinkcriteria works fine to count entries (if the user uses '%' as the wildcard) but then passing stlinkcriteria to the form returns no records - I assume because WHERECONDITION does not like ADO syntax ?

Is there a way around this (other than more code to swap all '*' for '%' or vice-versa) ?

Thanks for all your help,
StepOne

 

[Pat Hartman]

I don't have an answer at the moment, just an observation. I guess this is why Microsoft has left the record sources for forms/reports as DAO. If they had changed them to ADO when they issued A2K, many applications would have stopped working.

 

[dan-cat]

Here is the theory behind it

  Dim cn As ADODB.Connection
    Dim strSql As String
    Dim rs As ADODB.Recordset

    
   Set cn = CurrentProject.Connection
   Set rs = New ADODB.Recordset
   
   strSql = "select URN from tblRetailers where Id like '9501%'"
   rs.ActiveConnection = cn
   rs.Source = strSql
   rs.CursorType = adOpenStatic
   rs.LockType = adLockPessimistic
   rs.Open
   DoCmd.OpenForm ("myForm")
   Set Forms("myForm").Recordset = rs
   rs.Close
   Set rs = Nothing

This opens up your ADO recordset and binds it to your form.
However this is read-only - I'm not sure if you can do this with a read/write recordset.

With Jet I have been sticking with DAO for bound forms and controls and using ADO for unbound forms and controls.

I'm starting to move over to the thinking of NOT binding your forms and controls to data sources but opening recordsets and populating unbound controls with the fields of your choosing. So I haven't seriously looked at ADO's abilities to bind datasources to forms within access - this could be a limitation of ADO within Access?!? Answers on a postcard please ...

 

[Rich]

I'm starting to move over to the thinking of NOT binding your forms and controls to data sources but opening recordsets and populating unbound controls with the fields of your choosing.

might one venture to ask why?

 

[dan-cat]

It's to do with gaining complete control over your form.
Let's say you base a form on a table/query with over 20,000 records. Each time you open that form those 20,000 records are downloaded to the client.
There is no way that a user is going to browse 20,000 records each time they open the form.

Wouldn't it be far quicker for the form to be completely unbound. When you open the form it downloads no records at all or simply downloads the first/last record of a table. In this case its far more efficient to open a recordset with set criteria of being the first/last record of the table. Populating the unbound fields with the appropriate recordset fields and then closing the recordset. Thus only one record not 20,000 is downloaded - think of the relief on the network.

You then get to decide when the record is saved, when or how it is locked. You have complete control over navigation - ie. create search facilities which will define a recordset. Users who click next or previous buttons for records don't actually know what they are looking for. Wouldn't it better to provide a list box for all the sale numbers for a specific day which they could click on and the appropriate sale displayed? I think better search facilities within apps should be encouraged and not just simply rely on the next/previous form buttons provided by access.

It's just the way I've started looking at it - it might not be everyone's cup of tea but the more I pursue it - the more control I'm gaining over my application. Admittedly there is a greater amount of work involved though.

 

[Pat Hartman]

Let's say you base a form on a table/query with over 20,000 records. Each time you open that form those 20,000 records are downloaded to the client.

- That's why you NEVER do this with ODBC tables. with Jet tables it doesn't matter. The entire table will be downloaded anyway. With ODBC data sources, you always use a query with a select clause as the RecordSource for your forms.

You then get to decide when the record is saved, when or how it is locked. You have complete control over navigation - ie. create search facilities which will define a recordset.

- You have complete control with a bound form. You simply need to understand the event model so you know where your code needs to go to accomplish your purpose. Form events are not random and there IS a CORRECT event for every thing you want to do.

It's just the way I've started looking at it - it might not be everyone's cup of tea but the more I pursue it - the more control I'm gaining over my application. Admittedly there is a greater amount of work involved though.

- your time would be far better spent in studing form events so you know where to put a couple of lines of code rather than wasting company resources to build unbound forms with hundreds of lines of code.