How to Link Tables in an Access Split Database at Startup
How to use VBA to link the front end (FE) of a split Access database to tables in the back end (BE) when a user opens the FE.
If you visited this page before 2020-03-26, then please read the additions to the Caution item below.
I’ve used this code on this page in many databases, but when I come to apply it to a new one, I see it to be a bit heavy on code. So, on How at Start-up to Link Access Database Front End to Back End Tables you’ll find my attempt to lighten the load.
Last updated on 2022-10-23 by David Wallis.
Splitting Access Databases
I follow Microsoft’s recommendation — Split an Access Database — for all the Access databases I’m commissiond to create.
Taking the recommendation further, I suggest it’s good practice to have a split database in which, when a user opens it, the FE automatically checks for the BE and re-links to each of its tables. This article describes my approach, which offers these advantages:
- One cause of breakdown to a split database is the failure of the FE to link properly to the data tables in the BE.
- It’s an advantage to the client if the developer has given to the client control over where they choose to locate front-end and back-end files. So, if the client re-assigns drive mappings, or relocates or re-names folders, then the client doesn’t need to go back to the developer.
If you identify linkage breakdown as the cause of failure, you can manually re-establish the links between FE and BE.
Such a manual approach, generally, is not one for which you should expect users to take responsibilty. So, my process automatically re-links all tables as part of the FE’s startup routine.
My process gives the database owner a simple means of telling the database about any changes to files and their locations.
The program described here could be applied to front-end databases linking to tables illustrated in Creating a Contacts Database in MS Access, Party Data Model for Access Databases, Access Data Tables for Goods and Services Databases and Microsoft Access Database Tables for Project Management.
Cautions
If you get into this, and get as far as List Tables in BE DATA File you’ll see I use an array to store information about the tables to which links are to be made. But note that because an array will not work for a single element, you cannot use the program code for linking to a single table.
If you visited this web page before 2020-03-26 you wouldn’t have read the following caution.
Recently I’ve been working on a conversion of a substantial Access database to Office Access 365. The conversion threw up an issue I’d not come across before and has a direct bearing on the effectiveness of the process I’m describing here. Skip down the page to fnListLinkedTables() for the full story.
Initial Considerations
All DMW databases supplied to clients are split between BE (Back End) and FE (Front End). The BE comprises of tables only, as containers for the client's data. In a multi-user database the BE is in a folder on a LAN (Local Area Network) server. I name the BE file DATA.
All the forms, queries, reports, VBA code and so on that make up the user's view of the database are contained in the FE. Each user gets a FE on their workstation. I name the FE file USER.
The FE uses links to the tables in the BE. Such links can be made manually, using Access's Linked Table Manager, for example, or programmatically using VBA procedures, which are the topic of this article.
These are some of reasons I choose to automate the linking of tables as part of a start-up process initiated each time a user opens the FE:
- The presence of the BE file is confirmed as being accessible to the FE. (It can happen that someone moves the BE, not understanding the ramifications of the move!)
- A check is made for the full compliment of tables before the user gets to make use of the records in any of them. Those occasions on which I've suffered the office's “Access expert” fiddling with the table structure in a BE have convinced me that this check is a sensible step.
- If the BE file has become damaged, the user is posted a warning and prevented from using the database until the BE is restored. This is a precaution against file corruption.
- The client can choose where to place their BE and FE files. They do not need intervention from me if when choose to move files, folders, servers and so on.
(I've been told of a number databases already showing signs of corruption that remained in use until they finally ground to a halt. Some of these failed restoration from backups because backups had been made of files that were already corrupted!)
In addition to the DATA and USER files, there is a third file, KEY.ini, that completes the database package. The purpose of KEY.ini is to provide the means by which my client can themselves point USER to the folder in which DATA resides.
Tracking Back End File Location — KEY.ini
In my experience, clients like to be able to determine for themselves in which folder to place the BE.
Also, clients want to be able to change that folder’s name and location without needing me to tweak any code. This is a particular requirement when a client’s IT want the freedom to change locations for files on a network or to re-map drives.
Over the years, I’ve tried a number of ways of providing for this, currently settling on the use of the KEY.ini that is a simple text file, the content of which is this:
Important
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This file to be placed in same folder as USER file
Edit DataPath to correspond to your folder structure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[DEFAULT]
DataPath = "S:\Database\DATA.accdb"
It is vital that the name of your BE recorded against DataPath includes its file extension.
You must see to it that a copy of KEY.ini is in the same folder on their workstations as the users' USER files.
Structuring the Startup Program
The program that checks the links to tables resides in the FE. This program sub procedure, sStartUp, determines the sequence of VBA procedures, each performing a discrete operation in the overall linking process.
This is the skeleton of the sStartUp program comprising of four main function procedures:
Sub sStartUp()
' 1) Determine location of DATA file
fnGetPathFromKEY
' 2) Make a list of all tables to be linked
fnListLinkedTables
' 3) Delete all existing links
fnDeleteLinkedTables
' 4) Re-link to tales in DATA
fnLinkTables
End Sub
These are the roles played by each of these main sub-procedures:
fnGetPathFromKEY This VBA function sub-routine looks for KEY.ini and gets the DataPath from it. If fnGetPathFromKEY cannot locate KEY.ini, or is unable to extract from it the information about the DataPath, then fnGetPathFromKEY returns an error message to sStartUp.
fnListLinkedTables This VBA function delivers a list of the tables in DATA to where that list is needed in sStartUp.
fnDeleteLinkedTables This VBA function deletes any links that USER has to external tables. This makes sure that USER gets a fresh set of links and demolishes any legacy links that may be hanging around and are no longer relevant.
fnLinkTables This VBA function creates fresh links to those tables in DATA.accdb listed by fnListLinkedTables.
If at any point in the program one of the functions returns an error signal, sStartUp stops any use of the database and presents an explanatory message to the user in anticipation of technical attention to the problem.
In addition to those four main sub-procedures, sStartUp makes use of the function fnBlnFile, a general-purpose function for checking the existence of a given file. The functioin is called a couple of times: firstly, for testing for the KEY file; secondly for the DATA file.
The VBA code for each of the functions appears below, followed by the fully-fledged sStartUp program.
Checking Existence of a File
The function fnBlnFile determines whether a file, as identified by a full path description, 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.
1) Extract Location of DATA File from KEY
The job of the fnGetPathFromKEY sub-routine is to retrieve the path of the back-end DATA file from the KEY file:
Function fnGetPathFromKEY(pathINI$, element$) As String
On Error GoTo errHandler
Dim i&, lenElement&
Dim fstChar34%, lstChar34%
Dim lineINI$, path$
If Len(Dir(pathINI$)) > 0 And Len(element$) > 0 Then
lenElement& = Len(element$)
i& = FreeFile()
Open pathINI$ For Input As #i&
Do While Not EOF(i&)
Line Input #i&, lineINI$
If Left(lineINI$, lenElement&) = element$ Then
path$ = Mid(lineINI$, lenElement& + 1)
Exit Do
End If
Loop
Close #i&
fstChar34% = InStr(path$, Chr(34)) + 1
lstChar34% = InStrRev(path$, Chr(34))
path$ = Mid(path$, fstChar34%, lstChar34% - fstChar34%)
Else
path$ = "Error"
End If
procDone:
fnGetPathFromKEY = path$
Exit Function
errHandler:
path$ = "Error"
Resume procDone
End Function
If it's unable to return the whereabouts of DATA, then fnGetPathFromKEY passes a warning to sStartUp, which composes an explanatory message to the user.
2) List Tables in BE DATA File
fnListLinkedTables fills an array tbls$. This array has eight rows in this example corresponding to eight tables in DATA.
tbls$ has three columns. The first holds the names of the tables as they appear in DATA. The second, the names of those tables as you want them to appear in USER when they are linked in.
The third column holds the path to the BE DATA file, passed to fnListLinkedTables 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 fnListLinkedTables(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:
fnListLinkedTables = resp&
Exit Function
errHandler:
resp& = Err.Number
Resume procDone
End Function
fnListLinkedTables returns a zero if it completes the array. If it fails, it returns the number of the error that its error handler identifies.
Cautionary Note
In the introduction to this web page, I draw attention to a new issue relating to the table linking process. Recently a client commissioned me to convert a substantial Access database to Office Access 365. The database had an unusually high number of tables: 178, all in its back end.
My tbls$ array thus had 178 elements, with table names assigned in alphabetical order, as has been my habit. When drawing on the contents of this array, fnDeleteLinkedTables threw a sniffy on attempting to delete the link to the ninth table in the array, thus preventing completion of a linking process that I’d gainfully applied for years.
I checked the obvious things — including the spelling of the table name — and ran the whole routine again and again, with the same result each time. Then I went to the back end and viewed the table relationships, and noted that the offending table was in some strange relationships with other tables.
I came away baulking at the possibility of having to develop a new delete-table-relationships procedure to include in the linking program, if indeed this would address the issue. So I left my subconscious the problem while I got on with something productive.
On returning from a coffee and a bowl of porridge, my subconscious popped up the idea that I should move the position of the table in the array down. So I did. And that worked.
There could be a new rule here: in the array, place tables on the many side of relationships ahead of those on the one side. I’ve not yet had time to test it conclusively.
Listing the 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 sListBackEndTables() 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 VBE’s Immediate Window, input Call sListBackEndTables 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 fnListLinkedTables. 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 fnListLinkedTables.
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 a 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)
' 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&
i& = 0
procDone:
On Error Resume Next
fnDeleteLinkedTables = i&
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) fnLinkTables()
fnLinkTables performs the linking of the tables contained in the array of table named in the tbls$ array generated by fnListLinkedTables.
Function fnLinkTables() As Long
On Error GoTo errHandler
Dim db As DAO.Database
Dim tdf As TableDef
Dim i&, dmw&
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:
fnLinkTables = i&
On Error Resume Next
If Not tdf is Nothing Then Set tdf = Nothing
If Not db Is Nothing Then Set db = Nothing
Exit Function
errHandler:
i& = Err.Number
Resume procDone
End Function
fnLinkTables returns a zero if it completes satisfactorily. If it fails, it returns the number of the error that its error handler identifies.
The Controlling Procedure
In databases I create I place the code described in this article in its own module that I name modStartup.
There are four local module-level constants and variables to declare at the top of modStartup, namely pINI$, BE$, tbls$() and pFrm$:
Option Explicit
Private Const pINI$ = "KEY.ini"
Private BE$
Private tbls$()
Private Const pFrm$ = "frmNavigation"
Sub sStartUp()
On Error GoTo errHandler
Dim msg$, icon&, title$
sequence of sub-routines
procDone:
If msg$ <> vbNullstring Then
MsgBox msg$, icon& title$
End If
Exit Function
errHandler:
title$ = "Error in sStartUp"
icon& = vbCritical
msg$ = Err.Number & ": " & Err.Description
Resume procDone
End Sub
Within this module, we can now write the StarUp() program with the sequence of sub-routines:
Option Explicit
Private Const pINI$ = "KEY.ini"
Private BE$
Private tbls$()
Private Const pFrm$ = "frmNavigation"
Sub sStartUp()
On Error GoTo errHandler
Dim msg$, icon&, title$
Dim bln As Boolean
Dim path$, resp&
icon& = vbCritical
path$ = _
Left(CurrentProject.FullName, _
InStrRev(CurrentProject.FullName, "\"))
If fnBlnFile(path$ & pINI$) Then
BE$ = fnGetPathFromKEY(path$ & pINI$, "PathData")
Select Case BE$
Case "<no value>"
bln = False
title$ = "KEY File Error"
msg$ = "KEY file missing or faulty."
Case vbNullString
bln = False
title$ = "KEY File Fault"
msg$ = "KEY [DEFAULT] does not point to DATA"
Case Else
If fnBlnFile(BE$) Then
bln = True
Else
bln = False
title$ = "DATA File Fault"
msg$ = "DATA not located at " & BE$
End If
End Select
Else
bln = False
title$ = "Missing KEY File"
bln = False
msg$ = "Unable to locate KEY file."
End If
If bln Then
resp& = fnListLinkedTables()
If resp& = 0 Then
bln = True
Else
bln = False
title$ = "Table Listing Error"
msg$ = AccessError(resp&)
End If
End If
If bln Then
resp& = fnDeleteLinkedTables()
If resp& = 0 Then
bln = True
Else
bln = False
title$ = "Fault Breaking Links"
msg$ = AccessError(resp&)
End If
End If
If bln Then
resp& = fnLinkTables()
If resp& = 0 Then
bln = True
msg$ = vbNullString
Else
bln = False
title$ = "Error Linking Tables"
msg$ = AccessError(resp&)
End If
End If
If bln Then DoCmd.OpenForm pFrm$, acNormal
procDone:
If msg$ <> vbNullstring Then
msg$ = msg$ & _
vbNewLine & vbNewLine & _
"Access will now close"
MsgBox msg$, icon& title$
DoCmd.Quit
End If
Exit Sub
errHandler:
title$ = "UNANTICIPATED ERROR AT STARTUP"
msg$ = & _
"Please make a note of this error: " & _
vbNewLine & vbNewLine & _
Err.Number & ": " & Err.Description & _
vbNewLine & vbNewLine & _
"Please check your workbook before saving"
Resume procDone
End Sub
On successful completion sStartUp() opens the form, in this example named “frmNavigation”. Should the process fail it closes Access altogether after presenting the user with a message explaining why.
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.
Front End Table Linking Failures
On a couple of occasions getting a new database up-and-running, users reported this error and the database failing to open:
The cause of the error was lack of permissions assigned to all the users. To avoid the error, make sure that each user is given full admission to the database back end, that includes to the folder and drive in which it is located.