DMW logo
contact@consultdmw.com

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

Last updated on 2022-10-23 by David Wallis.


Preface

The original article 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 is a bit heavy on code. The page you’re reading describes my effort to lighten the load.

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

Creating array of table names in an Access database

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

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

Second dimension of database table name array

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

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

Third dimension of Access table name array

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

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 contained in the array of table named 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.


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.

“Everything that can be invented has been invented.”

Charles H. Duell, Commissioner, U.S. Office of Patents, 1899