This document describes Steve Goodhall’s Connection Routines for Access 2000. This is a group of subroutines for managing links between the Code Database (Front End) of an Access Application and one or more Data Databases (Back End). These routines are distributed without warranty. They may be freely used so long as the copyright notice is preserved. They may be redistributed without charge so long as the copyright notices is preserved and this document is included in the distribution. The user is free to modify these routines at his own risk.
Steve Goodhall can be contacted by email at steve@goodhall.info. The routines can be downloaded from www.goodhall.info/steve. No support is offered for these routines, but reported defects will probably be corrected if well documented.
The purpose of these routines it to ensure that the links between the Code (Front End) Database and the Data (Back End) Databases are always up to date. This is necessary when maintaining both a development and a production version of the Application. When the development version of the Application is moved into a production environment, the links in the Code Database continue to point to the development Data Databases. Likewise, if the production version is copied back to the development environment, links will continue to point to the production Data Databases. This proves to be a source of errors and data corruption.
The approach taken is to disconnect and reconnect all linked tables at the beginning of application execution. A macro is provided to perform this function. If this macro is invoked from the Autoexec macro of the Code Database, links should always be correct. By default, the Code Database is linked to all tables in all Data Databases that reside in the same directory. For applications that use a shared Data Database, an initialization file is provided that will point to the location of the Data Databases. If a table name in a Data Database duplicates a table name in the Code Database, it will not be linked. If the same table name appears in more than one Data Database, the first version encountered will be linked. A message will be displayed in either circumstance.
These routines are specifically designed for Access 2000 and uses ADO to create connections. These routines have not been tested for Access XP, but it is likely that they will function correctly in that environment. A previous version of these routines is suitable for use with Access 97 and uses DAO rather than ADO. This version is also available at www.goodhall.info/steve.
The distributed package consists of five Access Objects, as follows:
|
SJGConnect Component |
Function Name |
Usage |
|
Module SJGmodConnect2k |
SJGRemoveLinkTables |
Removes all linked table references from the Code Database. If the form, fSJGDisconnect is open when this function is invoked, progress will be displayed. |
|
|
SJGRestoreLinkTables |
Process the INI file and invoke SJGLinkDBsInPath. If the form, fSJGResconnect is open when this function is invoked, progress will be displayed. |
|
|
SJGLinkDBsInPath |
Link to all non-system tables in all Data Databases in the spedified directory. |
|
|
SJGLinkAllDBTables |
Link to all non-system tables in a specified Data Databases |
|
|
SJGLinkJetTable |
Link to a single non-system table in a specified Data Database |
|
Module SJGModTestProcedures |
SJGtestLinkDBsInPath |
Test Harness for SJGLinkDBsInPath |
|
|
SJGTestLinkAllDBTables |
Test Harness for SJGLinkAllDBTables |
|
|
SJGtestLinkJet |
Test Harness for SJGLinkJetTable |
|
Form fSJGDisconnect |
|
Display table information during disconnect operation. Note that usage is optional. |
|
Form fSJGReconnect |
|
Display table information during reconnect operation. Note that usage is optional. |
|
Macro SJGReconnect |
|
Open and close forms and invoke SJGDisconnect and SJGReconnect functions |
To implement the routines in an application, copy these components into the Code Database and add references to the components described below.
The distributed code file uses the following references to dynamic libraries
|
Standard Component Name |
DLL File |
Usage |
|
Visual Basic for Applications |
|
Core library |
|
Microsoft Access 9.0 Object Library |
msacc9.olb |
Core library |
|
OLE Automation |
STDOLE2.TLB |
Core library |
|
Microsoft ActiveX Data Objects 2.1 Library |
msado21.tlb |
Core library |
|
Microsoft ADO Ext. 2.8 for DDL and Security |
msadox.dll |
Used to create and destroy linked table definitions |
|
Microsoft Scripting Runtime |
scrrun.dll |
File system input and directory management |
After importing the modules, open the Visual Basic Editor and use the Menu, Tools, References option, ensure that all of these references are checked. Failure to do so may result in compilation errors. If you have different versions of these components, you may find it necessary to update the references in order to run the test harnesses in the distributed MDB file.