If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Delete table relationships?
Is there a way to determine a table relationship, gather information about
the relationship in order to recreate it later. I'm appending data to some tables, so I would like to drop the existing relationships, do the append and then re create the relationships after. Can anyone advise me on trying to accomplish this. Are there other methods of doing this, if so please enlighten me. |
#2
|
|||
|
|||
One usually does not have to delete relationships to Append to a table,
maybe you mean to run an Update... What exactly are you trying to do? "Ben Wallace (3)" wrote in message ... Is there a way to determine a table relationship, gather information about the relationship in order to recreate it later. I'm appending data to some tables, so I would like to drop the existing relationships, do the append and then re create the relationships after. Can anyone advise me on trying to accomplish this. Are there other methods of doing this, if so please enlighten me. |
#3
|
|||
|
|||
Ben Wallace (3) wrote: Is there a way to determine a table relationship, gather information about the relationship in order to recreate it later. I'm appending data to some tables, so I would like to drop the existing relationships, do the append and then re create the relationships after. Can anyone advise me on trying to accomplish this. Are there other methods of doing this, if so please enlighten me. You can extract schema information, including FOREIGN KEY information (FK is a stricter interpretation of an Access 'Relationship' because it must reference a unique key) in various ways. My preference is to use the OpenSchema method in VBA code because I get a recordset of schema information and a recordset is a nice flat object to work with (easier than traversing a hierarchical object model, for example). Here's an example using my table Payroll in my Airplanes database: Dim rsKeys As Object ' 27 = adSchemaForeignKeys Set rsKeys = CurrentProject.Connection.OpenSchema(27, _ Array(Empty, Empty, Empty, Empty, Empty, "Payroll")) Here's a peak at the information in the recordset using the Vusual Basic Editor's Immediate Window: For Each f in rsKeys.Fields : ?f.Name, f.Value : Next PK_TABLE_CATALOG Null PK_TABLE_SCHEMA Null PK_TABLE_NAME Pilots PK_COLUMN_NAME pilot_ID PK_COLUMN_GUID Null PK_COLUMN_PROPID Null FK_TABLE_CATALOG Null FK_TABLE_SCHEMA Null FK_TABLE_NAME Payroll FK_COLUMN_NAME pilot_ID FK_COLUMN_GUID Null FK_COLUMN_PROPID Null ORDINAL 1 UPDATE_RULE CASCADE DELETE_RULE CASCADE PK_NAME pk__pilots FK_NAME fk__payroll__pilots DEFERRABILITY Null If you didn't explicitly choose a name for your schema objects you may not have names as intelligent (meaningful) as 'pk__pilots', instead you would have a system-assigned name such as 'Rel_7B67BA8F_D7D7_48B0'. One thing the FK schema information doesn't reveal, which is required to recreate the object, is the columns in the PK (PRIMARY KEY) table ['PK' is a bit misleading here because the FK may not be based on a PRIMARY KEY!] Schema information about PKs and other unique keys may be extracted using OpenSchema with different parameters. Which leads me to another point ... .... I have a much easier way of extracting this schema information: I just look at my SQL DDL (data definition language) script: for my database: CREATE TABLE Pilots ( pilot_ID CHAR(10) NOT NULL CONSTRAINT pk__pilots PRIMARY KEY, .... CREATE TABLE Payroll ( pilot_ID CHAR(10) NOT NULL CONSTRAINT fk__payroll__pilots REFERENCES Pilots (pilot_ID) ON DELETE CASCADE ON UPDATE CASCADE, .... Creating the tables using SQL code means I am in control of what constraints and indexes are created and I have chosen intelligent names for them. Re-creating these tables or just the constraints is easy because it was planned properly. I agree with the other respondent that it may be best to leave the constraint (if that's what it is) in place and see which rows fail when you insert the new data. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help again from Ken Snell (Query) | Randy | Running & Setting Up Queries | 22 | August 29th, 2005 08:15 PM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
Manual line break spaces on TOC or Table of tables | Eric | Page Layout | 9 | October 29th, 2004 04:42 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |