Recordset Count Problem

[Tigerlili3]

I have the following code to obtain the number of records in a table and use that in the table name when it is exported to another database. It works except the record count is low by 5 records every time, even if new records are added. If the count should be 5075, it shows up as 5070. If three records are added and it should be 5078, it shows up as 5073. Can anyone tell me why this is happening?

Thanks!

Function RecordCount()
    Dim rstProblems As DAO.Recordset
    Dim xCount As Variant
   
        Set rstProblems = CurrentDb.OpenRecordset("tblLogs", dbOpenTable)
        rstProblems.MoveLast
        xCount = rstProblems.RecordCount
        rstProblems.Close
    
    DoCmd.TransferDatabase acExport, "Microsoft Access", _
    "K:\Folder\MSAccess\Backup Versions\BackupTblLog.mdb", acTable, _
    "tblLogs", "tblLogs" & Format(Now(), "mmddyy") _
    & "_" & xCount, False
    
    Set rstProblems = Nothing
    Set xCount = Nothing
End Function

 

[PHV]

Any chance tblLogs is open by another process at same time ?
Seems like a write cache issue.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Or are you by any chance using autonumbers, and compare the recordcount with the last autonumber?

If so, remember autonumbers are only a mean of getting an incremented value, not a recordcounter. If a record is cancelled prior to being added (getting 5050), that number is used, and next record will get the next increment (5051) creating a gap.

For Access questions, do a forum search, there are 7 pure Access fora, dedicated to different Access issues/topics;-)

Roy-Vidar

 

[Tigerlili3]

Thanks for your replies! I don't think the table is being used by another process at the same time because the task that does the record count is scheduled to run at 5:30am. No one is here then and there are no other processes scheduled to run against it then either. It is strange that at any time of day, it is off by 5 records. Is there anything else that could cause caching issues?

 

[Tigerlili3]

Problem solved! Instead of using .recordcount, I used the Dcount function to count the records in a required field. Now the code looks like this...

Function Export_Daily()

Dim xCount As Integer
'obtain record count in table using the state field
    xCount = DCount("State", "tblLogs")

'export back table for logs
    DoCmd.TransferDatabase acExport, "Microsoft Access", _
    "K:\ORKAND\MSAccess\Backup Versions\BackupTblLog.mdb", acTable, _
    "tblLogs", "tblLogs" & Format(Now(), "mmddyy") _
    & "_" & xCount, False    
    
End Function