Loop Through Dao Recordset
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
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
Thank you for taking the time to report an issue.
What's wrong... Please write below.
I read somewhere that if you are using DAO, you should use DAO.recordset instead of just "recordset" to make sure it run properly.
Dim rs As DAO.Recordset
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
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
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
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.delete rs.movenext loop rs.close rs2.close set rs = nothing set rs2 = nothing set db = nothing set db2 = nothing
Dim db As DAO.Database Dim rst As DAO.Recordset
Set db = CurrentDb Set rst = db.OpenRecordset("qry_RecNumberCollect")
Do Until rst.EOF
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
I want to loop through table A and insert records to table B that have the same autonumber (basically, 1 to 20)
I want to loop through and amend the last two fields to;
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")
Do Until rs1.EOF
rs2![name] = rs1![name]
Set rs2 = Nothing
Set db = Nothing
This just adds three more names and doesn't amend the current ones
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
Set rs = Nothing
I'd also like to use ADODB
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
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')"
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)
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![ID] = I
I = I + 1 Loop End Sub
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?