Mike Perris



home >> access >> VBA to refresh links between Access front and backend files

Using VBA to refresh links between Access frontend and backend database files.

If you’ve split an Access database into front and back ends and then move your database to a new machine, you may well find that the links no longer work – this is a serious problem if you distribute a new version of your front end to other people.

The following VBA macro code will programmatically relink your database every time your database front-end is started. All you need to do is add the code to the form_load event handler for the first form that your front-end loads.

If your front and back-end files are in the same directory as the front-end, the code will re-link them quite happily without any further input from yourself. However, if the front and back-ends are in different directories, the VBA must be told where the backends have been placed – it can’t “guess” where on your computer (or network) you’ve put them.

To achieve this, you will need to define an environment variable called DBBEPATH which points to the location of the backend files. You will need to consult your Windows manuals for details of setting environment variables, as it varies slightly between different versions of Windows.

Note that in Access 2010, there’s a nasty little bug that gives the error message “Invalid database object reference” when backend files are relinked using VBA, normally requiring a compact and repair, which you can’t do if you’ve distributed compiled VBA code. To avoid the compact and repair, there is additional VBA in the following program that addresses the issue, preventing the error; just look for the segment starting “'Bugfix for Access 2010 "Invalid database object reference" problem”.

Standard disclaimer:

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Private Sub Form_Load() ' the following code refreshes the links to linked tables. Dim strMsg As String 'Run the Procedure, getting any error messages. strMsg = RefreshTableLinks() 'strMsg will be a zero-length string if there is no error message. If Len(strMsg & "") = 0 Then Debug.Print "All Tables were successfully relinked." Else 'Notify the user of the errors. Debug.Print strMsg MsgBox strMsg, vbCritical End If End Sub '---------------------------------------------------------------------------- ' Procedure: RefreshTableLinks ' Purpose: Refresh table links to back-ends in the same folder as front end ' (or path pointed to by DBBEPATH environment variable). ' Note: Linked Tables can be in more than one back-end. ' Return: Returns a zero-length string if all tables are relinked. ' Return: Or returns a string listing tables not relinked and errors. ' based on  http://blogs.office.com/b/microsoft-access/archive/2012/08/03/how-to-automatically-relink-microsoft-access-tables-.aspx '---------------------------------------------------------------------------- Public Function RefreshTableLinks() As String On Error GoTo ErrHandle Dim db As DAO.Database Dim tdf As DAO.TableDef Dim strCon As String Dim strBackEnd As String Dim strMsg As String Dim intErrorCount As Integer Dim strDBBEPath As String 'DataBase BackEnd Path strDBBEPath = Environ("DBBEPATH") If Len(strDBBEPath & "") = 0 Then strDBBEPath = CurrentProject.Path End If Set db = CurrentDb 'Loop through the TableDefs Collection. For Each tdf In db.TableDefs 'Verify the table is a linked table. If Left$(tdf.Connect, 10) = ";DATABASE=" Then 'Get the existing Connection String. strCon = Nz(tdf.Connect, "") 'Get the name of the back-end database using String Functions. strBackEnd = Right$(strCon, (Len(strCon) - (InStrRev(strCon, "\") - 1))) 'Verify we have a value for the back-end If Len(strBackEnd & "") > 0 Then 'Set a reference to the TableDef Object. Set tdf = db.TableDefs(tdf.Name) 'Build the new Connection Property Value. tdf.Connect = ";DATABASE=" & strDBBEPath & strBackEnd 'Refresh the table link. tdf.RefreshLink Else 'There was a problem getting the name of the back-end. 'Add the information to the message to notify the user. intErrorCount = intErrorCount + 1 strMsg = strMsg & "Error getting back-end database name." & vbNewLine strMsg = strMsg & "Table Name: " & tdf.Name & vbNewLine strMsg = strMsg & "Connect = " & strCon & vbNewLine End If End If Next tdf ' Bugfix for Access 2010 "Invalid database object reference" problem ' http://www.utteraccess.com/forum/Invalid-database-Object-R-t1953275.html Dim QD As QueryDef For Each QD In CurrentDb.QueryDefs QD.SQL = QD.SQL Next ExitHere: On Error Resume Next If intErrorCount > 0 Then strMsg = "There were errors refreshing the table links: " _ & vbNewLine & strMsg & "in Procedure RefreshTableLinks." RefreshTableLinks = strMsg End If Set tdf = Nothing Set db = Nothing Exit Function ErrHandle: intErrorCount = intErrorCount + 1 strMsg = strMsg & "Error " & Err.Number & " " & Err.Description strMsg = strMsg & vbNewLine & "Table Name: " & tdf.Name & vbNewLine strMsg = strMsg & "Connect = " & strCon & vbNewLine Resume ExitHere End Function

OTHER ACCESS ITEMS

Access 2010 VBA 'On Not In List' event

Securing Access databases

Exporting Access data to a website

VBA to re-link front and backend database files

Data validation

Mail-merge made easy

Reports: mailshots without mail-merge

Importing data from Microsoft Excel

Manually creating a backup of your database

Automating backups of your Access database

 

Website design Copyright (C) 2007-2014 Mike Perris.