How at Startup to Link Access Database Front End to Back End Tables
A VBA program to link at start-up the FE (front end) of a split Access database to the data tables in the BE (back end).
My original piece on this topic is How to Link at Startup Tables in an Access Split Database. In concluding that article I remark that the VBA described in it — to perform the linking — is a bit heavy. The piece you're reading describes my effort to lighten the load.
Last updated on 2024-04-24.
Preface
Please read the original article for observations on the adoption of split databases; on the need at all for any such start-up routine; and on use of a “KEY” file that any non-programmer could edit to point FE at BE.
Startup Program Structure
When a user opens their FE to beginning working on the database, our startup program makes certain of the links that their FE makes to tables residing in the BE.
The program sequences four main function procedures:
1. Check for presence of BE file
fnBlnFile
2. Make a list of tables to be linked
fnFillLinkedTablesArray
3. Delete any existing links
fnDeleteLinkedTables
4. Re-link FE to tables in BE
fnReLinkTables
fnBlnFile A general-purpose function procedure for checking the existence of a given file. It's called to check that the BE file is where it is expected to be.
fnFillLinkedTablesArray This VBA function produces a list of the tables in the BE.
fnDeleteLinkedTables This VBA function deletes any links that the FE has to external tables. This makes sure that the FE gets a fresh set of links and demolishes any legacy links that may be hanging around and therefore are no longer relevant.
fnReLinkTables This VBA function creates fresh links to those tables in the BE file that have been listed by fnFillLinkedTablesArray.
If, at any point in the program, one of the functions returns an error signal, then sStartUp stops any use of the database, and presents an explanatory message to the user, which can be passed on in anticipation of technical attention to the problem.
The VBA code for each of the functions appears below, followed by the fully-fledged sStartUp program.
The part this function plays in the overall program is to check for the presence of the BE file.
1) Checking Existence of a File
The function fnBlnFile determines whether a file exists at the location supplied to its file$ argument.
Function fnBlnFile(file$) As Boolean
On Error Resume Next
Dim attrib&
attrib& = GetAttr(file$)
fnBlnFile = _
(Err.Number = 0) _
And ((attrib& And vbDirectory) = 0)
End Function
fnBlnFile returns TRUE if it locates the file in the stated folder, FALSE if it doesn't.
2) List the Tables in Back End
fnFillLinkedTablesArray fills an array tbls$. This array has eight rows in this example corresponding to eight tables in the BE file.
tbls$ has three columns. The first holds the names of the tables as they appear in the BE. The second, the names of those tables as you want them to appear in the FE when they are linked in.
The third column holds the path to the BE file, passed to fnFillLinkedTablesArray by way of its BE$ argument.
For this example the third column is overkill. But it's there should you ever need to link to tables in different back ends. Should you, include an argument for each BE.
Function fnFillLinkedTablesArray(ByVal BE$) As Long
On Error GoTo errHandler
Dim resp&
ReDim tbls$(8, 3)
tbls$(1, 1) = "tblAddress"
tbls$(2, 1) = "tblOrganisation"
tbls$(3, 1) = "tblOrganisationAddress"
tbls$(4, 1) = "tblOrganistionPerson"
tbls$(5, 1) = "tblPerson"
tbls$(6, 1) = "tblPersonAddress"
tbls$(7, 1) = "tblPersonEmail"
tbls$(8, 1) = "tluEmailPurpose"
tbls$(1, 2) = "tblAddress"
tbls$(2, 2) = "tblOrganisation"
tbls$(3, 2) = "tblOrganisationAddress"
tbls$(4, 2) = "tblOrganistionPerson"
tbls$(5, 2) = "tblPerson"
tbls$(6, 2) = "tblPersonAddress"
tbls$(7, 2) = "tblPersonEmail"
tbls$(8, 2) = "tluEmailPurpose"
tbls$(1, 3) = BE$
tbls$(2, 3) = BE$
tbls$(3, 3) = BE$
tbls$(4, 3) = BE$
tbls$(5, 3) = BE$
tbls$(6, 3) = BE$
tbls$(7, 3) = BE$
tbls$(8, 3) = BE$
resp& = 0
procDone:
fnFillLinkedTablesArray = resp&
Exit Function
errHandler:
resp& = Err.Number
Resume procDone
End Function
fnFillLinkedTablesArray returns a zero if it completes the array. If it fails, it returns the number of the error that its error handler identifies.
Making a List of Names of Tables to Include in the Array
Since all the tables to which you want the FE to link are in the BE, the best place to make a list of them is in the BE. So introduce this procedure into a module in the BE:
Sub sListTablesInBackEnd() As String
On Error GoTo errHandler
Dim tbl As AccessObject, db As Object
Dim msg$
Set dB = Application.CurrentData
For Each tbl In db.AllTables
If Not Left(tbl.Name, 4) = "MSys" Then Debug.Print tbl.Name
Next tbl
msg$ = "Table listing complete"
procDone:
MsgBox msg$, vbInformation, "Table Listing"
Exit Sub
errHandler:
msg$ = Err.Number & " " & Err.Description
Resume procDone
End Sub
In your BE's VBE Immediate Window, input Call sListTablesInBackEnd and press enter. The procedure will list all the tables in the Window from where you can copy them.
Filling the Array of Tables
You can use Excel to create the entries for the array used by fnFillLinkedTablesArray. In Excel, paste a list of the table names into Column A. Then to make the first dimension in your array, copy and paste this formula into Cell B1 and copy it down:
=CONCAT("tbls$(",ROW(),",1)=""",A1,"""")
To create the second dimension, copy and paste this formula into Cell C1 and copy it down:
=CONCAT("tbls$(",ROW(),",2)=""",A1,"""")
For the third dimension, copy and past this formula into Cell D1 and copy it down:
=CONCAT("tbls$(",ROW(),",3)=","BE$")
Finally, copy and paste the content of Columns B, C and D in turn into fnFillLinkedTablesArray().
3) Delete Current Table Links
Before initiating linking of BE tables into the FE, I opt to delete existing links so that linking can start from a clean sheet. This way any tables that are no longer relevant — perhaps those at sometime introduced during the development of the database — are permanently removed.
An Access database has a number of tables in addition to the ones you introduce. One of these is MSysObjects. The SQL$ query in fnDeleteLinkedTables filters MSysObjects for those table objects that are of Type 6, i.e. linked tables.
Without exception, I use these prefixes in all the databases I create; hence I can rely on them for this procedure to work.
Function fnDeleteLinkedTables() As Long
On Error GoTo errHandler
Dim rs As DAO.Recordset, SQL$
Dim tdf As TableDef
Dim db As DAO.Database
Dim i&, n%
SQL$ = _
"SELECT MSysObjects.Name FROM MSysObjects " & _
"WHERE ((MSysObjects.Type)=6);"
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL$, dbOpenSnapshot)
If rs.RecordCount > 0 Then
rs.MoveLast
n% = rs.RecordCount
' Create array of linked tables
ReDim rsTbls(n%)
rs.MoveFirst
i& = 0
Do While Not rs.EOF
i& = i& + 1
rsTbls(i&) = rs!Name
rs.MoveNext
Loop
n% = i&
rs.Close
Set rs = Nothing
' Delete links to tables listed in array
For i& = 1 To n%
Set tdf = db.TableDefs(rsTbls(i&))
db.TableDefs.Delete rsTbls(i&)
Set tdf = Nothing
Next i&
End If
i& = 0
procDone:
fnDeleteLinkedTables = i&
On Error Resume Next
If Not tdf is Nothing Then Set tdf = Nothing
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
If Not db Is Nothing Then Set db = Nothing
errHandler:
i& = Err.Number
Resume procDone
End Function
fnDeleteLinkedTables returns a zero if it completes satisfactorily. If it fails, then it returns the number of the error that its error handler identifies.
4) Re-link FE to Tables in BE
fnReLinkTables performs the linking of the tables listed in the tbls$ array generated by fnFillLinkedTablesArray:
Function fnReLinkTables() As Long
On Error GoTo errHandler
Dim db As DAO.Database
Dim tdf As TableDef
Dim i&
Set db = CurrentDb
For i& = 1 To UBound(tbls$())
Set tdf = db.CreateTableDef(tbls$(i&, 2))
tdf.Connect = ";DATABASE=" & tbls$(i&, 3) & ";"
tdf.SourceTableName = tbls$(i&, 1)
db.TableDefs.Append tdf
Set tdf = Nothing
Next i&
procDone:
fnReLinkTables = i&
On Error Resume Next
If Not tdf is Nothing Then Set tdf = Nothing
If Not db Is Nothing Then
db.Close
Set db = Nothing
End IF
Exit Function
errHandler:
i& = Err.Number
Resume procDone
End Function
fnReLinkTables() returns a zero if it completes satisfactorily. If it fails, it returns the number of the error that its error handler identifies.
The Controlling Program
In databases I create I place the code described in this article in a module named modStartUp. There are four local module-level constants and variables to declare: pathBE$, BE$, tbls$() and frmName$.
Option Compare Database
Option Explicit
Private Const pathBE$ = "D:\Database\DATA.accdb"
Private tbls$()
Private tblName$
Private Const frmName$ = "frmNavigation"
Sub sStartUp()
On Error GoTo errHandler
Dim resp&
Dim msg$, icon&, title$
Dim tblMissing$
title$ = "ERROR LINKING TABLES"
icon& = vbExclamation + vbOKOnly
msg$ = _
"An error in its start-up program is " & _
preventing the database from opening. " & _
"Please make screen shot " & _
"or note of the following details." & _
vbNewLine & vbNewLine
' (1)
If Not fnBlnFile(pathBE$) Then
msg$ = msg$ & _
"DATA file not located at " & pathBE$
MsgBox msg$, icon&, title$
Application.Quit acQuitSaveNone
End If
' (2)
resp& = fnFillLinkedTablesArray()
If resp& <> 0 Then
title$ = "ERROR LISTING LINKED TABLES"
msg$ = msg$ & _
"Error Source: " & fnFillLinkedTablesArray()" & _
vbNewLine & vbNewLine & _
"Error Number: " & resp& & _
vbNewLine & vbNewLine & _
"Description: " & AccessError(resp&)
MsgBox msg$, icon&, title$
Application.Quit acQuitSaveNone
End If
' (3)
resp& = fnDeleteLinkedTables()
If resp& <> 0 Then
title$ = "ERROR CLEARING TABLE LINKS"
msg$ = msg$ & _
"Error Source: " & fnDeleteLinkedTables()" & _
vbNewLine & vbNewLine & _
"Error Number: " & resp& & _
vbNewLine & vbNewLine & _
"Description: " & AccessError(resp&)
MsgBox msg$, icon&, title$
Application.Quit acQuitSaveNone
End If
' (4)
resp& = fnReLinkTables()
If resp& <> 0 Then
title$ = "ERROR LINKING TO TABLES IN DATA FILE"
msg$ = msg$ & _
"Error Source: fnReLinkTables()" & _
vbNewLine & vbNewLine & _
"Error Number:" & resp& & _
vbNewLine & vbNewLine & _
"Description:" & AccessError(resp&) & _
vbNewLine & vbNewLine & _
"Object Variable:" & "[" & tblName$ & "]"
MsgBox msg$, icon&, title$
Application.Quit acQuitSaveNone
End If
DoCmd.OpenForm frmName$
procDone:
Exit Sub
errHandler:
title$ = "ERROR AT START UP"
icon& = vbCritical
msg$ = msg$ & _
"Error Source: sStartUp()" & _
vbNewLine & _
"Error Number: " & resp& & _
vbNewLine & _
"Description:" & AccessError(Err.Number)
MsgBox msg$, icon&, title$
Resume procDone
End Function
On successful completion sStartUp opens the form, in this example named frmNavigation. Should the program fail it closes Access altogether after presenting the user with a message explaining why.
Running the Controlling Program at Startup
To make the database's FE run the Control Program, sStartUp, whenever a user opens their FE, an AutoExec macro is needed::
- This AutoExec macro needs to run the Control Program via a function that itself calls sStartUp. (An Access macro cannot run a VBA sub procedure directly)
- This bit of code forms that function —
- With the function installed, create the AutoExec macro. From Create on the ribbon's menu, choose Macro
- Complete this macro with its RunCode Action set to =fnCallOntrolProgram —
- Save the name macro naming it AutoExec.
fnCallControlProgram
Call sStartUp()
End Function
Your database's FE will run your AutoExec every time its FE is opened.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
Thanks, in anticipation.