ACC: How to Retrieve 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 shows you how to create a sample user-defined Visual Basic for Applications function 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 get 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 shows you 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.

Additional query words: how to

Keywords : kbprg PgmObj
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto