Solved New Linked Table manager - Switch connection between Server and Local BE (1 Viewer)

[Nagesh]

Hi

Whenever there is modification required to Access database, I change the Linked Table to local BE to carry out the work.
I complete the modifications / testing using Local BE and then link all tables to the Server BE.

I save the FE file as ACCDE/ACCDR and then distribute the same to users.

My Query;
Is there a way in which I can 'save' the contents / linked tables of 'data source' (eg server BE / Local BE copy)?

Currently, during modifications, I am first 'deleting' the links to server BE and then going through the whole process of selecting individual tables to link tables to the local BE.
It would be great if I could just save the Server BE path and corresponding tables and also Local BE path and corresponding tables.
That way I can switch between Server and Local BE whenever required.

 

[arnelgp]

if the BE is Access also:

create a table(tblBE) with two fields:

BEPath (short text, the path to BE)
IsLocal (Yes/No)

fill this table with the paths and Set IsLocal to Yes if the path is pointing
to Local be.

you can use Dlookup() to get the Local/Server path:

'for local
sPath = DLookup("BEPath", "tblBE", "IsLocal = True")
'for server
sPath = DLookup("BEPath", "tblBE", "IsLocal = False")

you need another routine to change the BE:

dim tbl as new collection
dim scr as new collection
dim i as integer
dim db as dao.database
dim td as dao.tabledef
set db=currentdb
for each td in db.tabledefs
    if Left$(td.connect,10) = ";Database=" then
        tbl.add td.name, td.name
        scr.add td.sourcetablename, td.sourcetablename
    end if
next
for i = 1 to tbl.count
    call RelinkTable(sPath, tbl(i), scr(i))
next


'''''''''''''''
'''''''''''''''
Public Sub RelinkTable(ByVal DbPath As String, ByVal TableName As String, ByVal LinkName As String)

    Dim db As DAO.Database
    Dim td As DAO.TableDef
    
    If Len(LinkName) < 1 Then
        LinkName = TableName
    End If
    
    Set db = CurrentDb
    Set td = Nothing
    If DCount("1", "MsysObjects", "Name = '" & LinkName & "' And Type = 6") > 0 Then
        db.TableDefs.Delete LinkName
    End If
    Set td = db.CreateTableDef(LinkName)
    td.SourceTableName = TableName
    td.Connect = ";DATABASE=" & DbPath
    db.TableDefs.Append td
    
    db.TableDefs.Refresh
    Application.RefreshDatabaseWindow
    Set td = Nothing
    Set db = Nothing
End Sub

 

[Nagesh]

Excellent arnelgp !! This is what I was looking for.

Thanks very much for the code.
Additionally , I would create a form in the FE to link to required tables (Server / local)
Would also add field indicating path in the form to confirm connected BE is either Server / local

Thanks again

 

[arnelgp]

goodluck

 

[Pat Hartman]

The linked tables manager allows you to switch BEs. You just check the "prompt" for new location box.

 

[Nagesh]

The linked tables manager allows you to switch BEs. You just check the "prompt" for new location box.

Not sure which version of the Linked table Manager has this option.
In my case I was talking about Office 365

 

[Pat Hartman]

Refresh just refreshes the links to the current BE. The Relink button gives you the option of choosing a different BE. Here's a simple form. Use the browse button to pick a new BE. Then press relink next to the group of links you want to redirect. This form is intended to be used to swap production and test BE's for a database that is linked to multiple BE's.

 

[Nagesh]

Refresh just refreshes the links to the current BE. The Relink button gives you the option of choosing a different BE. Here's a simple form. Use the browse button to pick a new BE. Then press relink next to the group of links you want to redirect. This form is intended to be used to swap production and test BE's for a database that is linked to multiple BE's.

Thanks Part Hartman

Works well !!

 

[Pat Hartman]

You're welcome