modifying relationships using DAO?

[Rico]

Hello,

I have a relationship that I'd like to modify using DAO. Right now the
Cascading updates are off, but I'd like to add them and I'm not sure the
best way to do that.

Any ideas?

Thanks!

[MGFoster]

Rico wrote:

Hello,

I have a relationship that I'd like to modify using DAO. Right now the
Cascading updates are off, but I'd like to add them and I'm not sure the
best way to do that.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A relationship is really a Foreign Key constraint (FK) between 2 tables.
You can use DDL (Data Definition Language) to define the FK. If the
relationship is already in force you'll have to drop it before you can
assign the new relationship, like this:

ALTER TABLE <foreign key table> DROP CONSTRAINT <constraint name>

If you created the relationship using the Relationship window the
constraint's name will be an internally produced serial number, or a
human readable name. This name is stored in the table
MSysRelationships, column szRelationship.

To create the new constraint use a statement like this:

ALTER TABLE <foreign key table> ADD CONSTRAINT FK_table_name
FOREIGN KEY (<fk column list>)
REFERENCES <primary key table> (<pk column list>)
ON UPDATE CASCADE

The <foreign key table> is the table on the the "many" side of the
relationship. The <primary key table> is the table on the "one" side of
the relationship. The <fk column list> is a comma-separated list of
columns that are in the foreign key table that relate to the <pk column
list> in the primary key table.

An example:

ALTER TABLE OrderDetails ADD CONSTRAINT FK_OrderDetails
FOREIGN KEY (invoice_nbr, order_nbr)
REFERENCES Orders (invoice_nbr, order_nbr)
ON UPDATE CASCADE

The column list for each table must be in the same order. IOW, the
OrderDetails.invoice_nbr relates to the Orders.invoice_nbr and the
OrderDetails.order_nbr relates to the Orders.order_nbr.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRASmKIechKqOuFEgEQKdWQCgpfm8Ekmtsscv/w7DH2VBzWRhQooAn2P0
1hSwVVIRkfZztqWvUMuRb3fO
=sWa9
-----END PGP SIGNATURE-----

[Lyle Fairfield]

TTBOMK one must use an ADODB connection to deal with Updates and
Constraints as in:

(dropping the existing constraint)

CurrentProject.Connection.Execute _
"ALTER TABLE [Order Details] " _
& "DROP CONSTRAINT " _
& "OrderParent "

(and rebuilding it with the OnUpdate or OnCascade attributes set)

CurrentProject.Connection.Execute _
"ALTER TABLE [Order Details] " _
& "ADD CONSTRAINT " _
& "OrderParent " _
& "FOREIGN KEY (OrderID) " _
& "REFERENCES Orders (OrderID) " _
& " ON UPDATE CASCADE " _
& " ON DELETE CASCADE"

[MGFoster]

You can also use DAO. The DDL statements run just the same as a DAO
QueryDef.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Lyle Fairfield wrote:

TTBOMK one must use an ADODB connection to deal with Updates and
Constraints as in:

(dropping the existing constraint)

CurrentProject.Connection.Execute _
"ALTER TABLE [Order Details] " _
& "DROP CONSTRAINT " _
& "OrderParent "

(and rebuilding it with the OnUpdate or OnCascade attributes set)

CurrentProject.Connection.Execute _
"ALTER TABLE [Order Details] " _
& "ADD CONSTRAINT " _
& "OrderParent " _
& "FOREIGN KEY (OrderID) " _
& "REFERENCES Orders (OrderID) " _
& " ON UPDATE CASCADE " _
& " ON DELETE CASCADE"

[Lyle Fairfield]

In Access 2003 this

DBEngine(0)(0).Execute _
"ALTER TABLE [Order Details] " _
& "ADD CONSTRAINT " _
& "OrderParent " _
& "FOREIGN KEY (OrderID) " _
& "REFERENCES Orders (OrderID) " _
& " ON UPDATE CASCADE " _
& " ON DELETE CASCADE"

gives me a Syntax Error in the Constraint Clause.

I note this:

Foreign keys

When dealing with foreign keys, the concept of a fast foreign key may
be useful. A fast foreign key is a foreign key that has no index.
Although this may seem counter-intuitive, there is a valid explanation
for it. By default, when a foreign key is defined, an index based on
the column(s) in the foreign key is created automatically. In many
instances this enhances performance when executing operations that
maintain referential integrity. However, if there are many duplicated
values in the foreign key field, the foreign key index will affect
performance when records are added and deleted from the table. To
prevent the automatic creation of indexes on foreign keys, use the NO
INDEX keywords in the declaration of the foreign key.

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_tblInvoices
FOREIGN KEY NO INDEX (CustomerID) REFERENCES
tblCustomers (CustomerID)
ON UPDATE CASCADE
ON DELETE CASCADE

