Can you "switch" referential integrity off and on?

[ryetee]

I have some VBA code that imports data for each table from an excel spreadsheet.

I am trying to do this by looping through table definitions. In this way I don't have to bother if the number of tables changes.

To import I'm running the following.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strFileNameXls, True, ""

This works for most tables but there are a number that fail because of referential integrity because they rely on a table already being processed. If the table they rely on is higher in the alphabet then the load will fail.

I can get round this in a number of ways.

1. Identify those tables that are failing and import them in the right order outside of the loop.
2. Rename some of the tables so they appear in the "correct" alphabetical order.
3. Run through the loop repeatedly until all the imports have been successfully imported - so set up some array with tablenames in keeping track in whether or not the import has worked. If all tables haven't been implemented go through loop again and process only those that have failed.
4. Switch off referential integrity. Loop through the table defs. Switch on data integrity.

Pros and cons are
1. This detracts from the automation I'm after and adding in extra tables could mean extra analysis to where it belongs - ie code needed in the loop not to process and outside of the loop to process.
2. Yeah right
3. Beginning to like this idea if turning referential integrity is a) not possible b) more trouble than it's worth or c) an absolute no no under any circumstance WHATEVER! Besides I can code this now (once I've revised array processing)
4. Don't know if it's possible and if so potentially dangerous

 

[James Deckert]

You can turn off ref integrity, do the import then turn back on. However, you may need to fix some records which don't meet the requirements for RI before you can turn it back on.
The only danger would be if someone else was accessing the data also and deleted a parent record (or something which doesn't follow RI rules).

I'd always make a backup of data prior to doing this.

 

[Ranman256]

you should never have to.

 

[ryetee]

You can turn off ref integrity, do the import then turn back on. However, you may need to fix some records which don't meet the requirements for RI before you can turn it back on.
The only danger would be if someone else was accessing the data also and deleted a parent record (or something which doesn't follow RI rules).

I'd always make a backup of data prior to doing this.

Thanks for the prompt reply!

The imported data has RI so there should never be a problem. The data imported is for a sole user so no one else should be using it.
How do I turn RI off and back n again. I've googled this a bit and not really seen a solution.

 

[ryetee]

you should never have to.

All though I can possibly code round it, it's a pain (using 2 dimensional dynamic arrays) so it would be easier (potentially) if I can switch it off and on.

 

[James Deckert]

Go into Database Tools>Relationships. Right click a join line Edit Relationships. uncheck Enforce RI

 

[Galaxiom]

I can't really understand why it would be needed to be automated but you would Append or Delete items from the Relations Collection.

Here is an example of code.

 

[ryetee]

Go into Database Tools>Relationships. Right click a join line Edit Relationships. uncheck Enforce RI

I want to do this in VBA

 

[ryetee]

I can't really understand why it would be needed to be automated but you would Append or Delete items from the Relations Collection.

Here is an example of code.

So I or anyone else doesn't have to amend the code even if new tables are added.

 

[Cronk]

I'd caution against modifying table design to import data, remove RI problems and then modifying the tables back to include the original design.

IMO, a better approach is to import spreadsheet data in to temporary tables, clean the data, then move the data from the temporary tables in to the required tables.

 

[Galaxiom]

Sorry I only gave you half an answer.

You will need to loop through the Relations Collection and change the Attribute Property of each relation according to the enumerations on this page.

Use them like this:
Don't Enforce Referential Integrity

yourRelation.Attributes = dbRelationDontEnforce

Enforce Referential Integrity (Right relation)

yourRelation.Attributes = dbRelationRight

The Attribute Enumerations can be added together. For example:

yourRelation.Attributes = dbRelationRight + dbRelationUpdateCascade + dbRelationDeleteCascade

 

[Galaxiom]

IMO, a better approach is to import spreadsheet data in to temporary tables, clean the data, then move the data from the temporary tables in to the required tables.

I would agree with this.

Once the data is in the temp tables you can append the data in the sequence of tables that won't conflict with the Referential Integrity.

 

[ryetee]

I'd caution against modifying table design to import data, remove RI problems and then modifying the tables back to include the original design.

IMO, a better approach is to import spreadsheet data in to temporary tables, clean the data, then move the data from the temporary tables in to the required tables.

I wanted to do this without having to know table names so that any additional table wouldn't need any programming considerations. If my tables were named alphabetically in the order they need to be loaded there wouldn't be a problem. I could just loop around the table definitions and hey presto. As that isn't the case I'm having to hard code a handful of tables. Any new tables may also have to be hard coded.

 

[ryetee]

Sorry I only gave you half an answer.

You will need to loop through the Relations Collection and change the Attribute Property of each relation according to the enumerations on this page.

Use them like this:
Don't Enforce Referential Integrity

yourRelation.Attributes = dbRelationDontEnforce

Enforce Referential Integrity (Right relation)

yourRelation.Attributes = dbRelationRight

The Attribute Enumerations can be added together. For example:

yourRelation.Attributes = dbRelationRight + dbRelationUpdateCascade + dbRelationDeleteCascade

Thanks for your help. I'll take a longer look when my head stops hurting!

 

[ryetee]

I would agree with this.

Once the data is in the temp tables you can append the data in the sequence of tables that won't conflict with the Referential Integrity.

I have something that works at the moment but will look into this if it falls apart in proper testing!

 

[Cronk]

Actually, I'd say you don't have something "that works at the moment" because you are looking to turn off RI.

In solving this, you have the choice of selecting something that is in my opinion, an inferior option. But I fully accept it's your choice. Good luck.

 

[ryetee]

Actually, I'd say you don't have something "that works at the moment" because you are looking to turn off RI.

In solving this, you have the choice of selecting something that is in my opinion, an inferior option. But I fully accept it's your choice. Good luck.

I do - without turning off RI!! Just need to hard code some table names. Testing looks ok too but needs a lot more, testing that is.

I wasn't sure what you meant by clean the data in your last post.
My data is clean.
Problem was I had something like

AtablebeginningwithA
JobtablebeginningwithJ

The table beginning with A was dependent on data from table beginning with J being there. In my looping through the table definitions it was processed first so the data from the table beginning with J wasn't there and the import fails for first table.

If I were to name JobtablebeginningwithJ to AAAJobtablebeginningwithAAA then this would work.

In the end I did this - within the loop, if table = AtablebeginningwithA then don't process. Outside of the loop I then hardcoded the import for AtablebeginningwithA.

This is the method I now have.
All I wanted was not to have to hard code anything. I thought by switching RI off and on would work but it looks too messy.

 

[James Deckert]

I wonder if you could recursively traverse your relationships and import the parents before the children. Just don't ask me for the code.

 

[ryetee]

I wonder if you could recursively traverse your relationships and import the parents before the children. Just don't ask me for the code.

I thought about processing through the loop and storing the table and whether or not the import had worked in an array and then reprocessing until all tables had been processed.

2 problems with that. One was identifying and trapping the error; the other was that after the first ever import, I actually expect some tables to fail - this makes the reprocessing harder so decided to "hard" code ones that had parent/child.