Trying to replicate relations from an external database

[komis]

Hi all,
I have a problem trying to replicate relations from an external database into a new file.
I have the following code, which doesn't work because it says the relation I am trying to append to the current db has no fields. If I use the .append method to append the taField object to the taRel collection, it says invalid operation, error 3219... helpppppp..... btw taRel=target relationship (to be created) and imRel=imported relationship (resides in external mdb).

Private Sub createRels()

Dim imRel As DAO.Relation
Dim taRel As DAO.Relation
Dim strpath As String
Dim imdb As DAO.Database
Dim imField As DAO.Field
Dim taField As DAO.Field
Dim x As Integer
Dim i As Integer

'get the external mdb path
Text24.SetFocus
strpath = Text24.text & "\" & List22.Value

Set imdb = DBEngine.Workspaces(0).OpenDatabase(strpath)

For i = 0 To imdb.Relations.count - 1

Set imRel = imdb.Relations(i)
Set taRel = CurrentDb.CreateRelation(imRel.name, imRel.Table, imRel.ForeignTable, imRel.Attributes)


For x = 0 To imRel.Fields.count - 1
MsgBox "field " & x & " in imrel"

Set imField = imRel.Fields(x)
Set taField = taRel.CreateField(imField.name)

MsgBox "fields now in tarel " & taRel.Fields.count
taField.ForeignName = imField.ForeignName
'if I include the following line, it says invalid operation, if I don't it says cant append a relationship without fields... grrrrrrrr....
'taRel.Fields.Append (taField)
Next x

CurrentDb.Relations.Append taRel

Next i

CurrentDb.Relations.Refresh
End Sub

 

[komis]

Hi and thanks for the reply.

I have another routine which does just that, i.e. deletes all tables in the current db and imports all of the tables from the external database.

So, I imported the tables first, now I need to import the relationships too. But my code doesn't seem to work, although I really can't see what might be preventing it from working. Is the Relations collection read-only or something? If so, is there a way to make it writeable? I can't really see how such a thing might be the cause of the problem but then again I really can't think of anything else....

 

[komis]

Hi,
Thanks, I've solved it. Here's the code I used (based on a microsoft example) :


Private Sub createRels(strpath As String)

Dim imRel As DAO.Relation
Dim taRel As DAO.Relation
Dim imdb As DAO.Database
Dim imField As DAO.Field
Dim taName As String
Dim taTname As String
Dim taFTname As String
Dim taAtt As Variant
Dim fName As String
Dim fFName As String


Set imdb = DBEngine.Workspaces(0).OpenDatabase(strpath, True)

For Each imRel In imdb.Relations
With imRel
taName = .name
taTname = .Table
taFTname = .ForeignTable
taAtt = .Attributes

Set taRel = CurrentDb.CreateRelation(taName, taTname, taFTname, taAtt)

For Each imField In .Fields
fName = imField.name
fFName = imField.ForeignName
taRel.Fields.Append taRel.CreateField(fName)
taRel.Fields(fName).ForeignName = fFName
Next

CurrentDb.Relations.Append taRel
End With

Next

End Sub