Create a "dynamic" link between databases 2

[RGordy]

Have created two databases. DB #1 is just tables. DB#2 contains the forms, etc. for DB#1. These are on a zip disk. When I change computers and the drive designation changes for the zip disk, I have to delete the links and then re-link the two databases. Is there a way the program can do this, if the two databases are always in the same sub-directory?

 

[FancyPrairie]

You could set up a option group or something the you select which database you want to link to. In the example below, I have a command button that toggles between Archive data and live data. The caption on the command button initially says "Link to Archive database". When selected, the program links to the archive database and the caption of the command button changes to "Link to Live database". This concept could work for you.

Private Sub cmdViewArchive_Click()

Dim strDatabase As String

If (InStr(cmdViewArchive.Caption, "Archive&quot) Then
strDatabase = "C:\NameOfArchiveDatabase.mdb"
cmdViewArchive.Caption = "Link to Live Database"
Else
strDatabase = "c:\NameofLiveDatabase.mdb"
cmdViewArchive.Caption = "Link to Archived Database"
End If

Call LinkTable(strDatabase, "All&quot

End Sub

This is the function that relinks. You pass it the path and name of the database you want to link to and the names of the tables you want to relink (separated by commas). If you want to link all of the tables, simply pass the string "All".

Function LinkTable(strLinkToDBname As String, _
ParamArray varTblName() As Variant)

'********************************
'* Declaration Specifications *
'********************************

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Dim i As Integer

Set dbs = CurrentDb

'***********************
'* Relink the tables *
'***********************

If (varTblName(0) = "All&quot Then

For Each tdf In dbs.TableDefs
If (Left$(tdf.Name, 4) <> &quot;MSys&quot And (Left$(tdf.Name, 4) <> &quot;USys&quot And (Left$(tdf.Name, 1) <> &quot;~&quot Then
tdf.Connect = &quot;;DATABASE=&quot; & strLinkToDBname
tdf.RefreshLink
End If
Next

Else

For i = 0 To UBound(varTblName)
Set tdf = dbs.TableDefs(CStr(varTblName(i)))
tdf.Connect = &quot;;DATABASE=&quot; & strLinkToDBname
tdf.RefreshLink
Next i

End If

'********************
'* Exit Procedure *
'********************

ExitProcedure:

Exit Function

'****************************
'* Error Recovery Section *
'****************************

ErrHandler:

MsgBox Err.Description, vbExclamation

Resume ExitProcedure


End Function