Transferring Relationships From One DB To Another 2

[Ed2020]

Hi,

I want to use the code below to read the relationships in one database and recreate them in another database.

Everything appears to be fine until it reaches the ThisDB.Relations.Append NewRel line, at which point I get an error message "You do not have the necessary permissions to use the '<tablename>' object...".

If anyone could spot what I'm doing wrong it would be much appreciated!

Public Sub TransferAllRelationships(ByVal strDB_Path As String)
    Dim OtherDB As DAO.Database
    Dim ExtRel As DAO.Relation
    Dim ThisDB As DAO.Database
    Dim NewRel As DAO.Relation
    Dim pntr As Integer
    Dim MyField As DAO.Field
    
    Set OtherDB = OpenDatabase(strDB_Path, , True)
    Set ThisDB = CurrentDb()
    
    For Each ExtRel In OtherDB.Relations
        Set NewRel = ThisDB.CreateRelation(ExtRel.Name, ExtRel.Table, ExtRel.ForeignTable, ExtRel.Attributes)
        For pntr = 0 To ExtRel.Fields.Count - 1
            NewRel.Fields.Append NewRel.CreateField(ExtRel.Fields(pntr).Name)
            NewRel.Fields(pntr).ForeignName = ExtRel.Fields(pntr).ForeignName
        Next pntr
        ThisDB.Relations.Append NewRel 'Fails here - insufficient permissions on table?!
    Next ExtRel
End Sub

TIA,

Ed Metcalfe.

Please do not feed the trolls.....

 

[jedraw]

Ed,

I got this to work as you have it. The only issue I had was that any tables in the relationships had to be imported first.

I got errors saying the Table could not be found. But I did not get any permissions errors.

 

[CautionMP]

Ed2020,
Is it possible you were trying to change the relationships while someone else was accessing the database?

Try [tt]Set OtherDB = OpenDatabase(strDB_Path, True, True)[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Ed2020]

Thanks for your replies.

CautionMP - I've tried the amendment you suggested and I am now getting the permissions error on the line "Set OtherDB = OpenDatabase(strDB_Path, True, True)". I'm certain that I have full permissions on the source database as I created it.

Very confused!

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Sorted it! I simply needed to add DBEngine(0) to the OpenDatabase line:

Public Sub TransferAllRelationships(ByVal strDB_Path As String)
    Dim OtherDB As DAO.Database
    Dim ExtRel As DAO.Relation
    Dim ThisDB As DAO.Database
    Dim NewRel As DAO.Relation
    Dim pntr As Integer
    Dim MyField As DAO.Field
    
    Set OtherDB = DBEngine(0).OpenDatabase(strDB_Path, True, True)
    Set ThisDB = CurrentDb()
    
    For Each ExtRel In OtherDB.Relations
        Set NewRel = ThisDB.CreateRelation(ExtRel.Name, ExtRel.Table, ExtRel.ForeignTable, ExtRel.Attributes)
        For pntr = 0 To ExtRel.Fields.Count - 1
            NewRel.Fields.Append NewRel.CreateField(ExtRel.Fields(pntr).Name)
            NewRel.Fields(pntr).ForeignName = ExtRel.Fields(pntr).ForeignName
        Next pntr
        ThisDB.Relations.Append NewRel 'Fails here - insufficient permissions on table?!
    Next ExtRel
End Sub

The only thing I don't understand is why. I always thought that if this was omitted then the default DBEngine object was used...?

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Oh, and stars for both of you for taking the time to respond.

Ed Metcalfe.

Please do not feed the trolls.....