Persistent Caching Of Combo Boxes etc

Take a Load Off Your Server with Access 2007 or Access 2003.  Includes examples of using XML in ADO.

Access 2000 allows you to generate persistent recordsets that can be stored on a local drive. This article demonstrates how to use these local recordsets to cache information for display in ComboBoxes or ListBoxes on Access forms.


Figure 1  – The form that handles all the caching options in this article.  The numbered points on the Left Hand Side of figure 1 will be used for reference throughout the article.

An example of why this is important is a ComboBox that displays U.S. Post Office Zip Codes. If 25 local machines query the network database to get these Zip Codes every time they open a particular form, data traffic would slow the server unnecessarily. Using persistent recordsets, this can be handled locally. The data can then be updated every week, or on demand.

1) Creating A RecordSet For Caching Using ADO

Caching with ADO is handled using a method that allows you to save the current recordset to a local file.   Before you can use this method, you first need to define a recordset and connect to a data source as follows.  The simplest method for defining this connection in Access 2000+ is using the CurrentProject object.

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection

rst.Open "zipCodes"

In this example, a table of zip codes are being cached to a file on the local drive.   Prior to saving the recordset, the cached file must be deleted it if it exists.

On Error Resume Next

Kill  "c:\my documents\cache\" & "zipCodes.rst"

Now the actual task to save the recordset to disk is the recordset save method

rst.Save "c:\my documents\cache\" & "zipCodes.rst", adPersistADTG

When the file is saved to disk, there  is only one format available using the standard version of Access 2000.  This is a proprietary format called Advanced Data TableGram (ADTG).  This is a binary format and is only decipherable using the open recordset options described in section 2 below.

If you install version 2.2 of ADO on your PC, you will be able to use the open Extensible Markup Language (XML) format.  This approach is useful as it is a very easy way to turn recordsets into this very important interchange format.   Whether it actually benefits the caching process is debatable as files are 50% bigger and they require the users of your software to install the 6 megabyte ADO upgrade on their computer.  In the sample database, there is a toggle button to switch between saving the file as XML or ADTG format.  The save method for XML is as follows.

rst.Save "c:\my documents\cache\" & "zipCodes.rst", adPersistXML

A sample of XML is shown as follows   (Editor This Is an optional part of the article)

To manage the external data files, you will need to handle the caching directory and the filenames through constants or variables (so that you only store their definition once).   The sample database uses global constants as follows

Public Const CacheDir =  "c:\my documents\cache\"

Public Const FileType = ".RST"

Public Const dataSource = "zipCodes"

2) Adding the RecordSet To The Combo Box

Now we will retrieve the cached recordset and add it as a rowsource to the combo box.   This is achieved by looping through the recordset and building a value string that we add to the combo box at the end of the recordset.   For this example I have also added Row Headings to the output string.

Set rst = New ADODB.Recordset

rst.Open CacheDir & dataSource

' Add the row headings to the row source string

strRowSrc = rst(0).Name & ";" & rst(1).Name & ";"

' Loop through the recordset and build the

' rowsource as if it had been created from a table

Do Until rst.EOF

strRowSrc = strRowSrc & rst(0) & ";" & rst(1) & ";"

rst.MoveNext

Loop

' Now add the string to the Row Source property

Me.cboManual.RowSource = strRowSrc

' Close the persisted recordset

rst.Close

To make this example more universal, I refer to the names of the recordset columns and names using the column number as follows  recordset(ColumnNumber).name.  Note also the use of a semi colon to separate the data columns in the row source.

3) Setting Up The Combo Box For Cached Recordsets.

To make the combo boxes work in this example, I set the following properties

Row Source Type = Value List

Row Source = NULL

Column Count = 2

Column Heads = Yes

4) Making your own smart Combo Box to handle the caching

The manual caching system that I have described in steps 1 to 3 works OK except for two major issues.  The first one is that the value list property will only accept a maximum length of 2048 characters.   The other one is that the caching mechanism must wait until you build the full row source string before it displays any data.  Testing the technique described in section 2 on 10000 zip codes showed that the combo box would have taken 1 minute to respond to the locally cached file.

So this brings me to smart combo and list boxes. When I first ran into the 2048 character restriction when programming my data mining shareware, the Access error message pointed out that you could actually build your own function for passing data to the combo box.  To find this elusive help,  ask the answer wizard to display information on “Value List” and choose “Create A List Box that retrieves its rows from a function”.  You will need this help to expand on issues in this article.

So how do these smart combo box functions work.  Although the documentation is a little scant, what you are doing is setting up a function that is called many different times to retrieve all the  information required by the combo box.  You do not actually have to know how it does this but you need to respond with the correct information for each of the different calls made to the function.   The arguments for the function are as follows

