Null Disquisition

So here’s a fun fact – when you are browsing data displayed in a Continuous Form or a Datasheet, Access takes it upon itself to requery the database a gagillion times to make sure you have the latest token of data.

After a little experimenting, I found that: clicking into a field, mouseing over a button, or scrolling through results causes Access to query the database. I confirmed this using Windows’ network manager, as well as monitoring the database itself. Since I am going to be utilizing continuous forms quite often, I needed to find a fix for this.

For you impatient types, the short answer is ADODB. Read on for explaination.

Problem: Need to cache recordsets so Access won’t kill the server when using a continuous form or datasheet.

Solution: Manually set the recordset for my sub-form using an ADODB connection and recordset.

My first attempt at this led me to the fillCache method for a recordset. Microsoft’s documentation is less than inspiring (http://msdn.microsoft.com/en-us/library/bb221030.aspx), but the basic idea is you set the cursor location (bookmark) and the number of records you want to cache, and the Jet engine will handle the rest. Well, this is not really all that true. I was suprised to see very little documentation, or discussion about the fillCache method on the interwebs, but I can only assume that’s because it doesn’t really work.

I’ll spare you the 2 hour journey through google search results and books off my shelf.

Code:

Open the Connection

Dim con AS ADODB.Connection
Dim rs AS ADODB.Recordset
Set con = New ADODB.Connection
con.open "Data Source=MyDataSource;" _
& "Initial Catalog=MyDB;" _
& "User ID=joepublic;" _
& "Password=foobar"

Create the load the recordset

Set rs = New ADODB.Recordset
rs.CursorLocation = dbUseClient
rs.Open "SELECT fields FROM table WHERE id = " _
& Me.id_from_parent_form, con

Clean up and update the recordset of the form

Set rs.ActiveConnection = Nothing
con.Close
Set Me.sfrmSomeSubForm.Form.Recordset = rs
rs.Close
Set rs = Nothing

Now, the ADODB connection and recordsets are closed and gone, but the data remains in memory. Huzzah! N.B., there are several options for the Open method of the ADODB Recordset which should not be overlooked. You can find more info on that in the VB help files if you search for “ado recordset open”.

Cheers