Copy Relationships to other database

[flaviooooo]

Hey,

where at the start of an access 97 to Xp-migration and I'm having some trouble here.
We have a few databases where we can test on, but one of these databases has an unholy amount of relationships.
Now it's my job to have these relationships in both 97 (as they are) and Xp.
To make things clearer: we have an 97 database, that we convert to XP. Then I need to link all the tables in XP back to the 97 tables, so that all the employees have the same data.
I tried splitting the database to back and front end; but it went Out of Range on... the relationships.

Now: is there a simple way to restore these relationships. When I link a table from 97 and rename it to the original name, it still doesn't show in the relationships-window.

If there is no easy way, I'll have to do it the hard way... all manual!!

So for the love of God, help me!

greets

 

[JeremyNYC]

I'm quite sure that the object model will give you access to these relationships through code. You ought to be able to walk through the collection...ok, so I just did about five seconds of poking in the help file and I got this far:
Sub al908()
Dim db As DAO.Database
Dim rltn As DAO.Relation

Set db = CurrentDb
For Each rltn In db.Relations
Debug.Print rltn.Name
Next rltn
End Sub

You're going to need to get a bit more info out of it than just the name, and that'll take a bit more poking than I care to do right now, but this should get you started.

Just walk the collection, putting entries in a table. Then you should be able to get that table over to your other database and write code to recreate those relationships.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[flaviooooo]

Thanks for the tips.
I'm now using following code to create a relationship

Set db = DBEngine(0)(0)
Set rel = db.CreateRelation("KLANTPMTE1", "KLANT", "PMTE", 0)
Set fld = rel.CreateField("KEYVALKLANT&quot
fld.ForeignName = "KEYVALKLANT"
rel.Fields.Append fld
db.Relations.Append rel
db.Relations.Refresh
Set db = Nothing

When I use the code on a table in my database, it works just fine.
The problem is that I need to use it with linked tables, and then it gives me an error "3057 - Operation not allowed on linked tables"
It highlights 'db.Relations.Append rel' as the culprit...
any ideas on what to do here?

 

[JeremyNYC]

Well, the relationships should be in the back end, not the front end, in any case, so you should just move your code there and you'll be all set.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[flaviooooo]

ok,

but the problem is that when I try to split the database, it keeps giving me this error 'subscript out of range' -'error occures while copying relationships'

I've tried copying all the tables to another (empty) database, with an empty msysrelationships; but in the original database are some linked tables as well, so i can't create the relationships here by code either.

The main problem seems to be that error when i'm trying to split the database...