Link table with a relative path

[pnovreske]

I am dealing with a legacy VB6 app that has an Access backend. The db contains a linked table. The primary db file are each in their own directory. For example

C:\My Location\ABC\db_abc.mdb
C:\My Location\XYZ\db_xyz.mdb

db_abc.mdb links to a table in db_xyz.mdb.

Is there a way to have Access to store the relative path?

I have tried to programmatically edit the field (MSysObjects.Database) that contains the location of the database that contains the table that is linked to. I am able to open a connection, create the required recordset but I have not succeeded in updating the record.

I receive the following error message: "Operation must use an updateable query". In the db I have gone to Tools | Security | User and Group Permissions and set the permissions for MSysObjects to allow read/write etc.

Any suggestions or other options for changing the value of the location of the linked table in the MSysObjects table? Is there a way of having Access store the link path as a relative path? Any other suggestions?

 

[dhookom]

There is code at

http://www.mvps.org/access/tables/tbl0009.htm

describing how to change the location of the linked mdb. You could modify the code to

   strNewPath = currentproject.Path

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pnovreske]

Thanks for your response.

My .mdb file is exclusively a back end for my app and is never opened via Access. Therefore any code to manipulate the db needs to reside in the VB app. If this were an "Access" app, I would have a number of options, including the one you referenced.

Any suggestions or other options for changing the value of the location of the linked table in the MSysObjects table? Is there a way of having Access store the link path as a relative path? Any other suggestions?

Thanks.

 

[PHV]

any code to manipulate the db needs to reside in the VB app
And you don't know how to instantiate DAO objects in your VB app ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

MSysObjects is read only and you can't make direct modifications to it in Access or VB. You need to write VB code something like

Public Sub ReLinkTables(db As DAO.Database, TableName As String, Connect As String)

Dim tbl                         As DAO.TableDef
On Error Resume Next
Err.Clear
Set tbl = db.TableDefs(TableName)
If Err.Number = 0 Then

    If (tbl.Attributes And dbAttachedTable) Then
        If tbl.Connect <> Connect Then
            tbl.Connect = Connect
            tbl.RefreshLink
        End If
    End If
Else
    Set tbl = New DAO.TableDef
    tbl.Name = TableName
    tbl.Connect = NewConnect
    db.TableDefs.Append tbl
End If

End Sub

and call it with something like

ReLinkTables myDatabase, "ForeignTable", ";DATABASE=C:\Data\myDatabase.mdb"

 

[pnovreske]

Thanks Golom.

After some minor modifications to deal with passwords on both dbs and unknown file locations until runtime, the code provided did the trick.

Here is the final code.

Private Sub UpdateMSysObjectsPath()
        
Dim db As DAO.Database
Dim tbl As DAO.TableDef
On Error Resume Next
Err.Clear
    
    Set db = DBEngine(0).OpenDatabase(gstrFilePathEFC, False, False, gstrConnectEFC)
    Set tbl = db.TableDefs("tblColleges")
    If Err.Number = 0 Then
    
    If (tbl.Attributes And dbAttachedTable) Then
        If tbl.Connect <> "AnyKludgeValue" Then
            tbl.Connect = gstrConnectColleges
            tbl.RefreshLink
        End If
    End If
    Else
    Set tbl = New DAO.TableDef
    tbl.Name = "tblColleges"
    tbl.Connect = gstrConnectColleges
    db.TableDefs.Append tbl
    End If
                       
End Sub