Enforcing Referential Integrity

[jim84]

Basically, I want to use this ^ .

I have a load of tables with primary key fields and then a table with just the appropriate foreign keys (we'll call this the f table). I have all the table links set up to Enforce Referential Integrity.

When I import the data i need into the f table a warning comes up.
It basically says 200 records will be "Lost" as they dont relate to the primary key tables.

Now i dont mind this as I know some of the data is bad and needs ammending ('??' or 'UNKNOWN' etc. in some fields).

THE PROBLEM i have is: Is there anyway to see this "lost" data after the import so that i can easily go through it as a block correcting the irregularities? it seems to me this "lost" data in has actually been deleted- is there anyway of retrieving it?

Ideally id like it to be able to export this "lost" data into a new table, spreadsheet (or anything else!) so i can sit down and go through it all as a block.

I know i could go through, editing the original data before importing but this will be a needle and haystack situation so i'd like to avoid at all costs!

Can anyone suggest any advice on approaching this problem?

Thanks very much!

Jim.

 

[Rabbie]

Access should create a table named Paste Errors or Import Errors or similar. This should let you see the records that failed so you can decide what to do with them

 

[jim84]

Yeh i was told this would happen but it hasn't. Is there an option somewhere i need to toggle?

Thanks.

 

[Rabbie]

It may be a hidden table. Try setting Hidden Objects on the View Tab of the Options screen.

 

[jim84]

no luck. i forgot to mention that the data is being imported from an excel spreadsheet, not sure if that'll cause issues!

 

[jim84]

ok ive tried doing it simply cut and paste and yay, its given me a paste errors table. lovely!

 

[Dennisk]

I do not think you are getting any import errors. If there was any errors then you will find a file called Import Errors.

I suspect you are importing into the 'many' side of a 'one to many' relationship and there is no corresponding record in the 'one' table hence it is a referential integrity error and not an import error.

 

[jim84]

yes Dennisk you are completely correct. I took another approach and it's got me a little further..

I copied and pasted the data into the 'many' table and it automatically created a "paste errors" table of all the dodgy records.

I then made a query of this table. I then fixed some of this data. I then did the append option on this query and the newly fixed records copied successfully into the required table (the many). The problem is they weren't removed from this query so every time I re-run the ammend it creates repeats in the 'many table'.

Does anyone know a way of deleting these successfully edited records from the query so that i can just run it after every edit without the fear of repeating data in the 'many table'?

Thanks for the help guys.