[ad_1]
Working With Access VBA Recordsets Using DAO or ADO: Some More Key Points With VBA Recordsets
As a follow-on from another article where I covered first three key points of the traps and tips to look out for when using Access VBA DAO or ADO libraries calling the Recordset object, here are four more key points to note.
As a recap, the first three points mentioned included DAO versus ADO, testing the cursor moving methods through Recordsets and the default OpenRecordset method types.
Here are four more key points:
1. Seek and ye shall find
There are two method options to find matching data values. You can use either the Seek or Find (FindFirst, FindLast, FindNext, FindPrevious) methods.
The Seek method is quicker as it indexes against key ‘indexed’ fields to a table and not queries or SQL statements compared to the Find methods which scans criteria for a recordset making it slower though more relaxed in finding answers to your data.
As a good practice when finding records is to test for a match first before iterating through known records by using the NoMatch property to avoid runtime errors. For example:
rs.FindFirst "Country = ""UK"""
If Not rs.NoMatch Then
'carry out some VBA execution here...
End If
2. Creating a new record, where’s the cursor?
To add a new record you use the AddNew and Update methods wrapped around the field collection to a known recordset.
However, the cursor (i.e. where the insertion point is currently positioned) is not always where the new record instance resides when saving (updating) the record.
Therefore, to ensure that should you wish to continue to code to the newly added record, you will need to tell the system where to place the cursor to continue editing using the Bookmark and LastModified properties. For example:
rs.AddNew 'Fields here are set to values...
rs.Update rs.Bookmark = rs.LastModified
'Continue working with the new record here...
3. Take the garbage out! Close and clean up your code
One of my personal bug-bears with poor coders is the lack of clearing up after your own mess! When creating any objects, learn to dispose of them correctly when you no longer require them.
In fact, VBA is pretty friendly in this regard and will take out the garbage for you but that’s not the point. You may need to refer to the object reference elsewhere in your procedures and if the scope of object variables is not correct or you change it this can cause referencing issues.
Two places to dispose of your object variables; one inside the code routine and the other inside error handling routines too. For example:
Sub WorkingWithRecords
On Error Goto Err_WWR
Dim db as Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers")
'Some code here...
rs.Close 'Close the opened table.
db.Close
Exit_WWR:
Set rs = Nothing 'Set objects to nothing-ness.
Set db = Nothing
Exit Sub Err_WRR: 'Error handler here...
Resume Exit_WWR
End Sub
4. Compiling queries in VBA code?
In Access, you create a query using the query interface (known as the QBE – query by example grid) and Microsoft Access learns to compile this query for the first time around when running and saving the query.
Access VBA code uses the QueryDef object which is the way to create a query object in code and also compiles it too means it is slightly faster to run and execute.
This is a better practice than creating SQL statements on the fly and then executing them but like with various methods of approach there are pros and cons when executing query objects against SQL statements which I’m not going to discuss here.
The object reference is set using the CreateQueryDef method:
Dim qd As QueryDef
Set qd = CreateQueryDef(str_Name, ste_SQL)
There you have it, 4 more key points to DAO and ADO when VBA coding in Microsoft Access. It’s worth spending the time looking at the properties, methods and events to both libraries.
[ad_2]
Source by Ben S Beitler
