Clear down a linked table and then re-import (1 Viewer)

[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?

 

[Pat Hartman]

If it is the 1-side table that you need to clear, you have to remove the relationship first. You can run a DDL query to clear it. Then after you delete and reimport the data, you can run another DDL query to put it back.

You will probably have to delete the existing relationships first so you can give them meaningful names. Use the MSysRelationships table to view the existing relationships.

 

[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

 

[Pat Hartman]

DML = Data Manipulation Language (the SQL we know and love)
DDL = Data Definition Language (manipulate structure)
The third type manipulates permissions.

DDL comes in extremly handy when you are working on a conversion. Somehow they always need to be run multiple times and DDL queries to drop and create constraints make the whole thing easier to do.

 

[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

 

[Pat Hartman]

Since the tables are always replaced and not updated by user input, there is no reason to create a relationship and enforce RI. The tables also seem to no longer have related records. You might need to change your queries to use left or right joins or you will loose records when you run queries.