Hi,
I need to test out some theories and would like to know (without copying the
db & etc) if there is a way to create more than one relationship model? Save
them as tests or theories without destroying the underlying current
relationship model.
Thank you
As far as I'm aware, Access doesn't support multiple, simultaneous
relationship models.
The easy solution would be the one you've mentioned, copy the
database. Why are you against working with multiple files for testing
purposes?
The more complex answer would be to create VBA code that creates/
deletes your relationships at the push of a button, but in a database
of any complexity, you'll spend more time creating the code than you
would probably spend manually creating/deleting the relationships
especially if you only want to change it once. If you want to flip
back and forth between the options many times, this may save you some
time.
If you'd like to go this route, here's some code to get you started.
This will create a relationship between table1 and table2 using
table1.ID1 and table2.ID2 as the linking fields.
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
Set db = CurrentDb
'Create the relationship
Set rel = db.CreateRelation("foo", "table1", "table2")
'Referential integrity with cascade update - several different
options here
rel.Attributes = dbRelationUpdateCascade
'Key field in table1
Set fld = rel.CreateField("ID1")
'Key field in table2
fld.ForeignName = "ID2"
'Add field to relation
rel.Fields.Append fld
'Add relation to database
db.Relations.Append rel
And this to delete all the relationships.
Dim db As Database
Dim rex As Relations
Dim rel As Relation
Set db = CurrentDb()
Set rex = db.Relations
Do While rex.Count > 0
rex.Delete rex(0).Name
Loop
