Removing & restoring realtionships bewteen tables during imports

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Back
Top