ACC: Retrieving the Path of Linked MS Access 95/97 Tables

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes a sample user-defined Visual Basic for Applications function that you can use to retrieve the path and file name of the originating database for a linked Microsoft Access table. The function uses the linked table's Connect property to obtain this information.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

The following example demonstrates how to create and use the sample GetLinkedDBName() function:

1. Open the sample database Northwind.mdb.

2. Link the Examples table from the Developer Solutions sample application (Solutions.mdb, which is usually in the Samples folder.)

3. Create a new module and type the following procedure:

         '===============================================================
         ' The GetLinkedDBName() function requires the name of a
         ' linked Microsoft Access table, in quotation marks, as an
         ' argument. The function returns the full path of the originating
         ' database if successful, or returns 0 if unsuccessful.
         '===============================================================
         Function GetLinkedDBName (TableName As String)
            Dim db As Database, Ret
            On Error GoTo DBNameErr
            Set db = CurrentDb()
            Ret = db.TableDefs(TableName).Connect
            GetLinkedDBName = Right(Ret, Len(Ret) - (InStr _
               (1, Ret, "DATABASE=") + 8))
            Exit Function
         DBNameErr:
            GetLinkedDBName = 0
         End Function
   
   

4. To test this function, type the following line in the Debug window, and then press ENTER.

         ? GetLinkedDBName("Examples")
   

   Note that the path of the linked table's originating database is displayed in the Debug window.

REFERENCES

For more information about the Connect property, search for "Connect property" using the Microsoft Access 97 Help Index.