linking tables to external ones?

[Howard]

My database links to two tables in another access database and also to
two excel spreadsheets. At work these and the 'main' database are all in
the same folder with other stuff. However I often put this entire folder
on a memory stick and/or copy it to my laptop to work on.

Is there a way I can set up the linking so that it will always look in
the folder where the 'main' database is without having to manually
re-link each thing separately? (something like application.path in VB)


Especially as the linked table wizard window is too small to see all of
the long pathnames in modern machines.

Howard

(cross posted also to the tables forum - apologies)

 

[Douglas J. Steele]

Here's code I have in the Load event of the first form to open in one of my
databases:

Private Sub Form_Load()
' Check that the front-end is linked to a back-end
On Error GoTo Err_Form_Load

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim strCurrLinkage As String
Dim strFrontendPath As String
Dim strExpectedBackend As String

Set dbCurr = CurrentDb()
strFrontendPath = Application.CurrentProject.Path
If Right(strFrontendPath, 1) <> "\" Then
strFrontendPath = strFrontendPath & "\"
End If
strExpectedBackend = ";Database=" & strFrontendPath & "CollectionData.mdb"

' Make the simplifying assumption that all linked tables
' will point to the same back-end
For Each tdfCurr In dbCurr.TableDefs
With tdfCurr
strCurrLinkage = .Connect
If Len(strCurrLinkage) > 0 Then
If StrComp(strCurrLinkage, strExpectedBackend, vbTextCompare) <> 0
Then
.Connect = strExpectedBackend
.RefreshLink
End If
End If
End With
Next tdfCurr

End_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Number & ": " & Err.Description
Resume End_Form_Load

End Sub

Now, you wouldn't be able to make the simplifying assumption I made, but you
can modify that section a little to allow you to reset different tables to
point to different locations.

 

[Howard]

Thank you Douglas,
I think I can see how to add my excel sheets into that as well.
It will save some time in the long run.
Howard