Ways to read a recordset (1 Viewer)

mcescher

Busy as usual
Local time
Today, 06:48
Joined
Mar 19, 2018
Messages
28
Working on updating some old code, and I've come across some code that I'm wondering if it would be better to update, or does it matter?

I need to grab some information from a table, so the first example (old) set the recordset to the table and then uses the "FindFirst" method with some criteria. (simplified for this example)

Code:
	Set rsMine = db.OpenRecordset("tblMine")
	strSQL = "Part=" & PartNum 
	rsMine.FindFirst strSQL
	If rsMine.NoMatch Then
		'Some error message
	Else
		'Use the record that was found
	End If

The second example (new) puts the table and all the criteria together as a normal SQL statement and pulls it that way.

Code:
	strSQL = "SELECT TOP 1 FROM tblMine WHERE Part=" & PartNum 
	Set rsMine = db.OpenRecordset(strSQL)
	If not rsMine.EOF Then 'Or should I use rsMine.RecordCount = 0
		'Use the record that was found
	End If

Is there a difference or benefit to one way versus the other way?

It should be noted that this particular example is a single record, but sometimes I'm pulling multiple records and looping through them, if that makes a difference.

Thanks all!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:48
Joined
Aug 30, 2003
Messages
36,118
I would use the second method, as it only pulls the desired record(s) across the network. The first pulls all records, which would be a performance issue with larger tables.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:48
Joined
Aug 30, 2003
Messages
36,118
Oh, and I use the EOF test rather than recordcount.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:48
Joined
Oct 29, 2018
Messages
21,357
Hi. As a general rule, you would only want to pull as many records through the network as you need. So, if you need to update multiple records, one at a time, maybe pulling them all together at once may be appropriate. Otherwise, pulling one record only when you need it may be the way to go most of the times.
 

mcescher

Busy as usual
Local time
Today, 06:48
Joined
Mar 19, 2018
Messages
28
Thank you both. This specific example was just a single record, but I get pulling all the records at once. Just didn't want to pull the entire table when I just need a single record.

Oh the trials of renovating old code... hehehe

Thanks to Paul also for answering the EOF/RecordCount question.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:48
Joined
Oct 29, 2018
Messages
21,357
Thank you both. This specific example was just a single record, but I get pulling all the records at once. Just didn't want to pull the entire table when I just need a single record.

Oh the trials of renovating old code... hehehe

Thanks to Paul also for answering the EOF/RecordCount question.
Hi. Good luck with the clean up process. Cheers!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:48
Joined
Feb 28, 2001
Messages
26,996
The optimum way is to assure that there is an index on the table for that field (doesn't have to be unique) and then use the OpenRecordset on the SQL string that does the filtration. That covers all the bases including monolithic Access DB; split Access DB; and Access front end, SQL engine of some type on the back end.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:48
Joined
Feb 19, 2002
Messages
42,970
You didn't post the code that runs when a record is "found". It would almost certainly be more efficient to use an action query rather than a VBA loop (even the optimized one that only pulls records that WILL be updated).
 

Users who are viewing this thread

Top Bottom