Change source of linked tables (1 Viewer)

eoan · Mar 13, 2009

Change source of linked tables - Solved!

I have a db (mydb.mdb) with a linked table ("tbl_one"), currently pointing to a table with the same name in an access db called "sourcedb-feb". I'd like to use vba to open a dialog box for the user to select a new source (like "sourcedb-mar") and to alter the table's link accordingly (the table name will be the same in the new source database as well).

From searching here and msdn I've got the following for the dialog box:

Code:

Dim FileChosen As String
With Application.FileDialog(1)
    .AllowMultiSelect = False
    .Filters.Add "DataSources", "*.mdb", 1
    .InitialFileName = "\\path\sourcedb-*.mdb"
    .Title = "Select Source Database"
    .Show
End With
If Application.FileDialog(1).SelectedItems.Count = 1 Then
FileChosen = Application.FileDialog(1).SelectedItems(1)
Else
MsgBox ("No file selected.")
End If

but all I've been able to turn up re: links are a couple of snippets of code I don't understand how to use, namely:

Code:

CurrentDb().TableDefs(TableName).SourceTableName

and from msdn

Code:

ConnectOutput OpenDatabase("mydb.mdb",_
            "AccessTable", _
            ";DATABASE= " & FileChosen & ", _
            "tbl_one"

If anyone could give me an idea of how to put this together (like, in the last bit of code, does "AccessTable" refer to a table in my db, or is it to tell access I'm going to be linking to an access table??) I'd be really grateful - cheers!

DCrake · Mar 13, 2009

There are many threads on updating linked tables you just ned to know the correct terminology.

Try Relink tables

James Dudden · Mar 13, 2009

Try this:
Private Sub Relink_Click()
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim NewPathname As String
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
NewPathname = InputBox("Input the path here.", "Input Path", Default)
'Loop through the tables collection
For Each Tdf In Tdfs
If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
Tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table
End Sub

eoan · Mar 13, 2009

Thanks James, unfortunately I know almost nothing about vb and I don't really understand what your code's doing - but I don't want to update all the linked tables as some link to other sources that need to be preserved, just tbl_one needs to be changed.

What do you think of:

Code:

Private Sub Relink_Click()

'Choose file from dialog box
Dim FileChosen As String
With Application.FileDialog(1)
    .AllowMultiSelect = False
    .Filters.Add "Datasources", "*.mdb", 1
    .InitialFileName = "\\path\sourcedb-*.mdb"
    .Title = "Select Source Database"
    .Show
End With
If Application.FileDialog(1).SelectedItems.Count = 1 Then
FileChosen = Application.FileDialog(1).SelectedItems(1)
Else
MsgBox ("No file selected.")
GoTo exit_function
End If

'Change link and refresh
CurrentDb.TableDefs("tbl_one").Connect = ";DATABASE=" & FileChosen
CurrentDb.TableDefs("tbl_one").RefreshLink

End Sub

Does that look about right?

Thanks again for your help with this!

James Dudden · Mar 13, 2009

Almost right. try this:

Private Sub Relink_Click()
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim NewPathname As String
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
Dim FileChosen As String
With Application.FileDialog(1)
.AllowMultiSelect = False
.Filters.Add "Datasources", "*.mdb", 1
.InitialFileName = "\\path\sourcedb-*.mdb"
.Title = "Select Source Database"
.Show
End With
If Application.FileDialog(1).SelectedItems.Count = 1 Then
FileChosen = Application.FileDialog(1).SelectedItems(1)
Else
MsgBox ("No file selected.")
End If
For Each Tdf In Tdfs
If Tdf.SourceTableName = "tbl_one" Then
Tdf.Connect = ";DATABASE=" & FileChosen
Tdf.RefreshLink
End If
Next

End Sub

eoan · Mar 19, 2009

Thanks very much James, very helpful - apologies for the delay in replying, away for a few days...

I've now had a couple of successful runs and thought i'd post the code as it currently stands.

This code queries a table (tbl_updatelinks - two fields, TableName (text) and Deleted (yes/no)) containing names of linked tables for those not logically deleted (yes/no tickbox left unticked). It then changes the link for each of the selected tables to the file chosen by the user (initially filtered for mdbs beginning "SourceDB-"). Any comments/ corrections gratefully received!

Code:

'Private Sub updatelinks()

Function updatelinks()
On Error GoTo updatelinks_Err

'Open file chooser, allow user to select new SourceDB
Dim FileChosen As String
With Application.FileDialog(1)
    .AllowMultiSelect = False
    .Filters.Add "Source DBs", "*.mdb", 1
    .InitialFileName = "\\Path\SourceDB-*.mdb"
    .Title = "Select Source Database"
    .Show
End With
'If user hasn't selected a file, quit, otherwise proceed
If Application.FileDialog(1).SelectedItems.Count = 1 Then
FileChosen = Application.FileDialog(1).SelectedItems(1)
Else
MsgBox "No file selected."
GoTo updatelinks_Exit
End If

Dim dbs As Database
Dim tdf As TableDef
Dim tdfs As TableDefs

Set dbs = CurrentDb
Set tdfs = dbs.TableDefs

Dim updatelist_rs As ADODB.Recordset
Dim updatelist_sql As String
Dim updatelist_tname As String


'Open (in memory) list of tables to update; picks from a_tbl_links using [not logically deleted]
Set updatelist_rs = New ADODB.Recordset
updatelist_sql = "SELECT * FROM tbl_updatelinks WHERE ((tbl_updatelinks.Deleted) = 0);"
updatelist_rs.ActiveConnection = CurrentProject.Connection
updatelist_rs.Open updatelist_sql, , adOpenDynamic, adLockOptimistic

'Table-updating loop
Do While Not updatelist_rs.EOF

    'Set variable - table to update
    updatelist_tname = updatelist_rs.Fields("TableName")

    'Change link and refresh
    For Each tdf In tdfs
        If tdf.SourceTableName = updatelist_tname Then
        tdf.Connect = ";DATABASE=" & FileChosen
        tdf.RefreshLink
        End If
    Next
        
    'Next line of query list
    updatelist_rs.MoveNext

'End of table-updating loop
Loop


MsgBox "done."

'Exit this function
updatelinks_Exit:
    Exit Function
'Error reporting in function
updatelinks_Err:
    MsgBox Error$
    Resume updatelinks_Exit



End Function

All you need to do is put the names of the tables whose links you want to change in tbl_updatelinks and make sure there's no tick in the 'Deleted' column.

Change source of linked tables (1 Viewer)

eoan

Registered User.

DCrake

Remembered

James Dudden

Access VBA Developer

eoan

Registered User.

James Dudden

Access VBA Developer

eoan

Registered User.

Similar threads

Users who are viewing this thread