Findfirst on ODBC/SQL with DAO = error

[Zakraket]

I have an Access-db, currently split in frontend Access 2013, backend 2013.

I need to upsize to SQL-server.

In this database the DAO-recordset is used a lot, not ADODB. In cases where data needs to be found the .findfirst method is used, which works properly when using the Access-backend.

For example:
dim rst as DAO.recordset
set rs= currentdb("select * from cities", dbOpenDynaset, dbSeeChanges)
rs.FindFirst "City = ""New York"""
if Not rs.NoMatch Then
debug.print rs!city
else
debug.print "nothing found"
End If

When using the Access-backend this works, also if the recordset is empty. When using the SQL-database through ODBC this code generates an error on the .findfirst method if the recordset does not have records.
So, it the recordset is empty, then .findfirst does not work with SQL-odbc tables

This is unexpected. I COULD fix it by checking if the rs.eof and rs.bof is true but this is basically the same as a nomatch and it would require me to rewrite a LOT of modules just to overcome this. Also in my situation no records is a viable option which should trigger the "nomatch" procedure (in this example the debug.print "nothing found")

I was under the assumption - based on a few other projects on SQL-server that I am working on - that declaring the recordset explicitly as DAO (and adding dbOpenDynaset, dbSeeChanges to the openrecordset) would suffice to make it work?

I can't find any info on .findfirst not working on empty recordsets? (it clearly works when using a access-backend and DAO-recordset)

 

[JHB]

...
This is unexpected. I COULD fix it by checking if the rs.eof and rs.bof is true but this is basically the same as a nomatch and it would require me to rewrite a LOT of modules just to overcome this.

It can be discussed, after my opinion it is not the same.
In an empty recordset is no record to search in.
While nomatch is a result from search through all the returned records to find a match or not.
I always check for EOF before I start working/using a recordset.

 

[Zakraket]

You are right and I also normally check this.

But the question is not how to fix it, but why .findfirst works on a empty recordset with a DAO-recordset on a (linked) access-table, and not with a DAO-recordset with a linked SQL-table.

In this particular case finding no records at all should follow the same path as finding no records with the criteria in a filled rs.

This works

dim rst as DAO.recordset
set rs= currentdb("select * from cities", dbOpenDynaset, dbSeeChanges)

if not rs.bof and not rs.eof
  rs.FindFirst "City = ""New York"""
  if Not rs.NoMatch Then
    debug.print rs!city
  else
    debug.print "nothing found"
  End If
else
  debug.print "nothing found"
endif

The "problem" however is that the debug.print section in the Access-client is actually a lot of code, so the above solution duplicates that code (something I don't like) and I will have to go through a LOOOTT of code to fix all these occurances.

I have done SQL-migration before on existing Access-clients and never came across this. Could be I never had this exact codepath before (where the absence of records is as meaningfull as finding no results )...
Normally I just explicitly declare recordsets as DAO, and add the proper options to the openrecordset and off you go ..

I'd like to fix it with as little changes to existing code as possible

 

[Zakraket]

Problem solved, with use of EOF/BOF and as little adjustments as possible:

dim rst as DAO.recordset
set rs= currentdb("select * from cities", dbOpenDynaset, dbSeeChanges)

if not rs.eof and not rs.bof then 
    rs.FindFirst "City = ""New York"""
end if

if Not rs.NoMatch Then
    debug.print rs!city
else
    debug.print "nothing found"
End If

In this case the findfirst won't trigger if there are no records, and the rs.nomatch is still "not true", which means in both cases (no records found ór empty recordset) the code will go to the nomatch=true section

I was under the assumption that .nomatch could only be evaluated after a find-method, but that doens't seem to be so

Normally I would have done this

dim rst as DAO.recordset
set rs= currentdb("select * from cities", dbOpenDynaset, dbSeeChanges)

if not rs.eof and not rs.bof then 
    rs.FindFirst "City = ""New York"""
    if Not rs.NoMatch Then
        debug.print rs!city
    else
        debug.print "nothing found"
    End If
end if

but this would make it more difficult to enter the "nothing found"-code from the first if without duplicating the code

 

[pbaldy]

I realize the problem is solved, but thought I'd throw a thought out. Your method opens the entire table, which requires it all to be sent from the server. I would typically do:

set rs= currentdb("select * from cities WHERE City = 'New York'", dbOpenDynaset, dbSeeChanges)

and test for EOF. With this method, only matching records are sent over the network, not the entire table. With larger tables, you would see a performance difference.

 

[Zakraket]

Yes you are right, but in this case it's all existing code so implementing your suggestion would mean more extra code-adjustments to get an existing application to work (and would require more functional testing)

But I find your remark interesting about sending data over the network. As far as I know the Access-client still handles all data locally, which would imply that even for opening the rs with
set rs= currentdb("select * from cities WHERE City = 'New York'", dbOpenDynaset, dbSeeChanges)
Access still retrieves all data from the (meaning the whole table), makes the selection locally and then fills the recordset. Regardless of using a linked Access-backend or ODBC/SQL backend

I am aware of the remarks from Microsoft on the .find-methods (see help on .findfirst) that these methods are less efficient then using a proper SQL for the openrecordset so it's always better to use a small as possible recordset.

 

[pbaldy]

No, Access sends the SQL to SQL Server, which sends back only the matching records, if any. If you have a large table, you should be able to test and see the difference.

 

[Zakraket]

Thanks, I'll test this a bit

Would this mean that

do
 openrecordset("select * from tbl where id=" & lngID)
 do something
loop

is faster than

openrecordset("select * from tbl")
do
 .find "id=" & lngID
 do something
loop

 

[pbaldy]

If your find would always be the same ID, then yes without question (though probably only noticeable on larger tables). If you open the table and then look for different ID's in the loop, who knows.