DMW logo
tel 01732 833085
tonbridge · kent · UK

How to Link Tables in an Access Split Database at Startup

How to use VBA to link the FE (front end) of a split Access database to tables in the BE (back end) when a user opens the FE.

If you visited this page before this update, then please read the additions to the Caution item below.

Last updated on 2020-07-27 by David Wallis.


Splitting Access Databases

I follow Microsoft’s recommendation — Split an Access Database — for all the 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

If you get into this, and get as far as dmwListLinkedTables Sub-Procedure you’ll see I use an array to store information about the tables to which links are to be made. My caution is 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 this 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 dmwListLinkedTables() 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.

Access Ribbon

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:

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 workstation as the user's USER file.


Structuring the Startup Program

The program that checks the links to tables resides in the FE. This program procedure, dmwStartUp(), determines the sequence of VBA sub-procedures, each performing a discrete operation in the overall linking process.

This is the skeleton of the dmwStartUp() program:

Function dmwStartUp()

dmwGetPathFromKEY

dmwListLinkedTables

dmwDeleteLinkedTables

dmwLinkTables

End Function

In addition to those four main sub-procedures, dmwStartUp() makes use of the function dmwBlnFile() for checking the existence of a given file, in this case, of DATA.accdb.

These are the roles played by each of the main sub-procedures:

dmwGetPathFromKEY() This VBA function sub-routine looks for KEY.ini and gets the export path from it. If dmwGetPathFromKEY() cannot locate KEY.ini, or is unable to extract from it the information about the export path, then dmwGetPathFromKEY() returns an error message to dmwStartUp().

dmwListLinkedTables() This VBA function delivers a list of the tables in DATA to where that list is needed in dmwStartUp().

dmwDeleteLinkedTables() 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.

dmwLinkTables() This VBA function creates fresh links to those tables in DATA.accdb listed by dmwListLinkedTables().

If at any point in the program one of the functions returns an error signal, dmwStartUp() stops any use of the database and presents an explanatory message to the user in anticipation of technical attention to the problem.

The VBA code for each of the functions appears below, followed by the fully-fledged dmwStartUp() program.


dmwBlnFile()

This function determines whether a file, as identified by a full path description, exists at the location supplied to its file$ argument.

Function dmwBlnFile(file$) As Boolean

On Error Resume Next

Dim attrib&

attrib& = GetAttr(file$)

dmwBlnFile = _

(Err.Number = 0) _

And ((attrib& And vbDirectory) = 0)

End Function

dmwBlnFile returns TRUE if it locates the file in the stated folder, FALSE if it doesn't.


dmwGetPathFromKEY()

The job of the sub-routine dmwGetPathFromKEY is to retrieve the path of the back-end DATA file from KEY:

