Clear down a linked table and then re-import

[andy_dyer]

Hi,

I have two tables linked with a 1-2-1 relation with enforced referential integrity based on a input ID field.

I originally had autonumber field which was the primary key on them both but then wasn't able to update any of the fields. I then deleted the autonumber fields and made the manual ID fields my primary key which fixed the ability to input.

But I do need to clear one the tables down and import fresh from excel and now the relationship is so strong it won't let me delete...

Is there a better way of doing this or is there a way to delete the relationship, clear down the table, import and then rebuild the relationship?

 

[andy_dyer]

That's perfect Pat many thanks - I'd never heard of DDL queries but I took your example and implemented it and it works a treat!

Thank you so much for your response

 

[andy_dyer]

Thanks for your help Pat before but if you and/or anyone can help me some more of this I would be grateful...

My challenge is that my two tables

tblImport
tblStatic

tblImport is the one on a daily basis I will want to clear down and reimport and this is working ok - I can delete the relationship and import fine...

My challenge is when it comes to re-establishing the relationship...

Because tblImport now includes more records than tblStatic it will not allow the re-establishment...

i.e. previously both tables had 650 records and i could delete, reimport the same data and re-establish relationships fine - now one table has 750 records and it won't let me re-establish the referential integrity relationship I need...

Ideally i need a way of as part of the import creating a mirror of the record numbers with the DefectID field copied across for the new records... then when the import is complete and my users go into the database then can ebegin to populate these new records with the static info that will remain in the database only.

Does any of that make sense or is even possible?

 

[andy_dyer]

Hi - I've found a way round this although someone may still come up with a 'slicker' solution...

I created a find unmatched query on the main ID field which forms the basis of the relationship and found the new ID's - I then created an append query which added records to the tblStatic with those ID's in them and then the relationship can be recreated...

Hope that helps anyone else with a similar problem