Access VBA

css navigation by Css3Menu.com

Capture Record Counts

I like elegant solutions to tough problems. In the program we were writing, we begin with millions of rows and after applying lots of edits, we are down to 30K rows.

We wanted something to count the records to a table along the way so we could see what the effects of various edits did.

Public Function UpdateValTable(ValType As String, ProcTable As String)
    Dim sqlString       As String
    Dim DB              As Database
    Dim Builder         As Recordset
    Dim SetUpdt         As Recordset
    Set DB = CurrentDb
    On Error GoTo ErrHandleS
    sqlString = "SELECT COUNT(*) as RecordCount FROM " & ProcTable
	'Count records in Target Table
    Set Builder = DB.OpenRecordset(sqlString, dbOpenDynaset)
    Set SetUpdt = DB.OpenRecordset("tblValidation", dbOpenDynaset)
	'Get table to write counts
        SetUpdt.AddNew
            SetUpdt!Type = ValType
            SetUpdt!Quantity = Builder.Fields("RecordCount")
            SetUpdt!UserName = UserName()
        SetUpdt.Update
    Set Builder = Nothing		' Clean up
    Set SetUpdt = Nothing		' Clean up
    GoTo Exit_SomeName
    Exit Function
Exit_SomeName:                          ' Label to resume after error.
     Exit Function                          ' Exit before error handler.
ErrHandleS:                            ' Label to jump to on error.
    Select Case Err
    Case 9999                         ' Whatever number you anticipate.
        Resume Next                   ' Use this to just ignore the line.
        Resume Exit_SomeName          ' Use this to give up on the proc.
    Case Else                         ' Any unexpected error.
        Call LogError(Err, Error$, "UpdateValTable()")
        Resume Exit_SomeName
    End Select
End Function
 '+----------------------------------
 'Call your function from inside any mod using: 

Call UpdateValTable TheNameofType, Table2PullFrom

In this case, we have a pre-built table called “tblValidation” with 4 columns; Type, Quantity, UserName, and Date. Date is set with default value of NOW().

To access the function, I use Call UpdateValTable TheNameofType, Table2PullFrom within my code. I can use it anywhere to write a Validation record.


© 2011-2024

Updated:  01/23/2024 13:34
This page added:  03 March 2011