Using Linked Tables in a Desktop Database

Your first foray into the world of shared applications will most likely involve copying your completed database to a file server and instructing users to open the database from the server. That’s basically not a good idea because Access doesn’t run on the server-it runs on each user’s desktop. If you have several users sharing a database file, the copy of Access running on each user computer has to load all your “code” definitions-the queries, forms, reports, macros, and modules-over the network. If one user applies a sort or filter to a form and then closes it, Access will try to save the changed definition in the copy on the server, and it might run into locking or corruption problems if another user has the form open at the same time. Also, if your application needs to keep information about how each user works with the application, it would be more complicated to store this information in the database because all users share the same file.

The solution is to create a data-only .accdb file on a server and use linked tables in a desktop application that you install on multiple desktop computers and link to the data server. When you separate the data tables into a shared file on a server, you’re actually building a simple client/server application. The main advantage to splitting your database over simply sharing a single desktop database is that your application needs to retrieve only the data from the tables over the network. Because each user will have a local copy of the queries, forms, reports, macros, and modules, Access running on each user workstation will be able to load these parts of your application quickly from the local hard drive. Using a local copy of the desktop application on each client computer also makes it easy to create local tables that save settings for each user. For example, the Conrad Systems Contacts application allows each user to set a preference to open a search dialog box for companies, contacts, and invoices or to display all records directly.

Taking Advantage of the Database Splitter Wizard

You could split out the tables in your application into a separate file and link them into the database that contains all your queries, forms, reports, and modules “by hand” by first creating a new empty database (see Chapter 4, “Creating Your Database and Tables”) and then importing all your tables into that database using the techniques described in Chapter 6. You could then return to your original database and delete all the tables. Finally, you could move the data database (the one containing the tables) to a file server and then link these tables into your original code database (the one containing all your queries, forms, reports, and modules), again using the techniques in Chapter 6.

Inside Out-Is Your Desktop Application Designed for Client/Server? 

When you build a desktop database application, it’s all too easy to design forms and reports that always display all records from your tables when the user opens them. It’s also tempting to create combo boxes or list boxes that display all available values from a lookup table. These issues have little to no impact when you’re the only user of the application or you share your application with only a few other users. However, fetching all rows by default can have serious performance implications when you have multiple users who need to share a large amount of data over a network.

A successful client/server application fetches only the records required for the task at hand. You can design an application so that it never (or almost never) opens a form to edit data or a report to display data without first asking the user to specify the records needed for the task at hand. For example, the Conrad Systems Contacts application opens a list of available companies, contacts, or invoices from which the user can choose only the desired records. This application also offers a custom query by form search to filter specific records based on the criteria the user enters.

You can also design the application so that it uses information about the current user to filter records. For example, the Housing Reservations database always filters employee and reservations data to display information only for the currently signed on employee. When a department manager is signed on, the application shows data only for the current manager’s department

Even so, the two main sample applications aren’t perfect examples. Both applications use a ZIP Code table that contains more than 50,000 records to help users enter valid address data, and this huge table is the row source for several combo boxes. In the desktop database version, each user has a local copy of the ZIP Code table, so the performance impact is minimal. If you decide to split your database application into a client/server architecture, you need to think about keeping any tables similar to this in the local database on each user’s computer. You could certainly do this with a ZIP Code table because the data is relatively static.

The bottom line is you should take a look at the way your desktop application fetches data for the user. If it always fetches all records all the time, it’s probably not a good candidate for upsizing to a client/server application.

Fortunately, there’s an easier way to do this in one step using the Database Splitter wizard. Open your original database, and on the Database Tools tab, in the Move Data group, click Access Database. (You can try this with one of your own databases or a backup copy of the Housing.accdb sample database.) The wizard displays the page shown in Figure 25–1.

Figure 25–1: The Database Splitter wizard helps you move the tables into a separate database.

When you click the Split Database button, the wizard opens a second page where you can define the name and location of the back-end, or data-only, database. Be sure to choose a location for this database on a network share that is available to all potential users of your application. Click the Split button on that page, and the wizard exports all your tables to the new data-only database, deletes the tables in your original database, and creates links to the moved tables in your original database. You can now give each user a copy of the code database-containing your queries, forms, reports, modules, and linked table objects pointing to the new data-only database-to enable them to run the application using a shared set of tables.

One disadvantage to using the Database Splitter wizard is that it splits out all tables that it finds in your original desktop database. If you take a look at the desktop database version of the Conrad Systems Contacts application (Contacts.accdb), you can see that the application also uses some local tables (tables that remain in the code database). For example, the ErrorLog table contains records about errors encountered when the user runs the application. If the error was caused by a failure in the link to the server, the code that writes the error record wouldn’t be able to write to this table if the table was in the server database. The database also contains local copies of lookup tables that aren’t likely to change frequently, such as the tlkpStates table that contains U.S. state codes and names and the ztblYears table that provides a list of years for the frmCalendar form. Access can fetch data from local tables faster than it can from ones linked to a database on a server, so providing local copies of these tables improves performance.

