Relative Table Links

[sequelman]

I have an application that uses front end and back end databases. The front end database contains links to the back end db's tables.

The front end is contained in \..\application.
The back end is contained in \..\appliaction\bedb.

Is there a way to make 'relative' links from my Front end DB to the Back end DB. I'm not sure what drive letter the eventual user will be using. I do know, however, that the back end database is always a subdirectory below the front end database.

Any suggestions would be greatly appreciated.

 

[RickSpr]

You can't get Access to use relative paths in linked tables. Instead, you'll have to relink the tables when the database is first opened. To do this, use CurrentDir() to get the current directory, and append your subdirectory to the path. Then use this value to build a new Connect string for each linked table's TableDef, and do a RefreshLink on the TableDef.

RefreshLink changes are permanent, so you really only have to do this the first time the user opens the database. I usually just try to access a back end table at startup. If it succeeds, fine; if not, I prompt the user for the back end path.

I think the Northwind or the Solutions sample database contains some code you can adapt for this purpose.

Warning: RefreshLink requires Modify Design permission on the link (not on the back end table). If you're using user-level security, and the user doesn't have Modify Design, the RefreshLink will cause a trappable error.

 

[hennep]

Could this be of help ?

Public Sub AttachLocalDatabase(strDataFile As String)
';(re)attach the tables of the current database to the same location as the current database
Dim strName As String
Dim intPosition As Integer
strName = CurrentDb().Name
intPosition = ReverseInstr(strName, "\&quot
RefreshAttachments Left$(strName, intPosition) & strDataFile, strDataFile
End Sub
Private Function ReverseInstr(strSource As String, strPattern As String) As Integer
Dim intLength, intPos, intStart As Integer
On Error Resume Next
intLength = Len(strSource)
intStart = intLength - Len(strPattern)
intPos = 0
Do While intPos = 0 And intStart > 0
intPos = InStr(intStart, strSource, strPattern)
intStart = intStart - 1
Loop
ReverseInstr = IIf(IsNull(intPos), 0, intPos)
End Function
Private Function RefreshAttachments(strDbPath As String, strDb As String) As Integer
'; RefreshAttachments returns true if all attachments have been succesfully refreshed
On Error GoTo RefreshAttachments_Err
Dim Ws As Workspace
Dim Db As Database
Dim i As Integer
Dim J As Integer

RefreshAttachments = True
Set Ws = DBEngine.Workspaces(0)
For J = 0 To Ws.Databases.Count - 1
Set Db = Ws.Databases(J)
For i = 0 To Db.TableDefs.Count - 1
If Len(Db.TableDefs(i).Connect) > 0 Then
If ysnForceRefresh Then
If InStr(Db.TableDefs(i).Connect, strDb) > 0 Then
Db.TableDefs(i).Connect = ";DATABASE=" & strDbPath
Db.TableDefs(i).RefreshLink
Db.TableDefs.Refresh
End If
Else
If InStr(Db.TableDefs(i).Connect, strDb) > 0 Then
If Db.TableDefs(i).Connect <> &quot;;DATABASE=&quot; &amp; strDbPath Then
Db.TableDefs(i).Connect = &quot;;DATABASE=&quot; &amp; strDbPath
Db.TableDefs(i).RefreshLink
Db.TableDefs.Refresh
End If
End If
End If
End If
Next i
Next J
Db.Close
Set Db = Nothing
Set Ws = Nothing
Exit Function

RefreshAttachments_Err:
RefreshAttachments = False
Debug.Print &quot;RefreshAttachments: &quot; &amp; Err &amp; &quot;, &quot; &amp; Error
Resume Next

End Function