Temporarily disable relationships/constraints

Hi,

I am trying to do append queries from many tables in database 'A' to tables
in database 'B' through vba code.

The problem is that there are relationships/constraints on the tables. I
could write code to make the tables append in the correct order so that the
'One' in 'One-to-many' would exist before the 'Many'. But this would require
a lot of hard coding, which is not practical.

Is there a way to temporarily turn off the relationships/constraints?

According to this website, you can do this sort of thing in SQL Server:

ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL
http://stackoverflow.com/questions/7...ts-temporarily

Is this possible in access? Or is there something else I should be doing?

-Ben

As far as I know, it is NOT possible to do this in Access. You need to do the
appends in order.

You could write VBA code to remove all the relationships and then
restore/rebuild them after the update is finished. Of course, the danger is
that if you did add a record or records that did not comply with the
relationship rule, you would generate an error when you tried to rebuild the
relationship.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ben8765 wrote:
Hi,

I am trying to do append queries from many tables in database 'A' to tables
in database 'B' through vba code.

The problem is that there are relationships/constraints on the tables. I
could write code to make the tables append in the correct order so that the
'One' in 'One-to-many' would exist before the 'Many'. But this would require
a lot of hard coding, which is not practical.

Is there a way to temporarily turn off the relationships/constraints?

According to this website, you can do this sort of thing in SQL Server:

ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL
http://stackoverflow.com/questions/7...ts-temporarily

Is this possible in access? Or is there something else I should be doing?

-Ben