DAO recordset effiency

Question

I have a form that consists of about 60 fields that all update when choosing a different customer from a drop down. I feel like my current way is not the best way as it takes about 30 seconds to update when changing customers. Currently I have:

Private Sub ClientSelection_AfterUpdate()

On Error GoTo errhandler

Dim dbTemp As DAO.Database
Dim rsTemp As DAO.Recordset
Dim strSQL As String


DoCmd.Hourglass True

sYear = Me.YearSelection.Value
    Me.CustomerID = Me.CustomerSelection.Column(0)
    CustomerID = Mid(Me.CustomerID.Value, 2, 36)

Set dbTemp = CurrentDb()
Set rsTemp = dbTemp.OpenRecordset("SELECT Field1 FROM Table where CustomerID = '" & CustomerID & "' and MonthYear = #01/01/" & sYear & "#")
Set rsTemp2 = dbTemp.OpenRecordset("SELECT Field1 FROM Table where CustomerID = '" & CustomerID & "' and MonthYear = #02/01/" & sYear & "#")


Me.JanCharges.Value = rsTemp("Charges")
Me.FebCharges.Value = rsTemp2("Charges")

rsTemp.Close
rsTemp2.Close
Set rsTemp = Nothing
Set rsTemp2 = Nothing
Set dbTemp = Nothing

Exit_ClientSelection_AfterUpdate:
Exit Sub


errhandler:
 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
    Resume Exit_ClientSelection_AfterUpdate

End Sub

Both of these commands basically happen 60 times (5 categories consisting of 12 fields(months)). Then even further down I close all the recordsets and set them to Nothing. All of this works just takes a little longer than I want. What is a more efficient to accomplish this?

Answer 1

I'm going to leave the ms-access specifics to more proficient ms-access reviewers, as I'm not all that familiar with DAO and Access VBA.

If possible, switch to SQL Server ADODB.

---

DEADC0DE

Unless I'm missing something, you're inlining the SQL statements and Dim strSQL As String is an unused declaration.

These can be hard to spot without an add-in though. MZ-Tools has a review source code feature that can find unused variables and procedures; Rubberduck has a code inspections feature that can find that too, and a number of other code issues (disclaimer: I own and maintain rubberduck, see tag info).

---

UX Issue?

DoCmd.Hourglass True

You're turning the hourglass on, but you never turn it back off. I would expect this:

Exit_ClientSelection_AfterUpdate:
    DoCmd.Hourglass False        
    Exit Sub

---

The error handler is problematic:

MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
    VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"

This cannot reliably be reporting the source of the error, unless there's only 1 module in your VBA project. Proof:

Module1

Public Sub Boom()
    MsgBox VBE.ActiveCodePane.CodeModule
End Sub

Module2

Public Sub DoSomething()
    Module1.Boom
End Sub

If Module2 is the opened module when you run DoSomething, the message will say Module2, not Module1 where the "error" would have happened.

And it requires adding a reference to the VBIDE extensibility library in your VBAProject, which requires special security handling. Not ideal.

Answer 2

Disclaimer: I don't know much about DAO. I typically use ADODB.

The expensive part of this code is opening and closing the recordset repeatedly. So, instead of opening and closing it every time this is triggered, open it once when the form is first loaded.

Private dbTemp As DAO.Something 'I don't know the right type
Private rsTemp As DAO.Recordset
Private rsTemp2 As DAO.Recordset

Private Sub Form_Load()
    ' code to open the entire recordset without a filter
End Sub

Private Sub Form_Unload()
    ' code to close and dispose of the recordsets
End Sub

Of course, now you don't have the specific record you're looking for when the event fires. You can use the Find and FindNext methods to filter the entire recordset down to what you need in the event procedure.

https://msdn.microsoft.com/EN-US/library/office/ff194787.aspx

---

Of course, this is if I can't talk you into switching to ADODB for your purposes here. It supports parameterized queries, which (in theory) should execute a bit faster. This is because, to the best of my knowledge, DAO requests all of the data and then filters it down in memory on the local machine. An ADODB parameterized query allows for the plan to be cached and only returns the results of the statement.

Dim rst As ADODB.Recordset
Dim cmd as ADODB.Command

Const sql as String = _
    "SELECT Field1 " & _
    "FROM Table " & _
    "WHERE CustomerID = ? and MonthYear = ?"

With cmd 
     .CommandType = adCmdText
     .CommandText = sql

     .Parameters.Append .CreateParameter(...)
     .Parameters.Append .CreateParameter(...)

     Set rst = .Execute
End With

' some code to set january's value

cmd.Parameters(1).Value = feburaryDate

Set rst = cmd.Execute

' some code to set february's value

Note how we simply pass the query a new value, instead of duplicating all of the query text? This is just one of ADODB's benefits.

---

One other, rather minor note.

I bet this code is all through your code base.

 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"

It's well worth extracting the logic into a dedicated module or class to standardize your error handling UI.

Answer 3

I wonder about the table design. It seems the CustomerID is a string whereas generally it is best to use long integers. Also has the CustomerID field in the table been indexed or set to primary key? Using a SQL where clause is much faster for filtering records that using findnext on a recordset, so do as much as you can in SQL. I note that the SQL statement is hard written into the openrecordset statement. I prefer the following Set rst = dbs.OpenRecordset(SQL, dbopensnapshot). SQL is a string variable and I can build the SQL statement up on the fly using VBA. For example in my application, the field list is fixed and only the where clause changes between cycles so I can write FieldList = "SELECT Field1, Field2, .. FROM Table " and then a separate Where Clause ", eg. "WHERE CustomerID = " & CustomerID. Then I create the SQL statement as follows SQL = FieldList & WhereClause. There are some advantages to doing things this way including being able to generate SQL statements that can be copied into empty queries and run to check they are working properly. Separating the field list from the where clause should let you change your code so that you can grab all needed fields from a table in one go. I tend to create a user defined type with all the fields that the form needs and populate the fields of the user defined types (~struct) from the database. This means that I can validate the data I am getting back from the database before anything is shown on the form. What if there is a null value where an integer is expected. By putting all the data into a struct first, I can confirm the correct data types and can run formatting code. Loading form fields from a struct is easy. Me.charges = s.charges. If I was a bit more modern, I would make a class module with properties for all the form fields, then I could use letters and getters to validate the data as it comes in.

The dbopensnapshot tells ms-access not to continuously check whether other users have edited the record so there is much less code running in the background. Is the MonthYear field in the database indexed.