Code Snippets

In all the times I have spent participating in forum discussions about all sorts of users' questions, I have learnt a great deal from all the participants.

I have decided to post some of the sample codes that I found users were asking for almost all the time to help me keep them in one place and to make it easier for me to share them again and again.

Hope you find them useful.



Get Back-End File Information

Based on a user's (PFG_Juli) request on UtterAccess, here's a function to retrieve the filename, filepath, or folder path of the BE file.

Option Compare Database
Option Explicit

Public Enum BEInfo
    beFilename = 1
    beFilePath = 2
    beFolderPath = 3
End Enum

Public Function GetBEInfo(Optional GetInfo As BEInfo = 2) As String
'Use to retrieve the BE filename, filepath, or folder path
'Source: http://www.accessmvp.com/thedbguy
'9/25/2013
'
'Note: This function was originally designed to work
'only with Access linked tables

Dim tdf As DAO.TableDef
Dim strConnect() As String
Dim strBEInfo As String
Dim lngIndex As Long

'Assume only one BE location
For Each tdf In CurrentDb.TableDefs
    If tdf.Connect > "" Then
        strConnect = Split(tdf.Connect, ";")
        For lngIndex = 0 To UBound(strConnect)
            'Retrieve BE info
            If InStr(strConnect(lngIndex), "DATABASE=") > 0 Then
                strBEInfo = Replace(strConnect(lngIndex), "DATABASE=", "")
                'Return requested BE info
                Select Case GetInfo
                    Case beFilename
                        strBEInfo = Mid(strBEInfo, InStrRev(strBEInfo, "\") + 1)
                    Case beFilePath
                        'Already got it above
                    Case beFolderPath
                        strBEInfo = Left(strBEInfo, InStrRev(strBEInfo, "\"))
                End Select
                Exit For
            End If
        Next
        Exit For
    End If
Next

GetBEInfo = strBEInfo

End Function