Ben said:
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.