Function ZipCodes_FX(fld As Control, id As Variant, _

row As Variant, col As Variant, code As Variant) As Variant

The Code argument informs the function what the combo box Access requires in the current iteration and Col and Row which tells you what data to pass back for the column and row that the Combo Box  wishes to display.

To make this function the data source for your Combo Box, change the familiar Row Source Type property from  Table/Query or Value List or Field List and add the function that I have written called “ZipCodes_FX”  When the Access form first opens, the combo box will call that function and start up the initialization sequence using the Code argument as follows

Select Case Code

Case acLBInitialize       ' Initialize.

In this initialization sequence, the function reads the cached recordset and stores the information into an array. As it is essential that the values in the array remain in place each time the function is called, the array is dimensioned as Static.

Set rst = New ADODB.Recordset

rst.Open CacheDir & dataSource & FileType

'   Add the row headings to the output

tablesArray(Entries, 1) = rst(0).Name

tablesArray(Entries, 2) = rst(1).Name

Entries = 1

'   With the recordset open, we want to build an array

'   with two items in it that will be displayed in the

'   the combo box

Do Until rst.EOF

tablesArray(Entries, 1) = rst(0)

tablesArray(Entries, 2) = rst(1)

rst.MoveNext

Entries = Entries + 1

Loop

'   Now we close the persisted recordset

rst.Close

ReturnVal = Entries

Now the combo box uses the Code argument to ask the function to return other useful information such as the number of rows and columns in the data array.

Case acLBOpen

' Generate unique ID for control.

ReturnVal = timer

Case acLBGetRowCount

' Get number of rows.

ReturnVal = Entries

Case acLBGetColumnCount

' Get number of columns.

ReturnVal = 2

Case acLBGetColumnWidth

' Column width -1 forces use of default width

' You can override this property on the combo

ReturnVal = -1

But the most important step is when Access asks the function to return the information from the array.  When the combo box has all its values, the function is called one last time where it is good coding practice to clear the Static Array from memory using the Erase statement

Case acLBGetValue

' Returns the data one array row at a time

ReturnVal = tablesArray(row, 1 + col)

Case acLBEnd

' On completion, clear the static array

Erase tablesArray

End Select

ZipCodes_FX = ReturnVal

5) List Boxes Work The Same Way.

You can also set up smart list boxes to add the cached file to the row source of a List Box by utilizing the same function.   The property that handles this is set as follows

Row Source Type = ZipCodes_FX

6) Clearing The Cache

To reduce the complexity in section 4 of this article, I neglected to mention that we need to handle situations where the cached file does not exist.  This handled in the ZipCodes function using error handlers as follows.

On Error GoTo BuildCache

CacheBuilt:

The code that I use to rebuild the cache is exactly the same as illustrated in section 1) of this article.  When the cache recordset is rebuilt, the module return to the next line in the smart combo box code by using a good old fashioned goto statement.

Whilst I was building the software for this article, I came up with many different ideas for deciding whether a recordset needed to be refreshed.  Finally I decided that every system probably will be different so I made up this simple rule

“If the cached file does not exist, then the smart combo box function should refresh the recordset. ”

This way refreshing your cached recordsets could be something as simple as sending an email to your end users to hit the Clear Cache button in the utilities section of your software.  This is the command button code that would clear the files that are being used in this system

On Error Resume Next

Kill CacheDir & dataSource & FileType

To make this more universal, you would need to store your filenames in tables and your cache directory as optional location on the users PC.

Conclusion

If you are using Microsoft Access and sharing its data amongst a number of end users, you will be passing all the information required by your forms from the server to the client every time you using a combo or list box on a form.  Whilst this will generally be quite trivial when you are displaying a few rows from a lookup table, sometimes you may require more than a 1000 rows of data.   If you are then to multiply this traffic by a large number of PC’s, the amount of data traffic can add up.   By using some the techniques in this article, you can start to take a load of the server and start using those ultra fast local drives that PC’s come with these days.

More importantly though are the different techniques that you now can use to enhance your applications.  Firstly is processing of persisted recordsets locally in any type of application can make your application more efficient.   The second lesson is how easy it is to use ADO to automatically generate XML files for your intranet/internet applications.  And finally are the smart combo and list boxes which can be used to add some special functionality to your application.

 Your download is called    Robinson_cacheADO.accdb 

About Garry Robinson

He is the guy who originally put this Smart Access web site together. Head to www.gr-fx.com.au for more about him and his company. His main claim to fame is winning the Microsoft MVP award for services to MS Access from 2006 to 2018.
This entry was posted in VBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.