Print Page | Close Window

Get Path\Filename of Back End

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Long, Crystal
Forum Description: Access Basics is designed for those of you with a thirst to understand the fundamentals of Access with programming in mind ... whether you realize it or not.
Printed Date: 20 Mar 2018 at 4:28pm

Topic: Get Path\Filename of Back End
Posted By: Crystal Long
Subject: Get Path\Filename of Back End
Date Posted: 03 Nov 2013 at 11:00am
Given a table name, this function returns the path\filename of the Back End database if it is Access:

Function GetAccessBE_PathFilename(pTableName As String) As String

   '  the file path and file name of the BE database
   '  "" if the table is not linked
   On Error GoTo Proc_Err
   Dim db As dao.Database _
      , tdf As dao.TableDef
   GetAccessBE_PathFilename = ""
   Set db = CurrentDb
   Set tdf = db.TableDefs(pTableName)
   If Len(tdf.Connect) = 0 Then
      GoTo Proc_Exit
   End If
   ' look at Connect string - Database Type is the first thing specified
   ' if the BE is Access
   If InStr(tdf.Connect, ";DATABASE=") <> 1 Then
      GoTo Proc_Exit
   End If
   GetAccessBE_PathFilename = Mid(tdf.Connect, 11)
   On Error Resume Next
   Set tdf = Nothing
   Set db = Nothing
   Exit Function
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetAccessBE_PathFilename"

   Resume Proc_Exit
End Function

to test, you can type this into the Immediate window:
? GetAccessBE_PathFilename("MyLinkedTableName")

MyLinkedTableName is the name of a table that is linked to an Access back end

Warm Regards,

My Analyzer
... the ultimate Data Dictionary Tool (Free download. As of November 2013, last update was October 2013)" rel="nofollow -

   (: have an awesome day :)

Warm Regards,
Microsoft MVP
Remote Training & Programming
Let's Connect and Build Together
~have an awesome day ~

Print Page | Close Window