Removing & restoring realtionships bewteen tables during imports

[Guest]

As part of a school database, I need to use 12 linked spreadsheets each night
to update 12 tables which are linked with referential integrity. The import
works fine unless there are any new students admitted on a certain day. In
such cases, the import stops andd error message states that "You cannot add
or remove records because a related record in required in table ..."
I have tried doing the imports in a different order but this didn't help.

I can overcome this by temporarily removing the relationship between the
tables. To my surprise just removing R.I. isn't enough.

I would like to use code as the start of the import routine to remove the
relationship and then to restore it at the end with full referenial integrity
(inc. cascade update & cascade delete). Can anyone advise on this.
I am using Access 2003. The 2 problem tables are "PupilData" and
"StudentAttendance"

Thanks in advance

 

[Douglas J. Steele]

Take a look at my November, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access". I show how you can use a single query to
update existing data, or add data if it doesn't already exist.

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Douglas J. Steele]

Since the technique requires that your "new" data be in a table somewhere,
you should be able to write a query that determines what foreign keys in the
"data" table are missing, and then add them to the appropriate table(s).

My normal approach to running multiple queries is to use VBA code.

CurrentDB.QueryDefs("ActionQuery1").Execute dbFailOnError
CurrentDB.QueryDefs("ActionQuery2").Execute dbFailOnError
CurrentDB.QueryDefs("ActionQuery3").Execute dbFailOnError

The inclusion of dbFailOnError as a parameter of the Execute method means
that you can use error trapping to determine which step failed, and stop
subsequent queries from running.

 

[Guest]

Hey, I didn't know you replied. This is just what I need!
Thank you!

m-
<..^