Navigation:  Articles > May-1998 >

Working with Attached Tables

Previous pageReturn to chapter overviewNext page

199805_ge2 George Esser          
 
Attached files are often necessary in a multi-user application. Here's a toolbox of routines to programmatically validate attachments; determine whether a problem is a result of networking, permissions, or missing files; and repair the attachment by reattaching or changing the path.
 
The ability to attach tables from other databases and treat them as native Access tables stored in the local database is a very powerful feature. However, to work effectively with these tables, you have to be prepared to handle the problems that can occur with them. I'm going to provide you with a toolbox of routines that you can add to your application to handle most things that can go wrong with your attached tables. While these techniques can be applied to any attached ODBC file, I'll illustrate them using native Access MDB files. For a quick introduction to the Connect property, see the sidebar, "Getting Connect."
 
CheckAttachment()
The fundamental routine in my toolbox for working with attached tables is the CheckAttachment() function. This function loops though each table listed in the TableDefs collection. If the length of the Connect property string is greater than zero, then the current table is an attached file. For each attached table, the function attempts to retrieve the name of the first field. If the attachment isn't valid, an error is raised. Here's the simplest version code in a format that works in any version of Access:
 

Function CheckAttachment() as Integer

Dim db As Database

Dim intCount As Integer

Dim intBad As Integer

 

On Error Goto BadConnect

Set db = CurrentDB()

For intCount = 0 To db.TableDefs.Count - 1

  'strip out the Database portion of the connect string

   strConnect = Mid(db.TableDefs(intCount).Connect, 11)

   intBad = False

   If Len(strConnect) > 0 Then

      strTest = db.TableDefs(intCount).Fields(0).Name

      If intBad Then

         'code to fix table. If unable to fix, set

         ' the function to return a flag saying so

         CheckAttachment = intBad

      End If

      'code to process table (if required)

   End If

Next intCount

db.Close

Exit Function

 

BadConnect:

  intBad = True

  Resume Next

End CheckAttachment

 
 
The code to fix the attachment is available in the full version of the routine in the database in the accompanying Download file. Because an error handler is used to catch the error, the assumption is that the problem means "the file or path cannot be found." The usual cause for this is that the attached database has been moved. To locate the correct path of the data, a Windows function pops up a common dialog box to let the user enter or browse for the correct database (I use a Windows API call to bring up the dialog box). See Figure 1 for a snapshot of the dialog box that the user sees.

199805_ge1 Figure1
 
I stored the returned path and filename in a variable named strName. I also set a flag to indicate that the routine has located a new path.
 
One of the problems with this simple version is that each file goes through this process. If there are 10 attached files, the common dialog box would pop up 10 times. To make this process more efficient, when the routine hits a bad connection, it first tries to fix the problem using the path from the previous problem table, as stored in strName. If the data is from one database only, then the dialog box only has to pop up once -- for the first attached table. There are instances where tables are attached from more than one database file. To handle this, I actually keep two variables with connect information (strName and strName2) and try them both. While an array would let me keep an infinite number of connect strings, it's an unusual application with tables attached from more than two databases.
 
You might be tempted to verify the attachments in a database from the AutoExec function on startup. But in a heavy network situation, the CheckAttachment() function can add up to one minute on the startup, even when all the tables are fine. A quicker way is to verify just one attachment -- if it's valid, then assume the rest are, too. Here's the code to call from the AutoExec function:
 

Function AutoExec()

  Dim strTest As String

  On Error Resume Next

  strTest = _ 

    CurrentDb.TableDefs("tblControlData").Fields(0).Name 

  'If this fails, run the CheckAttachment() Function

  If Err > 0 Then

      If Not CheckAttachment() Then

         MsgBox "Couldn't attach all the tables."

      End If

  End If

  'code to start application

End Function

 
 
If you want to give your users the ability to check their own attachments, you can use this next function, a manual attachments checker. I've put it on a "Utilities" switchboard form that is part of most of my applications:
 

Function Diagnostics()

  If CheckAttachment() Then

     MsgBox "All Files Attachments have been verified"

  Else

     MsgBox "Couldn't attach all the tables."

  End If

End Function

 
 
Errors with style
If you only check the first attachment in the database (or if something is changed while your application is running), then you might not find your problem table until you run some part of the application. You should always check whether your attachments are valid by trapping the appropriate error when opening your forms or reports.
 
Until now, I've assumed that any error that occurred with an attached table was due to a broken attachment. However, it's smarter to make sure that the error that you get when checking an attached file isn't some other problem. It's quite frustrating to try to fix what you think is an attachment problem and then discover it really is a "permissions" issue. "File not found" and "Isn't a valid path" are the errors you want to check for in the CheckAttachment() function. "Already in use" and "No permission" errors can't be handled by CheckAttachment() and require a completely different course of action.
 
