contact@consultdmw.com

How to Link Tables in a Split Microsoft® Access 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 2024-04-27.


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:

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

Single Table Caution

If you get as far as List Tables in BE DATA File, below, 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.

Order of Tables Caution

If you visited this web page before 2020-03-26 you wouldn't have read this caution.

I was 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 in this article.

Skip down the page to fnListLinkedTables() for the full story.


Initial Considerations

All databases supplied to clients by me 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.

The forms, queries, reports, macros and modules 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:

Microsoft Access ribbon

Alternatively, links can be made programmatically using VBA procedures — the topic of this article.

These are some of the reasons I choose to automate the linking of tables as part of a start-up process initiated each time a user opens the FE:

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, some 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 posts a warning to sStartUp. sStartUp then displays 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,"""")

Create array in Excel

To create the second dimension, copy and paste this formula into Cell C1 and copy it down:

=CONCAT("tbls$(",ROW(),",2)=""",A1,"""")

Create second dimension of array in Excel

For the third dimension, copy and past this formula into Cell D1 and copy it down:

=CONCAT("tbls$(",ROW(),",3)=","BE$")

Create third dimension of array in Excel

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.


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::

  1. 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)
  2. This bit of code forms that function —
  3. fnCallControlProgram

    Call sStartUp()

    End Function

  4. With the function installed, create the AutoExec macro. From Create on the ribbon's menu, choose Macro
  5. Complete this macro with its RunCode Action set to =fnCallOntrolProgram
    Access AutoExec macro
  6. Save the name macro naming it AutoExec.

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.


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:

Error linking tables

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.

“If we want things to stay as they are, things will have to change.”

Guiseppe di Lampedusa, The Leopard (1958)