Link tables at startup

[ZX188]

I have a database with 10 linked tables in it these tables are linked to 4 different databases I need to refresh the link at start up because the drive letter can change from time to time. Is there a way to specify the path for the linked tables with a field in another table in a different database so that when the DB is moved to a different drive the path can be changed for all my linked tables by changing the path in that field
I have tried for several days with sample code from the solutions.mdb and codes from several other places but the fact that I have 4 links to different databases in one database seams to make the sample code not function correctly

Thanks in advance
Kevin

 

[petermeachem]

1) Why not use unc names and not drive letters.
2) What I do is
a) on start up, open a recordset on a table. If it opens, just run your code.
b) If it doesn't, I then do this

Set dbs = CurrentDb
csql = "SELECT * FROM AttachFileNames"
Set rs2 = dbs.OpenRecordset(csql)
Do Until rs2.EOF
If Not IsNull(rs2("AttachFileName&quot) Then
Call AttachTbl(rs2("AttachFileName&quot, cDataBasePath)
End If
rs2.MoveNext
Loop
rs2.Close
Set dbs = Nothing

where Attachtbl is

Sub AttachTbl(tblName, dbname As String)
'------------------------------------------------------------
'
' Version number :- dev
'
' Attach a table to a database
'
'
'------------------------------------------------------------
Dim tbl As New TableDef
Dim db As Database
Dim ctbl As String

On Local Error Resume Next

ctbl = tblName
Call DropTables(ctbl)
tbl.NAME = tblName
tbl.SourceTableName = tblName

tbl.Connect = ";database=" & dbname & ";"
tbl.Attributes = 0

Set db = CurrentDb
db.TableDefs.Append tbl
Set db = Nothing


End Sub

and

Sub DropTables(ctbl As String)
On Local Error Resume Next
DoCmd.SetWarnings False
If InStr(ctbl, "[&quot > 0 Then
DoCmd.RunSQL "DROP TABLE " & ctbl
Else
DoCmd.RunSQL "DROP TABLE [" & ctbl & "]"
End If
DoCmd.SetWarnings True

End Sub


As you have 4 different databases, you could make cDataBasePath a field in AttachFileNames

Is that OK?