I was revisiting some old code that I use to perform a compact and repair to keep things optimized for some of my clients and thought I’d share a useful function which I use to determine whether the file extension of a linked-table back-end file so I can name the compacted file appropriately.

I sometime obfuscate Access database files by switching the file extension and this enable me to create more versatile code that can handle such situations. Obviously, this is also useful in situation in which you may have both mdb and accdb files, so you can distinguish them from one another.

Not really rocket science, just extract the table definition connection and parse out the extension, but here’s a function so you don’t need to reinvent the wheel.

'---------------------------------------------------------------------------------------
' Procedure : GetBEExtension
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the file extension (mdb or accdb) of a linked table back-end
'               Linked table returns the file extension (ie: mdb, accdb)
'               Local table returns ""
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTableName: The name of a linked table whose backend you are trying to determine the
'               file extension/type of.
'
' Usage:
' ~~~~~~
' GetBEExtension("tbl_Contacts") -> Returns: mdb
' GetBEExtension("tbl_Cities") -> Returns: accdb
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-02-27              Initial Release
' 2         2019-04-10              Public Release
'---------------------------------------------------------------------------------------
Public Function GetBEExtension(sTableName As String) As String
    Dim tdf                   As DAO.TableDef
    Dim sBackEnd              As String

    On Error GoTo Error_Handler

    DBEngine(0)(0).TableDefs.Refresh 'Ensure we have an up-to-date list
    Set tdf = DBEngine(0)(0).TableDefs(sTableName)
    If InStr(tdf.Connect, ";DATABASE=") > 0 Then
        sBackEnd = Mid(tdf.Connect, 11)
        GetBEExtension = Mid(sBackEnd, InStrRev(sBackEnd, ".") + 1)
    End If

Error_Handler_Exit:
    On Error Resume Next
    If Not tdf Is Nothing Then Set tdf = Nothing
    Exit Function

Error_Handler:
    '3265 -> table not found
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetBEExtension" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function