Although splitting a database application makes it easier for multiple users to share your application, this technique works well only for applications containing a moderate amount of data (less than 200 MB is a good guideline) with no more than 20 simultaneous users. Remember that Access is fundamentally a desktop database system. All the work-including solving complex queries-occurs on the client computer, even when you have placed all the data on a network share. Each copy of Access on each client computer uses the file sharing and locking mechanisms of the server operating system.

Access sends many low-level file read, write, and lock commands (perhaps thousands to solve a single query) from each client computer to the file server rather than sending a single SQL request that the server solves. When too many users share the same application accessing large volumes of data, many simple tasks can start, taking minutes instead of seconds to complete.

Creating Startup Code to Verify and Correct Linked Table Connections

If you were careful when you created your linked tables, you used a Universal Naming Convention (UNC) path name instead of a physical or logical drive letter. Unless the network share name is different on various client computers, this should work well to establish the links to the data file when the user opens your application. However, even “the best-laid schemes mice an’ men gang aft aglee.”^[1] (Or, if you prefer, Murphy’s law is always in force.)

In Chapter 6, you learned how to use the Linked Table Manager to repair any broken connections. However, you can’t expect your users to run this wizard if the linked table connections are broken. You should include code that runs when your startup form opens that verifies and corrects the links if necessary. Also, you might over time make changes to the structure of the data tables and issue an updated version of the client desktop database that works with the newer version of the tables. Your startup code can open and check a version table in the shared data database and warn the user if the versions don’t match.

You can find sample code that accomplishes all these tasks in the desktop database version of the Conrad Systems Contacts database (Contacts.accdb). Open the database, and then open the modStartup module. Select the ReConnect function, where you’ll find the following code:

 Public Function ReConnect() Dim db As DAO.Database, tdf As DAO.TableDef Dim rst As DAO.Recordset, rstV As DAO.Recordset Dim strFile As String, varRet As Variant, frm As Form Dim strPath As String, intI As Integer ' This is a slightly different version of reconnect code ' Called by frmSplash - the normal startup form for this application     On Error Resume Next     ' Point to the current database     Set db = CurrentDb     ' Turn on the hourglass - this may take a few secs.     DoCmd.Hourglass True     ' First, check linked table version     Set rstV = db.OpenRecordset("ztblVersion")     ' Got a failure - so try to reattach the tables     If Err <> 0 Then GoTo Reattach     ' Make sure we're on the first row     rstV.MoveFirst     ' Call the version checker     If Not CheckVersion(rstV!Version) Then         ' Tell caller that "reconnect" failed         ReConnect = False         ' Close the version recordset         rstV.Close         ' Clear the objects         Set rstV = Nothing         Set db = Nothing         ' Done         DoCmd.Hourglass False         Exit Function     End If     ' Versions match - now verify all the other tables     ' NOTE: We're leaving rstV open at this point for better efficiency     '   in a shared database environment.     '  JET will share the already established thread.     ' Turn on the progress meter on the status bar     varRet = SysCmd(acSysCmdInitMeter, "Verifying data tables...", _         db.TableDefs.Count)     ' Loop through all TableDefs     For Each tdf In db.TableDefs         ' Looking for attached tables         If (tdf.Attributes And dbAttachedTable) Then             ' Try to open the table             Set rst = tdf.OpenRecordset()             ' If got an error - then try to relink             If Err <> 0 Then GoTo Reattach             ' This one is OK - close it             rst.Close             ' And clear the object             Set rst = Nothing         End If         ' Update the progress counter         intI = intI + 1         varRet = SysCmd(acSysCmdUpdateMeter, intI)     Next tdf     ' Got through them all - clear the progress meter     varRet = SysCmd(acSysCmdClearStatus)     ' Turn off the hourglass     DoCmd.Hourglass False     ' Set a good return     ReConnect = True     ' Edit the Version table     rstV.Edit     ' Update the open count - we check this on exit to recommend a backup     rstV!OpenCount = rstV!OpenCount + 1     ' Update the row     rstV.Update     ' Close and clear the objects     rstV.Close     Set rstV = Nothing     Set db = Nothing     ' DONE!     Exit Function Reattach:     ' Clear the current error     Err.Clear     ' Set a new error trap     On Error GoTo BadReconnect     ' Turn off the hourglass for now     DoCmd.Hourglass False     ' ... and clear the status bar     varRet = SysCmd(acSysCmdClearStatus)     ' Tell the user about the problem - about to show an open file dialog     MsgBox "There's a temporary problem connecting to the CSD data." & _        " Please locate the CSD data file in the following dialog.", _        vbInformation, "CSD Contacts Manager"     ' Establish a new ComDlg object     With New ComDlg         ' Set the title of the dialog         .DialogTitle = "Locate CSD Contacts Data File"         ' Set the default file name         .FileName = "ContactsData.accdb"         ' ... and start directory         .Directory = CurrentProject.Path         ' ... and file extension         .Extension = "accdb"         ' ... but show all accdb files just in case         .Filter = "CSD File (*.accdb)|*.accdb"         ' Default directory is where this file is located         .Directory = CurrentProject.Path         ' Tell the common dialog that the file and path must exist         .ExistFlags = FileMustExist + PathMustExist         If .ShowOpen Then             strFile = .FileName         Else             Err.Raise 3999         End If     End With     ' Open the "info" form telling what we're doing     DoCmd.OpenForm "frmReconnect"     ' ... and be sure it has the focus     Forms!frmReconnect.SetFocus     ' Attempt to re-attach the Version table first and check it     Set tdf = db.TableDefs("ztblVersion")     tdf.Connect = ";DATABASE=" & strFile     tdf.RefreshLink     ' OK, now check linked table version     Set rst = db.OpenRecordset("ztblVersion")     rst.MoveFirst     ' Call the version checker     If Not CheckVersion(rst!Version) Then         ' Tell the caller that we failed         ReConnect = False         ' Close the version recordset         rst.Close         ' ... and clear the object         Set rst = Nothing         ' Bail         Exit Function     End If     ' Passed version check - edit the version record     rst.Edit     ' Update the open count - we check this on exit to recommend a backup     rst!OpenCount = rst!OpenCount + 1     ' Write it back     rst.Update     ' Close the recordset     rst.Close     ' ... and clear the object     Set rst = Nothing     ' Now, reattach the other tables     ' Strip out just the path name     strPath = Left(strFile, InStrRev(strFile, "\") - 1)     ' Call the generic re-attach function     If AttachAgain(strPath) = 0 Then         ' Oops - failed. Raise an error         Err.Raise 3999     End If     ' Close the information form     DoCmd.Close acForm, "frmReconnect"     ' Clear the db object     Set db = Nothing     ' Return a positive result     ReConnect = True     ' ... and exit Connect_Exit:     Exit Function BadReconnect:     ' Oops     MsgBox "Reconnect to data failed.", vbCritical, _     "CSD Contacts Manager"     ' Indicate failure     ReConnect = False     ' Close the info form if it is open     If IsFormLoaded("frmReconnect") Then DoCmd.Close acForm, "frmReconnect"     ' Clear the progress meter     varRet = SysCmd(acSysCmdClearStatus)     ' ... and bail     Resume Connect_Exit End Function

