Linked Table Locator

[kenrav]

I developed a ACC2007 split database. Does anyone know of any code I can put
in my FE which, upon command button, would tell the user the location of the
BE? Thanks.

 

[Damon Heron]

Maybe one of the gurus on here has a simpler way, but I have used:

Public Sub FindBE()
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Dim strPathFilenameOfBE As String
Set db = CurrentDb
' Get the path and filename of the "backend" file
For Each tdf In db.TableDefs
If Len(tdf.Connect & "") > 0 Then
strPathFilenameOfBE = Replace(tdf.Connect, ";DATABASE=", "", 1, -1,
vbTextCompare)
Debug.Print strPathFilenameOfBE
Exit For
End If
Next tdf
Set tdf = Nothing
End Sub

Damon

 

[Graham Mandeno]

Hi kenrav

Here is the function I use. It takes either the name of a table or a
TableDef object as an argument and it strips any other irrelevant stuff out
of the Connect string:

Public Function GetBackendFilename(tbl As Variant) As String
Dim db As dao.Database, tdf As dao.TableDef
Dim sConnect As String, p1 As Integer, p2 As Integer
Const cProcName = "GetBackendFilename"
On Error GoTo ProcErr
Select Case VarType(tbl)
Case vbString
Set db = CurrentDb
Set tdf = db.TableDefs(tbl)
Case vbObject
If TypeOf tbl Is dao.TableDef Then
Set tdf = tbl
Else
Err.Raise 5, cProcName, "Object is not a TableDef"
End If
Case Else
Err.Raise 5, cProcName, "Invalid argument type"
End Select
With tdf
If .Attributes And dbAttachedTable Then
sConnect = .Connect
p1 = InStr(sConnect, ";DATABASE=")
If p1 Then
p2 = InStr(p1 + 1, sConnect, ";")
If p2 = 0 Then p2 = Len(sConnect) + 1
GetBackendFilename = Mid(sConnect, p1 + 10, p2 - p1 - 10)
End If
Else
Err.Raise 5, cProcName, tdf.Name & " is a local table"
End If
End With
ProcEnd:
Set tdf = Nothing
Set db = Nothing
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function