Path of Backend database in a FE/BE scenario

[Pat]

How do i get the path of the backend database when i am in the frontend?

[mbarron]

If you know the name of a table in the back end:

Code: Select all

Dim sTblDef As String
sTblDef = CurrentDb.TableDefs("test").Properties(4)
MsgBox Mid(sTblDef, InStr(sTblDef, "=") + 1)

[JohnH]

That is quicker than the method I have usually used, but it returns the wrong result if there is a password on the backend.
In that case there are 2 = signs in the string.

I think this works in either case:

MsgBox Mid(sTblDef, InStrRev(sTblDef, "=") + 1)

[Mark L]

How do i get the path of the backend database when i am in the frontend?

Or you can try the following (again, you must know the name of a linked table)

strBE = currentdb.tabledefs("sometable").connect

[Pat]

Thanks Mark, yet another way to find it.

[JohnH]

I used to use this function , which does not require a 'seed' table name, but it is quite slow to run, and I used it quite a bit. So I would store the result in a front end table, and normally retrieve it from there.
The loop returns all the queries, and the system tables before getting to the real tables, so you need a way of recognising a real table. In my case I look for the name starting with "tbl".

This requires a Reference to Microsoft ADO EXt for DDL and Security.

Code: Select all

Public fun ction fnGetBackEndDataFile
  Dim cat As ADOX.Catalog
    Dim tdftable As ADOX.Table
    Dim strdbName As String
    Dim sql As String
    On Error GoTo fnGetBackendDataFile_Error

    Set cat = New ADOX.Catalog
    Dim intI As Integer
    Dim blfound As Boolean
    intI = 1
    blfound = False
    cat.ActiveConnection = CurrentProject.Connection
    On Error Resume Next
    Do Until (blfound = True) Or (intI = 100)
        Set tdftable = cat.Tables(intI)
       ' Debug.Print intI, tdftable.Name
        If tdftable.Type = "LINK" And Left(tdftable.Name, 3) = "tbl" Then
            blfound = True
        End If
        intI = intI + 1
    Loop
    '     MsgBox (tdftable.Name)
    If intI < 100 Then
     strdbName = tdftable.Properties("Jet OLEDB:Link Datasource")
    Else
    ' tables are not linked
     strdbName = CurrentProject.FullName
     
    End If
    '  MsgBox (strDBName)
    If Len(strdbName) > 0 Then
        fnGetBackendDataFile = strdbName
        ' keep this so we can get it again quickly next time
        sql = "Update tbldatapath set tbldatapath.datapath = " & Chr(34) & strdbName & Chr(34) & " where  (tbldatapath.[key]=1)"
       
        CurrentDb.Execute sql, dbFailOnError
        
    Else
        fnGetBackendDataFile = "not found"
    End If


    Set tdftable = Nothing
    Set cat = Nothing

Exit_fnGetBackendDataFile:
    Exit Function


fnGetBackendDataFile_Error:
    If Err.Number = 0 Then
        Resume Next
    Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fnGetBackendDataFile of Module Global Code", , "Error in procedure fnGetBackendDataFile"
      
        Resume Exit_fnGetBackendDataFile
    End If

End Function

[Pat]

Wow, all these possibilities, thank you guys.

[Tom Wickerath]

Hi Pat,

You can also get the linked paths using a SQL Statement (query)--no VBA code required. While the SQL involves the msysObjects table, and this is undocumented by Microsoft, the chances of this breaking in a future release are very remote. Here are two variations:

To reveal all linked paths:

SELECT Left(Database,255) AS [Linked Databases],
Count(MsysObjects.Database) AS [Number of Tables]
FROM MsysObjects
WHERE (MsysObjects.Type)<>9
GROUP BY Left(Database,255)
HAVING Left(Database,255) Is Not Null;

If you want to see each table name, use this instead:

SELECT Left(Database,255) AS [Linked Databases], Name, ForeignName
FROM MsysObjects
WHERE (((MsysObjects.Type)<>9))
GROUP BY Left(Database,255), Name, ForeignName
HAVING (((Left([Database],255)) Is Not Null))
ORDER BY Name, ForeignName;

Notes:
1. Make sure to compact the database first, to avoid revealing table links that have already been deleted.
2. Grouping on the first 255 characters is required for Access 97, but not later versions, since [Database] is a memo data type. (Contributed by Doug Steele).
3. The criteria, WHERE (MsysObjects.Type)<>9, is used to filter out any table constraints created with ADO. (Contributed by Dirk Goldgar).

Tom Wickerath
Microsoft Access MVP
2006 - 2011

[Pat]

Thanks Tom, all good info, i will keep that in my bag of tricks.
I have used that table in the past.
It helps when you know what those codings (eg Type<>9) mean.

[HansV]

Hi Tom,

Welcome to Eileen's Lounge! Nice to see you here. I hope you'll enjoy it.

Thanks for your first contribution.