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.