Sometimes we, as developers, need to determine certain fundamentals about the database that is running.  One of which is the type of database that the users is currently running.  Is it an mde/accde?  What is the file extension?  I thought I’d briefly touch upon this question today.

The Current (FE) Database Extension

Nothing could be easier than determining the file extension of the current database (FE).  We know that CurrentDb.Name returns the full path and filename with the file extension, so we only need extract it from there by doing something along the lines of:

Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, ".")+1)

The Back-End Database Extension

In the case of database back-ends, things get slightly more complicated as we first have to extract and parse the linked table connection string to get the back-end path, filename and extension and then extract the extension from that string.  To do so, you’ll need a function similar to:

'---------------------------------------------------------------------------------------
' Procedure : GetBEExtension
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the file extension of the back-end
' 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: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTableName    : Name of a linked table to determine the back-end file extension from
'
' Usage:
' ~~~~~~
' GetBEExtension("Contacts")    -> returns "" if it is a local table
'                               -> returns a valid file extension (mdb, accdb, ...) for
'                                   linked tables
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2017-07-27              Initial Release
'---------------------------------------------------------------------------------------
Public Function GetBEExtension(sTableName As String) As String
    Dim tdf                   As DAO.TableDef
    Dim sBackEnd              As String

    On Error GoTo Error_Handler

    Set tdf = DBEngine(0)(0).TableDefs(sTableName)
    If InStr(tdf.Connect, ";DATABASE=") > 0 Then
        sBackEnd = Mid(tdf.Connect, InStr(tdf.Connect, ";DATABASE=") + 10)
        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:
    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

File Extensions Are fine, But What About Runtime?

Sometimes we need to know if our application is running in an runtime environment or not.  This once again is extremely easy to determine using SysCmd() method.  Therefore, we can simply test for the runtime environment with a single line of code

SysCmd(acSysCmdRuntime)

Therefore, we can simply test for the runtime environment with a single line of code.  So we’d end up with something like:

If SysCmd(acSysCmdRuntime) = True Then
    'Runtime Environment
Else
    'Full Version
End If

So, as you can see, it is relatively easy to determine so basic information about the database files and usage.

You could therefore use this type of check to validate that a user hasn’t tried to change file extensions in an attempt to circumvent your security.

You could check what environment it is running in to disable certain incompatible features, or shutdown the app completely is you only want it running in a runtime environment.

This are very simple checks, but they can be a great addition to pretty much any database.