When trapping errors on opening reports and forms, I call this HandleFileError function to decide whether to call CheckAttachment() and to offer a more user-friendly "description" and "required action" when an error is raised. The comments on each Case statement reflect the message that you get in Access's user interface when the error occurs:
 

Function HandleFileError(intError as Integer)

 HandleFileError = 0

 Select Case intError

  Case 3024      'Couldn't find file '|'.

   HandleFileError = Checkattachment()

  Case 3044      ''|' isn't a valid path.

   HandleFileError = Checkattachment()

  Case 3045      'Couldn't use '|'; file in use

   MsgBox "The file is currently being used." 

  Case 3051      'Couldn't open file 'x.MDB'.

   MsgBox "You currently do not have permission to" &_

   "open this database."

 End Select

End Function

 
 
If you use the Switchboard Add-In that comes with Access, you should add this error-handling code to the subroutine HandleButtonClick() like this:
 

HandleButtonClick_Err:

    ' If the action was cancelled by the user for

    ' some reason, don't display an error message.

    ' Instead, resume on the next line.

   If Err = 3024 Or Err = 3044 Or _

      Err = 3045 Or Err = 3051 Then

      If HandleFileError(Err.Number) Then

         Resume

      Else

         GoTo HandleButtonClick_Exit

      End If

    ElseIf (Err = conErrDoCmdCancelled) Then

        Resume Next

    Else

        MsgBox "There was an error.", vbCritical

        Resume HandleButtonClick_Exit

    End If

 
 
These error numbers are slightly different for Win 3.1 and Win95/WinNT4.0. Please see the HandleFileError routine in the sample databases for the different numbers. The sample database Attach20.mdb contains the numbers for Win 3.1; Attach95.mdb and Attach97.mdb contain the numbers for Win95/WinNT 4.0. Access 2.0 can be run on both platforms and will work on both platforms with the Win 3.1 error numbers.
 
Form frmAttach
There are some neat benefits to using this code. If you allow your users to invoke the routine from a form, they can switch between different databases at will. I have an application that's used by one of my clients to manage the data for several different refineries. There's a single copy of the application and one database for each of the refineries. By using the frmAttach form that I've included in the sample databases, the client can switch between the different refinery locations without leaving the application. The application database contains a table with all the valid connections, and frmAttach lets the user select between them (see Figure 2). An extra benefit is that this function let us train users on the database application -- we just switched them to a "dummy" data source and let them experiment!

199805_ge2 Figure 2
 
I hope that you find these attachment routines as useful as I have. As I said at the start of this article, one of the most useful features of Access is its ability to attach tables. These routines (and the sample databases) will give you the ability to harness that power.
 
Read about the download ATTACH.ZIP   on this page
 
George Esser owns his own consulting business, ITL Solutions. He is certified in Access 95 and VB 4.0 and currently has contracts with major chemical companies in the Sarnia, Ontario, area. He is also the pastoral team leader of a 500-member church and plays keyboard in a band. geoesser@rcv.org.
 
 
Sidebar: Getting Connect
Every TableDef object has a "Connect" property -- a string that provides information about the source of an open connection, an open database, a database used in a pass-through query, or a linked (attached) table. The "connect" string contains the attachment information in two parts (database type and parameters), which are separated by a semicolon (";"). Here's the syntax for setting the Connect property for any object:
 

object.Connect = [databasetype;[parameters;]]

 
 
Text to the left of the semicolon is information pertaining to the type of database. This can be "Access" (the default), "dbase", "btrieve", or "ODBC", among other settings. Text to the right of the semicolon is the information on the location of the table (path and name). For native database objects (that is, non-ODBC databases), new connection objects, linked tables, and newly created TableDef objects not yet appended to a collection, this property setting is read/write.
 
A convenient way of displaying Connect property information is via the immediate window. The following command in the immediate window will display the Connect property information for an attached Access table named "tblContact":
 

?currentdb.TableDefs("tblContact").Connect

 
 
For an Access table, this would display something like ";DATABASE=H:\PINNACLE\DATA20A.MDB". Compare this to the result for a dBase III attached table, which would look like "dBase III;HDR=NO;IMEX=2;DATABASE=C:\WINDOWS\MSAPPS\MSQUERY". As you can see, the text to the left of the first semicolon indicates the type of attached table (in this case, "dBase III"). Text to the right of the semicolon contains only the path information. The name of the linked table isn't necessary, since dBase III tables use separate files.
 
If a change is made to this "connect" string, the link must be refreshed to update the information before it will take effect. The code to refresh the link for a table called tblContract would be:
 

currentdb.TableDefs("tblContact").RefreshLink