Hi Guys!



Does anyone know how to programmatically delete an existing relationship 

and then re-create it., I'm importing a table (on a weekly basis) that is 

part of an existing relationship and so, need to delete the relationship 

first, import the table, add a primary key to it and recreate the 

relationship (a one-to-many).



This is all meant to be automated behind a command button.  The 

importation spec is fine. It's the programmatic deletion and re-creation 

of the relationship that's my problem at the moment.



Any advice on how to go about this?



Amy.

Can you import the table to a temp table then append the data to the

existing table?  This would preclude what you are asking to do?





John Ruff - The Eternal Optimist :-) 







-----Original Message-----

From: Anonymous

Sent: Monday, October 29, 2001 12:47 AM

To: Access

Subject: [access] Deleting and recreating relationships





Hi Guys!



Does anyone know how to programmatically delete an existing relationship

and then re-create it., I'm importing a table (on a weekly basis) that

is

part of an existing relationship and so, need to delete the relationship

first, import the table, add a primary key to it and recreate the

relationship (a one-to-many).



This is all meant to be automated behind a command button.  The

importation spec is fine. It's the programmatic deletion and re-creation

of the relationship that's my problem at the moment.



Any advice on how to go about this?

DAO's Database object has a collection of Relation objects that you can use

to programmatically add and delete table relationships.



One other option that may work for you is to engineer your import routine so

that it imports data into tables in an order that won't run afoul of your RI

constraints.  That is, first import into Parent, then into Child, then into

GrandChild, etc.



I don't know if there's an analogue in ADO.  Look in ADOX and JRO for that

as well...



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From:Anonymous

Sent: Sunday, October 28, 2001 4:47 PM

To: Access

Subject: [access] Deleting and recreating relationships





Hi Guys!



Does anyone know how to programmatically delete an existing relationship 

and then re-create it., I'm importing a table (on a weekly basis) that is 

part of an existing relationship and so, need to delete the relationship 

first, import the table, add a primary key to it and recreate the 

relationship (a one-to-many).



This is all meant to be automated behind a command button.  The 

importation spec is fine. It's the programmatic deletion and re-creation 

of the relationship that's my problem at the moment.



Any advice on how to go about this?

Deleting and re-creating relationships can certainly be achieved with ADOX

using the Columns, Keys and Indexes collections. However, whether you use

ADO, DAO or populating existing tables (which is probably the easiest

solution), you are still left with the potential problems of dealing with

orphaned records, if they occur.



Perhaps you could let us know which table(s) in a Parent, Child, Grandchild

etc set of relationships you wish to import into. Also, how you wish to

synchronise your data between what you are importing and what exists.



Maybe then we could make some more concrete suggestions.





Ian Ashton





-----Original Message-----

From: Pardee, Roy E [mailto:roy.e.pardee@l...]

Sent: Monday, October 29, 2001 3:46 PM

To: Access

Subject: [access] RE: Deleting and recreating relationships





DAO's Database object has a collection of Relation objects that you can use

to programmatically add and delete table relationships.



One other option that may work for you is to engineer your import routine so

that it imports data into tables in an order that won't run afoul of your RI

constraints.  That is, first import into Parent, then into Child, then into

GrandChild, etc.



I don't know if there's an analogue in ADO.  Look in ADOX and JRO for that

as well...



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Anonymous

Sent: Sunday, October 28, 2001 4:47 PM

To: Access

Subject: [access] Deleting and recreating relationships





Hi Guys!



Does anyone know how to programmatically delete an existing relationship

and then re-create it., I'm importing a table (on a weekly basis) that is

part of an existing relationship and so, need to delete the relationship

first, import the table, add a primary key to it and recreate the

relationship (a one-to-many).



This is all meant to be automated behind a command button.  The

importation spec is fine. It's the programmatic deletion and re-creation

of the relationship that's my problem at the moment.



Any advice on how to go about this?