naqayya
asked on
Count records in a recordset (VBA)
I have a recordset called rsMerge in my code and I want to get the number of records in that recordset so that I can do a test.
If the number of records is 1 the program flow will be different, and if more than 1 then the flow will be different.
I have tried using .MoveLast and .RecordCount but this is extremely slow, and I have 300,000 records to process.
I need something much faster! Please can anyone help?
If the number of records is 1 the program flow will be different, and if more than 1 then the flow will be different.
I have tried using .MoveLast and .RecordCount but this is extremely slow, and I have 300,000 records to process.
I need something much faster! Please can anyone help?
ASKER
thanks Ricky,
sorry, i don't understand how to use rsMerge.Movenext to count the number of records in rsMerge. Could you please explain?
Thanks.
sorry, i don't understand how to use rsMerge.Movenext to count the number of records in rsMerge. Could you please explain?
Thanks.
ASKER
Ricky,
the DCount function does not work with recordsets, only tables or queries.
Here is the code i am using:
With rsMerge
.MoveLast
If .RecordCount = 1 Then
CurrentDb.Execute "INSERT INTO tblDonationAllocations_New
CurrentDb.Execute "INSERT INTO tblDonationAllocations_Don
ElseIf .RecordCount > 1 Then
.MoveFirst
SumAmount = 0
Do While Not .EOF
CurrentDb.Execute "INSERT INTO tblDonationAllocations_Don
SumAmount = SumAmount + !Amount
.MoveNext
Loop
CurrentDb.Execute "INSERT INTO tblDonationAllocations_New
End If
.Close
End With
Any more suggestions? Thanks.
the DCount function does not work with recordsets, only tables or queries.
Here is the code i am using:
With rsMerge
.MoveLast
If .RecordCount = 1 Then
CurrentDb.Execute "INSERT INTO tblDonationAllocations_New
CurrentDb.Execute "INSERT INTO tblDonationAllocations_Don
ElseIf .RecordCount > 1 Then
.MoveFirst
SumAmount = 0
Do While Not .EOF
CurrentDb.Execute "INSERT INTO tblDonationAllocations_Don
SumAmount = SumAmount + !Amount
.MoveNext
Loop
CurrentDb.Execute "INSERT INTO tblDonationAllocations_New
End If
.Close
End With
Any more suggestions? Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
example for the DCount:
assuming the sql-statement for the rsMerge is like this:
set rsMerge= db.openrecordset("Select Field1, Field2 from YourTable where Field3 = 'YourCriteria';")
then u can use the dcount like this:
var = DCount("Field1","YourTable
var will now hold the value of the amount of records found.
assuming the sql-statement for the rsMerge is like this:
set rsMerge= db.openrecordset("Select Field1, Field2 from YourTable where Field3 = 'YourCriteria';")
then u can use the dcount like this:
var = DCount("Field1","YourTable
var will now hold the value of the amount of records found.
..or you could check if there is on ore more record by
1 Go to first record
2.Go on record forward and siee if EOF=true
Function testing()
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("table1")
If rs.BOF Then 'check if no records
MsgBox "no records at all"
Exit Function
End If
rs.MoveFirst 'go to frist
rs.MoveNext 'go to second
If rs.EOF Then 'check if one record
MsgBox "1 records only in this recordset"
Exit Function
End If
End Function
1 Go to first record
2.Go on record forward and siee if EOF=true
Function testing()
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("table1")
If rs.BOF Then 'check if no records
MsgBox "no records at all"
Exit Function
End If
rs.MoveFirst 'go to frist
rs.MoveNext 'go to second
If rs.EOF Then 'check if one record
MsgBox "1 records only in this recordset"
Exit Function
End If
End Function
Hi all,
Just miss in the Q and comments the "empty" situation or no records at all.
I guess the code for filling the recordset is also like:
set rsMatch = dbcurrent.openrecordset("S
To speed thing up you are better off with defining a query like:
"SELECT ... FROM ... WHERE X= [parameter];"
This query can be "compiled" by access on forehand and will thus execute faster. You will need to specify the parameter in the code..
perove's .movenext is also faster as a movelast, but when the number can be one, then the total won't be that enormous too, that's why the slow response looks odd.
I guess the .movelast won't differ much as the prime selection will be the most time consuming. Jusrt use the single step mode to execute your code and you get a first impression where the time is consumed....
And check for EOF & BOF to make sure records are there.
Nic;o)
Just miss in the Q and comments the "empty" situation or no records at all.
I guess the code for filling the recordset is also like:
set rsMatch = dbcurrent.openrecordset("S
To speed thing up you are better off with defining a query like:
"SELECT ... FROM ... WHERE X= [parameter];"
This query can be "compiled" by access on forehand and will thus execute faster. You will need to specify the parameter in the code..
perove's .movenext is also faster as a movelast, but when the number can be one, then the total won't be that enormous too, that's why the slow response looks odd.
I guess the .movelast won't differ much as the prime selection will be the most time consuming. Jusrt use the single step mode to execute your code and you get a first impression where the time is consumed....
And check for EOF & BOF to make sure records are there.
Nic;o)
yep,
movenext is faster than movelast,
as i commented in the first post
:-)
Ricky
movenext is faster than movelast,
as i commented in the first post
:-)
Ricky
ASKER
Solved the problem using .MoveNext and (mainly) setting indexes on the main table.
Thanks.
Thanks.
have u tried to only use rsMerge.Movenext ? (should work)
or else u could use the DCount-function:
var = DCount("SomeField","YourTa
hope this helps,
Ricky