The code begins by attempting to open the linked ztblVersion table. If the open generates an error, the code immediately jumps to the Reattach label about halfway down the listing. If the version-checking table opens successfully, the code next calls the CheckVersion function (not shown here) that compares the version value in the table with a public constant saved in the modGlobals module. If the versions don’t match, that function displays an appropriate error message and returns a False value to this procedure. If the version check fails, this procedure returns a False value to the original calling procedure (in the frmSplash form’s module) and exits.

If the versions do match, the code next loops through all the table definitions in the database and attempts to open a recordset on each one to verify the link. Note that the code leaves the recordset on the version-checking table open. If it didn’t do this, each subsequent open and close would need to establish a new network connection to the file server, and the checking of all tables would take minutes instead of seconds. Note also that the code uses the SysCmd system function to display a progress meter on the Access status bar.

If all linked tables open successfully, the procedure returns True to the calling procedure and exits. If opening any of the tables fails, the code immediately jumps to the Reattach label to attempt to fix all the links.

The code beginning at the Reattach label clears all errors, sets an error trap, and then displays a message informing the user that there’s a problem. After the user clicks OK in the dialog box, the code creates a new instance of the ComDlg class module, sets its properties to establish an initial directory and ask for the correct file type, and uses the ShowOpen method of the class to display a Windows Open File dialog box. The class module returns a True value if the user successfully locates the file, and the code retrieves the class module’s FileName property to find out the path and name of the file chosen by the user. If the ShowOpen failed, the code raises an error to be logged by the error-handling code at the end of the procedure.

Next, the code opens a form that is a dialog box informing the user that a reconnect is in progress. The code attempts to fix the link to the version-checking table using the path and file the user selected. Notice that the code sets the Connect property of the TableDef object and then uses the RefreshLink method to reestablish the connection. If the table isn’t in the file the user selected, the RefreshLink method returns an error, and the code after the BadReconnect label near the end of the procedure executes because of the error trap.

After checking that the version of the code matches the version of the database, the code calls the AttachAgain function (not shown here) and passes it the path and file name. You can also find this function in the modStartup module. The function loops through all the TableDef objects, resets the Connect property for linked tables, and uses RefreshLink to fix the connection. Because this sample database also has some linked Microsoft Excel worksheets, you’ll find that code in the AttachAgain function checks the type of linked table and sets up the Connect property appropriately.

If you’d like to see how this code works, you can open the Contacts.accdb file and then use Windows Explorer to temporarily move the ContactsData.accdb, Fictitious Companies.xlsx, and Fictitious Names.xlsx files to another folder. Open the frmSplash form, and you should see the code prompt you to identify where you moved the ContactsData.accdb file. The code in the AttachAgain procedure assumes that the two Excel files are in the same folder as the ContactsData.accdb file.

You can study the other functions called by the Reconnect function in the modStartup module on your own. We provided comments for every line of code to help you understand how the code works.

^[1] Burns, Robert. “To a Mouse, On Turning Her Up in Her Nest With the Plough.” 1785.