Topics Search

Loop Through Dao Recordset

Loop Through Dao Recordset
Views: 9
I would like to understand what is necessary and what is not in code to loop through a DAO recordset. I have seen differing solutions.
Just in case there are differences based on the environment, this is a SQL Server back end.

Assume we have a recordset, I.e. Set rs = CurrentDB.OpenRecordset("SELECT . WHERE ADate > #1/1/2012#", dbOpenSnapshot, dbReadOnly)

--- The heart of the loop (I hope we will agree on this as being true)
Do Until rs.EOF
:
: process records
:
rs.MoveNext
Loop

Test that there are records in the recordset, if there are 0 records do not to execute the loop. I'm thinking Do Until rs.EOF takes care of this. Example of testing:
If rs.RecordCount = 0 Then .
- or -
If rs.BOF and rs.EOF Then
: 'Do not execute the loop, do something else
End If
Sponsored Links:

More topics

 

DAO.Recordset Vs ADODB.Recordset Vs Recordset

Preview
So I know that there are two types of libraries for retrieving recordsets, either DAO or ADO.
I read somewhere that if you are using DAO, you should use DAO.recordset instead of just "recordset" to make sure it run properly.
E.g.
Dim rs As DAO.Recordset
instead of
Dim rs As Recordset

but if the default is DAO anyways (I think), would it really cause any problems. (I'm using Access 2003)

So now, which is better DAO or ADO if I'm only using a recordset to update a single field of a record from a table that may contain hundreds of records.

Which is better in general? What exceptions are there? What do other people use
 

simple recordset loop?

Preview
here? I have this simple code-

Dim db As DAO.Database Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblReturnList", dbOpenDynaset)
j = rs.RecordCount
For I = 1 To j
'##collect data from table or query##
strGetData = rs!fldFirstName
MsgBox strGetData
rs.MoveNext
Next I

it only ever returns the value of 1. If I add rs.MoveLast above the rs.RecordCount, I get the record count but then the loop fails on the msgbox as it is at the end of the recordset.

All im trying to do is loop through the records in the recordset and display a messagebox with the name. Hardly a complicated thing and ive done it many times but feel I might be a little rusty under the arches lol.

Any quick help available
 

rs.findfirst with another dao recordset

Preview
What would be the most optimum way to .findfirst on a dao recordset with the criteria from another dao recordset? I'll try to explain more with code that may not be right but you should get the idea.

Dim rs as dao.recordset, rs2 as dao.recordset, db as database, db2 as database set db = currentdb set db2 = opendatabase("Path to DB", False, False, ";PWD=###")
set rs = db.openrecordset("Table Name", dbopendynaset) set rs2 = db2.openrecordset("Table Name", dbopendynaset)
'// Finds records marked for deletion. Deletion equals 20 rs.findfirst "[Record Status] = 20" do while rs.nomatch = false
rs2.findfirst rs
rs2.delete rs.movenext loop rs.close rs2.close set rs = nothing set rs2 = nothing set db = nothing set db2 = nothing
 

Loop through recordset and run query

Preview
I am using the following code based on Bob Larson's sample on his website

Dim db As DAO.Database Dim rst As DAO.Recordset
Set db = CurrentDb Set rst = db.OpenRecordset("qry_RecNumberCollect")
Do Until rst.EOF
DoCmd.OpenQuery "qry_RecNumberUpdate"
rst.MoveNext Loop
rst.Close Set rst = Nothing

When I run it I get an error that says error 3601 too few parameters expected 1

When I click the debug button the followin line of code is highlighted.

Set rst = db.OpenRecordset("qry_RecNumberCollect
 

Update table with values from another table

Preview
I am trying to update values in a table with the correct values from another table. The first column is an autonumber field and is linked to another table and so I cannot just delete the records and add new ones.
I want to loop through table A and insert records to table B that have the same autonumber (basically, 1 to 20)

eg.

table A
1,ian,smith
2,paul,jones
3,kevin,thomas

I want to loop through and amend the last two fields to;

table B
1,john,evans
2,paul,davis
3,ringo,starr

This is my code;

Dim db As DAO.database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("t_A")
Set rs2 = db.OpenRecordset("t_B")
rs1.MoveFirst
Do Until rs1.EOF
rs2.AddNew
rs2![name] = rs1![name]
rs2.Update
rs1.MoveNext

Loop

rs2.Close
Set rs2 = Nothing
Set db = Nothing

End Sub

This just adds three more names and doesn't amend the current ones
 

Correct code for opening a recordset from another database?

Preview
Can anyone write a quick sub that basically opens a recordset from another database, iterates through them all, then closes correctly? My code isn't working properly:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = OpenDatabase("\\path\otherdb.mdb")
Set rst = db.OpenRecordset("SELECT * FROM Table_Name")

Do While Not rs.EOF
MsgBox rs("Col_name")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

I'd also like to use ADODB
 

Recordset loop + display all?

Preview
I've never tackled a recordset loop before in vba and have no idea what I'm doing - I tend to work best with a very basic example and rip it apart from there to make it do what I want through trial and error!

To that end, assuming I have a table called tNames and in it are the following fields:

NameID - Autonumber
Name - Text

If I wanted to just loop through that recordset and echo out the results from both fields for all records, how would I go about doing it
 

Do while ... Loop

Preview
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String
Set rst1 = CurrentDb.OpenRecordset("HtoROrignial")
Set rst2 = CurrentDb.OpenRecordset("subfolder")
Do While Not rst1.EOF
Do While Not rst2.EOF
If rst2("Fname") <> 0 Then
strSQL = "INSERT INTO subfolder (notes) VALUES ('this works')"
DoCmd.RunSQL
End If
rst2.MoveNext
Exit Do
rst1.MoveNext
Exit Do
End Sub

Hi, above is my code, it isn't running the SQL, and there is a problems with the loop (is this because you can't have a loop within a loop?)

I basically want the code to search through all of the values in table 1, against all of the values in table 2 to see if the field Fname matches the field Fname in table 1 (which is what the if will eventually do, just want the loop to work first)
 

Modify recordset

Preview
The following code is giving some problem.Please can anybody help to modify the code.

Private Sub Command0_Click() Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Set MyDB = CurrentDb() Set MyRS = MyDB.OpenRecordset("tbldata", dbOpenForwardOnly)
I = 51372911 Do While Not MyRS.EOF
MyRS.Edit
MyRS![ID] = I
MyRS.Update
I = I + 1 Loop End Sub
 

rs.PercentPosition Bug

Preview
I have a VB code block that takes several minutes to run. To prevent the user from getting impatient and cancelling execution (which could potentially mess up 2-3 Tables in the DB), I've been trying to set up a rudimentary progress bar using the .PercentPosition Property of DAO Recordsets.

The code block has three loops in it where it steps through several Recordsets (each containing thousands of Records). The Property works fine through the first loop, but once it gets to the second loop (which uses a different Recordset) it stops working and always returns 0%.

The third loop uses yet another Recordset and .PercentPosition always returns 0% on it too. In the code, I never to anything with .PercentPosition except read the value into a Form field. I never attempt to modify it in any way.

Also, as far as I can tell, the Recordsets continue function properly in all aspects other than their .PercentPosition Property. Has anyone else run into .PercentPosition just breaking like that?