Jan 19, 2007

These files demonstrate a different way of using Microsoft Jet tables under Access. Normally the developer creates links in the FrontEnd (FE) to tables contained in the BackEnd (BE). The alternative presented here is to deliver the data contained in those tables to the FE in the form of a recordset. Linked tables are not required.

The concept is to create a function in the BE that queries its tables (local or linked) and creates a recordset of the data contained in the tables. The recordset is returned to the calling program via a public function or procedure.

This method is posted as a point of interest -- I am not claiming that it's The Next Good Thing or even that it's practical to use -- only that it's possible to do. 

To run this demo, copy the files from the zip file into any folder of your choice. All the MDB files are Access 2000 format.

Open SQLReaderBE.mdb and update its link for atblHighwayJurisdiction. This is a conventional Access link to the table contained in HiddenBackend.mdb. While you are in SQLReaderBE.mdb, take a moment to verify there is a single, public function called SQLSelectBE. Notice also there are three local tables and two queries.

Open SQLReaderFE.mdb. Relink the tables to the tables in SQLReaderBE.mdb. NOTICE that only two of the tables are linked -- aTblCities and atblHighwayJurisdiction were deliberately omitted from having links. Try opening the query named qryCityFullInfo -- it will fail because there is no link to one of the tables included in the query. The only purpose for this query is to demonstrate that the atblCities table is NOT available to the SQLReaderFE.mdb

Re-create the Reference to SQLReaderBE.mdb using the VBA Editor Tools > References menu item. You should probably delete the existing reference to SQLReaderBE.mdb, then close the Reference window, then re-open it and establish the Reference to SQLReaderBE.mdb. This seems to be the most reliable method to repair a broken reference.

Now demonstrate the SQL function. Open the Module named FEModules. Notice there is a function named SQLSelectFE that is almost identical to the version contained in SQLReaderBE.mdb. Single-step through the procedure named testIt -- it simply prints some information in the Debug window to confirm that the process is working. TestIt opens four recordsets that:
- use conventional, linked tables and a conventional OpenRecordset method;
- use the SQL function contained in the FE and that uses a linked table in the FE;
- uses the SQL function contained in the BE and that uses tables in the BE, including a table that is invisible to the FE;
- uses the SQL function in the BE and uses tables that are both IN the BE and are LINKED to the BE.


There is a subtle difference between SQLSelectFE and SQLSelectBE. The frontend version uses the CurrentDB object, while the backend version uses the CodeDB object. This latter object ensures that Access runs the code "in the right place" and against the right tables.

There you go... a different way to look at things. I first experimented with this stuff around 2002 using Access 97. One of the interesting quirks I noticed at that time is that a recordset created using the backend function was returned to the calling program about 10 times faster than an identical query using linked tables in the frontend. I haven't taken the time to reproduce those results at this time, but feel free to experiment. I was surprised by the results, and you may be, too.

Back in 2002, I tested this procedure with local files and with files drawn across a wired network between WinXP and Win98. Today I tested it on local files on WinXP plus over a wireless network to a Linux NAS server. Every combination worked as expected.



Jack MacDonald


