Update / Refresh TableDefs, or Count All Records in a Table Multiple Times (1 Viewer)

[Stormin]

Hi all,

When importing files to my Access tables I keep track of how many records have been added / removed at each stage by counting the number of records at different points and then finding the differences where I'm interested.

When I designed my import function I used CurrentDb.TableDefs(TargetTable).RecordCount all over to count records.
Recently I have come to understand that using CurrentDb in each instance is considered bad practice and I have since changed to declaring CurrentDb as an explicit variable. Retrospectively updating my code I now have:

    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Set db = CurrentDb()
    Set td = db.TableDefs(TargetTable)    
    
    rCount1 = td.RecordCount

This works for the initial count of the table, but when I then change the number of records in a table db.TableDefs(TargetTable) doesn't refresh itself and so .RecordCount still returns the number of records from when db was initially set. The reason it was working previously was because CurrentDb.TableDefs(TargetTable).RecordCount would create a new instance of CurrentDb each time it was called (or something like that).

So is there an elegant way to 're-set' or refresh the TableDefs to get the latest .RecordCount? The only way I've found is to repeat the code

    Set db = CurrentDb()
    Set td = db.TableDefs(TargetTable)

before each time I want to recount, which is not ideal.

Is this even the most efficient option for counting all the records in a table? I've tried out DCount("*", TargetTable) but some of the tables have 800k+ records and this function takes ~3s each time with those, compared against td.RecordCount which takes <0.1s each time.

Cheers

 

[isladogs]

Recently I have come to understand that using CurrentDb in each instance is considered bad practice and I have since changed to declaring CurrentDb as an explicit variable.

No doubt someone else will disagree with me but I can't see the benefit in doing this

Is this even the most efficient option for counting all the records in a table? I've tried out DCount("*", TargetTable) but some of the tables have 800k+ records and this function takes ~3s each time with those, compared against td.RecordCount which takes <0.1s each time.

Using DCount is slow - avoid where there is a better alternative as in this case

 

[Stormin]

Explicitly declaring the database being better practice is a general attitude I've picked up from bits and bobs I've read all over. A search through my history provided one example here.
Another advantage seems to be to avoid ambiguous declarations between DAO and ADO, as mentioned on Allen Browne's website.

Since my original post I have also learnt that using TableDefs.RecordCount can be unreliable at times:

Be careful with the use of CurrentDb.TableDefs(“TABLENAME”).RecordCount option. It’s only available to the system when it is using a certain cursorType in your calls to the database. The default cursorType is Forward-Only and will not show the RecordCount.

So I am going to change my record counts to use Database.OpenRecordset.RecordCount instead to ensure accuracy. For each count I will also explicitly create and destroy the database object. Easiest to do this in a Function, then I can catch linked tables too.

I wrote the following this morning and it seems to behave in all cases I throw at it. Execution time for both local and linked tables (tested on 800k+ records) on DBs on a network was less than 0.1s.

Public Function CountAllRecords(TableToCount As String, Optional ByVal DatabasePath As String) As Long
    
    On Error GoTo Err_Handler
    
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim rc              As Long
    Dim strDbName       As String
    Dim strDbNewPath    As String
    Dim strConnect      As String
    Dim DetectDbPath    As Boolean
    
    Const strProcName   As String = "CountAllRecords"
    Const myErr1        As Long = vbObjectError + 1001
    Const myErr2        As Long = vbObjectError + 1002

Start_Proc:

    'Check if db path has been specified
    If Len(DatabasePath) > 0 Then
        Set db = OpenDatabase(DatabasePath, ReadOnly:=True)
    Else
        Set db = CurrentDb()
    End If
    
    'Check if it is a linked table in the db.
    'If it is, then find the db path and use it.
    'NB that TableDefs.RecordCount should not be used for counting
    strConnect = db.TableDefs(TableToCount).Connect
    If Len(strConnect) > 0 Then
        'It is a linked table.
        
        'Check we have not already done this
        If Not DetectDbPath Then
            DetectDbPath = True
        Else
            'We have already done this, and are probably in a loop
            Err.Raise myErr1, _
                Description:="DetectDbPath loop."
        End If
        
        'Grab the db path from the connection string
        strDbNewPath = GetDbPathFromConnString(strConnect)
        
        If Len(strDbNewPath) > 0 Then
            'We have successfully determined the db path
            DatabasePath = strDbNewPath
            GoTo Start_Proc
        Else
            'We have a connection string with no detectable db path in it
            Err.Raise myErr2, _
                Description:="Could not determine Db path from Connection string:- " & vbNewLine & strConnect
        End If
    End If
    
    
    'Now that we have the correct database, count the records
    Set rs = db.OpenRecordset(TableToCount, Options:=dbReadOnly)
    With rs
        If Not (.BOF And .EOF) Then
            .MoveLast
            .MoveFirst
            rc = .RecordCount
        Else
            'Recordset is empty
            rc = 0
        End If
        .Close
    End With

