MS Access, Linking backend tables using VB code over network

Question

I have made a Tracking Database for work that has a front end interface and backend tables. Right now I have a section of code which upon start up will link the front end with the back end tables so people can copy and paste the front end from "G:\Sections\MEO\DDPO\Active Issues\Tracking Database" to their desktop and it will still be able to access the backend file.

The Issue I want to avoid is if someone changes the file path of where the back end is located the front end will not be able to find the backend.

example:

Original Path G:\Sections\MEO\DDPO\Active Issues\Tracking Database\Database Backend\tracking Database_be.accdb

If someone Modified the Path

G:\New Sections\MEO\DDPO\Active Issues\Tracking Database\Database Backend\tracking Database_be.accdb

This is the current code I use for the front end to ensure it links to the backend upon start up.

Private Function ReconnectTables() As Boolean
On Error Resume Next

Dim tdf As DAO.TableDef
Dim dbs As DAO.Database
Dim strPath As String
Dim strConnect As String

Set dbs = CurrentDb

strPath = dbs.Name

strPath = "G:\Sections\MEO\DDPO\Active Issues\Tracking Database\Database Backend\tracking Database_be.accdb"

strConnect = strPath

For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" Then
        tdf.Connect = ";DATABASE=" & strConnect
        tdf.RefreshLink
    End If
Next

Set dbs = Nothing
If Err.Number = 0 Then ReconnectTables = True

End Function

Welcome to Stack Overflow, @Munroe. Not quite sure what your question is, but I suggest you don't hard-wire the back-end file path into your VBA procedure. Instead, store it in a settings table and have the procedure retrieve it from there. There may be other related issues you need to resolve; what are they? — HansUp
The backend should not move (unlike frontends) but remain stationary in one place for multiple-user environments. It even appears you are creating new BEs for each Section? If so, consider normalizing and use one database for all sections. — Parfait
@Parfait The back end does not move, but what if someone changed the name of one of the previous file names in the chain. In the example above the path name had the file name "sections" changed to "new sections". the back end is still in the same place but the extension has now changed and the original code would not locate the backend anymore. @ HansUp Would I not run into the same issue if I stored the file path in a settings table and used a procedure to retrieve it. I do have a few issues with my database but those are more ease of use issues. It is currently in a working state. — Munroe Mayhew
Even more importantly, folders should not change names (especially if links were sent out earlier). Can't your team agree on the rule? — Parfait
This is a process and not programming issue. Have even the folder locked down by Window permissions. Don't code workarounds for environment issues like this. My two cents. — Parfait

Munroe Mayhew Munroe Mayhew · Accepted Answer · 2018-10-04T15:25:00

I ended up removing the hard written path from the code and added a line where it checks a value in a table. That value stored in the table is the path which the user can select in a separate form which now gets called if it detects the tables are not linked.

My new code looks like this:

Private Function ReconnectTables() As Boolean
On Error Resume Next

Dim tdf As DAO.TableDef
Dim dbs As DAO.Database
Dim strPath As String
Dim strConnect As String

Set dbs = CurrentDb

strPath = dbs.Name

strPath = me.BackEndPath.Value

strConnect = strPath

For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" Then
        tdf.Connect = ";DATABASE=" & strConnect
        tdf.RefreshLink
    End If
Next

Set dbs = Nothing
If Err.Number = 0 Then ReconnectTables = True

End Function

I used to just have a message box that would pop up if the tables were not linked but now I added 2 lines of code to open a form (frmBEpath) if the tables are not linked.

Private Sub Form_Load()


 On Error Resume Next

'DoCmd.ShowToolbar "Ribbon", acToolbarNo

If ReconnectTables() = True Then
    strVerClient = Nz(DLookup("[VersionNumber]", "[tblVersionClient]"), "")
    strVerServer = Nz(DLookup("[VersionNumber]", "[tblVersionServer]"), "")
    Me.Repaint
Else
    'MsgBox "Couldn't Find Data Tables.  Exiting.", vbCritical, "Error"
   DoCmd.Close
   DoCmd.OpenForm "frmBEpath"
End If

End Sub

This new form tells the user the tables are not linked and it gives them a button to browse for the backend files. When they select the backend file and click the yes button it stores the path name in a table.

Option Compare Database

Public Function FolderSelection() As String
Dim objFD As Object
Dim strOut As String

strOut = vbNullString
Set objFD = Application.FileDialog(3)
If objFD.Show = -1 Then
    strOut = objFD.SelectedItems(1)
End If
Set objFD = Nothing
FolderSelection = strOut
End Function

Private Sub btnBrowse_Click()
Dim strChoice As String
strChoice = FolderSelection
If Len(strChoice) > 0 Then
    Me.txtPath = strChoice
End If
End Sub


Private Sub btnConfirmYes_Click()
Me.BackEndPath.Value = Me.txtPath.Value
DoCmd.Close
DoCmd.OpenForm "frmsplash"

End Sub

MS Access, Linking backend tables using VB code over network

1 Answers