Note The fast foreign key statement can only be executed through
the Jet OLE DB provider and ADO. It will return an error message if
used through the Access SQL View user interface. Also note that to drop
a fast foreign key, you must issue the DROP CONSTRAINT statement
through the Jet OLE DB provider and ADO.

Another example of a situation where a fast foreign key would be useful
is in an order entry database application. Assume that there is a table
called CustomerTypes that identifies what type of customers are being
tracked, a Customer table, and an Orders table. Assume that there are
10 rows in the CustomerTypes table, 100,000 rows in the Customer table,
and 350,000 rows in the Orders table. A good choice for the Customers
table would be a fast foreign key that references the primary key in
the CustomerTypes table. This is because there is a maximum of 10
unique values out of 100,000 rows. An index here has little value for
retrieving data and would be a drag on concurrency and inserts,
deletions, and updates in the CustomerType column.

On the other hand, the fast foreign key would probably not be useful
when applied to the CustomerID column in the Orders table, because
those values are likely to be unique, since each represents a different
customer. In this instance having the foreign key indexed in the
regular manner is very advantageous because it is used in joins and
other lookup criteria.
---
As ADO always works, I always use it.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Didn't know about the Fast Foreign Key. Is that a new feature in
Acc2003? Where is documentation?

In Acc2002, I usually have the ANSI 92 option set. When it is set, the
ALTER TABLE w/ ON UPDATE (or DELETE) CASCADE works thru DAO. That's why
I posted that DAO works w/ my previous posts. Have to take that into
consideration in future.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAUfUYechKqOuFEgEQILlgCfYnRewUabHL/in33O7+B3qNQjhnEAn1EK
mNreQVy3l2lNvqfadsY97r9a
=AbLc
-----END PGP SIGNATURE-----

Lyle Fairfield wrote:

In Access 2003 this

DBEngine(0)(0).Execute _
"ALTER TABLE [Order Details] " _
& "ADD CONSTRAINT " _
& "OrderParent " _
& "FOREIGN KEY (OrderID) " _
& "REFERENCES Orders (OrderID) " _
& " ON UPDATE CASCADE " _
& " ON DELETE CASCADE"

gives me a Syntax Error in the Constraint Clause.

I note this:

Foreign keys

When dealing with foreign keys, the concept of a fast foreign key may
be useful. A fast foreign key is a foreign key that has no index.
Although this may seem counter-intuitive, there is a valid explanation
for it. By default, when a foreign key is defined, an index based on
the column(s) in the foreign key is created automatically. In many
instances this enhances performance when executing operations that
maintain referential integrity. However, if there are many duplicated
values in the foreign key field, the foreign key index will affect
performance when records are added and deleted from the table. To
prevent the automatic creation of indexes on foreign keys, use the NO
INDEX keywords in the declaration of the foreign key.

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_tblInvoices
FOREIGN KEY NO INDEX (CustomerID) REFERENCES
tblCustomers (CustomerID)
ON UPDATE CASCADE
ON DELETE CASCADE

Note The fast foreign key statement can only be executed through
the Jet OLE DB provider and ADO. It will return an error message if
used through the Access SQL View user interface. Also note that to drop
a fast foreign key, you must issue the DROP CONSTRAINT statement
through the Jet OLE DB provider and ADO.

Another example of a situation where a fast foreign key would be useful
is in an order entry database application. Assume that there is a table
called CustomerTypes that identifies what type of customers are being
tracked, a Customer table, and an Orders table. Assume that there are
10 rows in the CustomerTypes table, 100,000 rows in the Customer table,
and 350,000 rows in the Orders table. A good choice for the Customers
table would be a fast foreign key that references the primary key in
the CustomerTypes table. This is because there is a maximum of 10
unique values out of 100,000 rows. An index here has little value for
retrieving data and would be a drag on concurrency and inserts,
deletions, and updates in the CustomerType column.

On the other hand, the fast foreign key would probably not be useful
when applied to the CustomerID column in the Orders table, because
those values are likely to be unique, since each represents a different
customer. In this instance having the foreign key indexed in the
regular manner is very advantageous because it is used in joins and
other lookup criteria.
---
As ADO always works, I always use it.