Exit_Proc:
    CountAllRecords = rc
    Set rs = Nothing
    Set db = Nothing
    Exit Function
    
Err_Handler:
    rc = -2 'return an error value
    Select Case Err.Number
        Case 3078, 3265
            'TableToCount was not found in the target database
            If db.Name = CurrentDb.Name Then
                strDbName = "CurrentDb()"
            Else
                strDbName = db.Name
            End If
            MsgBox _
                "The table """ & TableToCount & """ was not found in the database:-" & vbNewLine & _
                strDbName, _
                vbOKOnly, _
                "Error " & Err.Number & " in " & strProcName
            Resume Exit_Proc
        Case 3024, 3044
            'DatabasePath is not a valid file path
            MsgBox _
                "No database was found at the location:-" & vbNewLine & _
                DatabasePath, _
                vbOKOnly, _
                "Error " & Err.Number & " in " & strProcName
            Resume Exit_Proc
        Case Is < 0
            'Custom error
            MsgBox _
                Err.Description, _
                vbOKOnly, _
                "Error " & Err.Number - vbObjectError & " in " & strProcName
            Resume Exit_Proc
        Case Else
            'Something unforseen happened
            Debug.Print "*** START *** - " & Now
            Debug.Print "Procedure """ & strProcName & """"
            Debug.Print "Error " & Err.Number
            Debug.Print Err.Description
            Debug.Print "***  END  ***"
            MsgBox _
                "UNFORSEEN ERROR: Please report to DB admin." & vbNewLine & _
                Err.Description, _
                vbOKOnly, _
                "Error " & Err.Number & " in " & strProcName
            Resume Exit_Proc
    End Select
    
End Function

Public Function GetDbPathFromConnString(ConnectionString As String) As String
    
    Dim p As String 'DatabasePath
    Dim a As Variant 'Array
    Dim i As Integer
    
    a = Split(ConnectionString, ";")
    
    For i = LBound(a) To UBound(a)
        If UCase(a(i)) Like "DATABASE=*" Then
            p = Trim$(Mid$(a(i), 10))
            Exit For
        End If
    Next i
    
    GetDbPathFromConnString = p
    
End Function

 

[isladogs]

To be honest I was surprised nobody came back to contradict my original reply.
The fact that no-one did so, may indicate that the view I expressed is the consensus...?

Explicitly declaring the database being better practice is a general attitude I've picked up from bits and bobs I've read all over. A search through my history provided one example here.
Another advantage seems to be to avoid ambiguous declarations between DAO and ADO, as mentioned on Allen Browne's website.

If Allen Browne says something, its bound to be true...
However, I almost always use DAO & always explicitly define recordsets anyway

Since my original post I have also learnt that using TableDefs.RecordCount can be unreliable at times:

I read that link but I can't say I've any experience of it being unreliable personally.
It might be worth testing each method on a number of different tables / queries & getting both the count and the time taken

In fact I read an article recently by someone who did exactly that:
http://rogersaccessblog.blogspot.co.uk/2009/04/what-is-fastest-way-to-return-record.html

 

[Stormin]

To be honest I was surprised nobody came back to contradict my original reply.
The fact that no-one did so, may indicate that the view I expressed is the consensus...?

Maybe; however shouting into an empty room would give the same result

It might be worth testing each method on a number of different tables / queries & getting both the count and the time taken

In fact I read an article recently by someone who did exactly that:
http://rogersaccessblog.blogspot.co.uk/2009/04/what-is-fastest-way-to-return-record.html

I actually read that article in my research (there are a few more people that have conducted similar experiments) which is what contributed to my conclusion that scanning the recordset was the best approach for my application.

 

[isladogs]

Maybe; however shouting into an empty room would give the same result

Ha ha... hope you're not implying there's nobody out there at this forum...

 

[Minty]

Is that the same as the sound of one hand clapping ?