Linking backend in Access runtime (1 Viewer)

[Rmaster2022]

I created a database for someone to use on their computer. They do not have the full ACCESS application, so I loaded the runtime version on their computer. I split the database so that I could make changes to the front end without changing any of the data in their tables. Both the FE and the BE will be in the same folder on the one computer. Since I cannot use the linked table manager in runtime, what would be the VBA code to direct the FE to the BE? I prefer a simple code where I include the path to the BE in the VBA code. Or am I taking a wrong approach to this?

I did spend quite a bit of time looking for such a VBA code. I found this one. Will it work? Would I use it as an on load event for the unbound main menu?

Dim dbCurr As Database
Dim tdfTableLink As TableDef

For Each tdfTableLink In dbCurr.TableDefs
tdfTableLink.Connect = ";DATABASE=" & (Insert new file path)
tdfTableLink.RefreshLink
Next

 

[moke123]

Take a look and see if JStreet ReLinker will help.

https://jstreettech.com/developer-downloads/

 

[arnelgp]

sample code:

Public Sub relink()
Dim dbCurr As dao.Database
Dim tdfTableLink As dao.TableDef
Set dbCurr = CurrentDb
For Each tdfTableLink In dbCurr.TableDefs
    With tdfTableLink
        ' Note:
        '
        ' 1. replace \\127.0.0.1\ with the correct IP address of the (share host)
        '    you can also use it's Computer name, eg. \\Computer1\
        '
        ' 2. replace "shared" with the correct folder name of the
        '    share folder where the BE resides.
        '
        If InStr(1, .Connect, "DATABASE=") <> 0 Then
            .Connect = ";DATABASE=\\127.0.0.1\shared\demo_acad_be.accdb;"
            .RefreshLink
        End If
    End With
Next
End Sub

 

[bastanu]

You say the FE and BE will be in the same folder (=only one user), so you can get the path to the BE form the FE itself (CurrentProject.Path). Notice in arnelgp's code the check for a linked Access ("DATABASE=") table before attempting to refresh its .Connect property (it is missing in your original code). That ensures local or any other linked tables (Excel, ODBC) don't get touched.

Probably the safest would be to create a public function that you call as the first Action of an AutoExec macro; running it from an unbound form as you mention should also work but you have to make sure the form does not have any links to the linked tables (like in row sources for combos or listbox controls, etc.).

Cheers,