Function dmwGetPathFromKEY(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:

dmwGetFromKEY = path$

Exit Function

errHandler:

path$ = "Error"

Resume procDone

End Function

If it's unable to return the whereabouts of DATA, then dmwGetPathFromKEY() passes a warning to dmwStartUp, which composes an explanatory message to the user.


dmwListLinkedTables() 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 DATA, passed to dmwListLinkedTables() 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 dmwListLinkedTables(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:

dmwListLinkedTables = resp&

Exit Function

errHandler:

resp& = Err.Number

Resume procDone

End Function

dmwListLinkedTables() 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 drew 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, dmwDeleteLinkedTables() 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.


Filling the Array of Tables

You can use Excel to create the entries for the array used by dmwListLinkedTables(). 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,"""")

Table array

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

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

Table array

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

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

Table array

Finally, copy and paste the content of Columns B, C and D in turn into dmwListLinkedTables().


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 dmwListBackEndTables() 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 dmwListAllTables and press enter. The procedure will list all the tables in the Window from where you can copy them.


dmwDeleteLinkedTables() — Version 1

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 process removes any tables that are no longer relevant, perhaps those at sometime introduced during the development of a database.

An Access database has a number of tables in addition to the ones you introduce. One of these is MSysObjects. The SQL$ query in dmwDeleteLinkedTables() filters MSysObjects for those tables the names of which begin with tbl or tlu.

Without exception, I use these prefixes in all the databases I create; hence I can rely on them for this procedure to work.

Function dmwDeleteLinkedTables() As Long

On Error GoTo errHandler

Dim rs As DAO.Recordset, SQL$

Dim tdf As TableDef

Dim db As DAO.Database

Dim i&

SQL$ = _

"SELECT MSysObjects.Name FROM MSysObjects " & _

"WHERE " & _

"(((MSysObjects.Type)=6) " & _

"AND (Left(MSysObjects.Name,3)='tbl') " & _

"OR ((MSysObjects.Type)=6) " & _

"AND (Left(MSysObjects.Name,3)='tlu')) " & _

"ORDER BY MSysObjects.Name;"

Set db = CurrentDb

Set rs = db.OpenRecordset(SQL$, dbOpenSnapshot)

If rs.RecordCount > 0 Then

rs.MoveFirst

i& = 0

Do While Not rs.EOF

i& = i& + 1

ReDim Preserve tbls$(i&)

tbls$(i&) = rs!Name

rs.MoveNext

Loop

rs.Close

Set rs = Nothing

For i& = 1 To UBound(tbls$())

Set tdf = db.TableDefs(tbls$(i&))

db.TableDefs.Delete tbls$(i&)

Set tdf = Nothing

Next i&

End If

i& = 0

procDone:

On Error Resume Next

dmwDeleteLinkedTables = 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

dmwDeleteLinkedTables() returns a zero if it completes satisfactorily. If it fails, it returns the number of the error that its error handler identifies.


dmwDeleteLinkedTables() — Version 2

Version 1 of dmwDeleteLinkedTables(), above, is fine if your database’s tables follow your naming convention rigorously. For most of the databases I’ve been engaged to convert, however, this is not the case. So, I have another way of deleting the links:

Function dmwDeleteLinkedTables() As Long

On Error GoTo errHandler

Dim l&

Dim counter&, tblName$

For counter& = 1 To UBound(tbls$())

tblName$ = tbls(counter&)

l& = dmwDeletTbl(tblName$)

Next counter&

procDone:

dmwDeleteLinkedTables = l&

Exit Function

errHandler:

l& = Err.Number

Resume procDone

End Function

This dmwDeleteLinkedTables() runs through the list of tables in the tbls() array and for each entry calls on the function dmwDeletTbl() to perform the deletion:

Function dmwDeletTbl(tbl) As Long

On Error GoTo errHandler

Dim l&

DoCmd.DeleteObject acTable, tbl

l& = 0

procDone:

dmwDeletTbl = l&

Exit Function

errHandler:

Select Case Err.Number

Case 7874 ' Table does not exist

l& = 0

Case Else

l& = Err.Number

End Select

Resume procDone

End Function


dmwLinkTables()

dmwLinkTables() performs the linking of the tables contained in the array of table names generated by dmwListLinkedTables().

Function dmwLinkTables() 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:

On Error Resume Next

dmwLinkTables = i&

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

dmwLinkTables() 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 — dmwLinkTables()

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: pINI$, BE$, tbls$() and pFrm$.

Option Explicit

Private Const pINI$ = "KEY.ini"

Private BE$

Private tbls$()

Private Const pFrm$ = "frmNavigation"


Sub dmwStartUp()

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 StartUp"

icon& = vbCritical

msg$ = Err.Number & ": " & Err.Description

Resume procDone

End Function

We can now complete the dmwStarUp() program with the sequence of sub-routines:

Option Explicit

Private Const pINI$ = "KEY.ini"

Private BE$

Private tbls$()

Private Const pFrm$ = "frmNavigation"


Sub dmwStartUp()

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 dmwBlnFile(path$ & pINI$) Then

BE$ = dmwGetPathFromKEY(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 dmwBlnFile(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& = dmwListLinkedTables()

If resp& = 0 Then

bln = True

Else

bln = False

title$ = "Table Listing Error"

msg$ = AccessError(resp&)

End If

End If

If bln Then

resp& = dmwDeleteLinkedTables()

If resp& = 0 Then

bln = True

Else

bln = False

title$ = "Fault Breaking Links"

msg$ = AccessError(resp&)

End If

End If

If bln Then

resp& = dmwLinkTables()

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 Function

errHandler:

title$ = "Error in StartUp"

icon& = vbCritical

msg$ = Err.Number & ": " & Err.Description

Resume procDone

End Function

On successful completion dmwStartUp() 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) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification


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, and to the folder and drives 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)