dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Check Access Table Links on Startup

How to use VBA to re-establish linked tables when the front end of a split Access database is opened by a user.

Last updated on 2018-11-23 by David Wallis.


Preface

It's my contention that good practice is to have a split database in which the front end automatically checks for the back end and re-links to each of its tables.

Also, that it’s plus for the client if the developer has given control to the client of where the front end and back end files are located.

The program described here could be applied to the database described on my create contacts database page.


One Caution

If you get into this, and get as far as dmwListLinkedTables Sub-Procedure you’ll see I make use of 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 code as is for linking to a single table.


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 BE file USER

The FE uses links to the tables in the BE. Such links can be made manually, using Access's Link 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 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 placed alongside each user's USER file. That means in the same folder on their workstation.


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 descrete operation in the overall linking porcess.

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 these the function dmwBlnFile for checking the existence of a given file, in this case, of DATA.

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

dmwGetPathFromKEY This VBA-function sub-routine will look for KEY and get the export path from it. If dmwGetPathFromKEY cannot locate KEY.ini, or is unable the find the information about the export path, then it will return an error message to dmwStartUp().

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

dmwDeleteLinkedTables This VBA-function sub-routine 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 sub-routine creates links to those tables, listed by dmwListLinkedTables, in DATA as identified by dmwCheckPath.

If at any point in the program a sub-procedure throws up an error, dmwStartUp() presents a message to the user and stops any use of the database, pending appropriate attention to the problem.

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


dmwBlnFile Sub-Procedure

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 Sub-Procedure

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 warnings from which dmwStartUp composes messages 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 columnn 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.


dmwDeleteLinkedTables Sub-Procedure

Before initiating linking of BE tables into the FE, I opt to delete existing links so that linking can start from a fresh sheet — sometimes a table or two in the BE, introduced during the development of a database, are no longer needed by the FE and can therefore be excluded from the linking process.

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:

db.Close

Set db = Nothing

dmwDeleteLinkedTables = i&

Exit Function

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.


dmwLinkTables Sub-Procedure

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:

db.Close

Set db = Nothing

dmwLinkTables = i&

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.


Give DMW’s Tips Your Support

ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

Thanks, in anticipation.


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

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.

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

Guiseppe di Lampedusa, The Leopard (1958)