On Cascade and other Constraints when using Alter Table

[Lester Lane]

Hi all,
I've managed to export data from an old mdb into xml, then run Excel
vba code to sort it so it will fit the new database (Access 2003), run
access vba to load the resulting spreadsheets into my new schema
(imported in from Development with no data/relationships) and all I
need to do now to "save" time is create the relationships in Access
vba code for the new database.

I have tried this:
ALTER TABLE tblAccountClient ADD CONSTRAINT relAccount_AccountClient
FOREIGN KEY (AccountID) REFERENCES tblAccount (AccountID) ON UPDATE
CASCADE

for example, both as a DoCmd.RunSQL and a Data Def query. It does not
seem to recognise the UPDATE part of ON UPDATE. I also wish to set ON
DELETE and the same happens.

Plus can anyone tell me how to specify the more bizarre joins such as
one to one and relation left?

Thanks in advance

 

[Hans Up]

I have tried this:
ALTER TABLE tblAccountClient ADD CONSTRAINT relAccount_AccountClient
FOREIGN KEY (AccountID) REFERENCES tblAccount (AccountID) ON UPDATE
CASCADE

for example, both as a DoCmd.RunSQL and a Data Def query. It does not
seem to recognise the UPDATE part of ON UPDATE. I also wish to set ON
DELETE and the same happens.

AFAIK, you need to execute your SQL statement from ADO to allow Jet to
honor ON UPDATE or ON DELETE.

Here is a sample that works on my system (Access 2003, SP3). Try it by
changing the strSql variable to hold your ALTER TABLE statement.

Public Sub addConstraint()
Dim cn As Object
Dim strSql As String
strSql = "ALTER TABLE CalendarEntries " _
& "ADD CONSTRAINT Entries_PropRef_FK " _
& "FOREIGN KEY (PropRef) " _
& "REFERENCES CurrentProperties(PropRef) " _
& "ON UPDATE CASCADE;"
Debug.Print strSql
Set cn = CurrentProject.Connection
cn.Execute strSql

Set cn = Nothing
End Sub

Plus can anyone tell me how to specify the more bizarre joins such as
one to one and relation left?

The only way I know is to use the Relationships window.

Good luck,
Hans

 

[Lester Lane]

AFAIK, you need to execute your SQL statement from ADO to allow Jet to
honor ON UPDATE or ON DELETE.

Here is a sample that works on my system (Access 2003, SP3).  Try it by
changing the strSql variable to hold your ALTER TABLE statement.

Public Sub addConstraint()
Dim cn As Object
Dim strSql As String
strSql = "ALTER TABLE CalendarEntries " _
     & "ADD CONSTRAINT Entries_PropRef_FK " _
     & "FOREIGN KEY (PropRef) " _
     & "REFERENCES CurrentProperties(PropRef) " _
     & "ON UPDATE CASCADE;"
Debug.Print strSql
Set cn = CurrentProject.Connection
cn.Execute strSql

Set cn = Nothing
End Sub


The only way I know is to use the Relationships window.

Good luck,
Hans

Thanks Hans,
I would be trying this if it wasn't for the 1-1 etc joins I need to
do. I have just recently stumbled on the following in my MS Help:
"Note The Microsoft Jet database engine does not support the use of
CONSTRAINT, or any of the data definition language (DDL) statements,
with non-Microsoft Jet databases. Use the DAO Create methods instead."

This is about using the CONSTRAINT Clause and I would think it means
you CAN use it purely with Access tables/databases. It states it
accepts the ON DELETE etc clauses. BUT I looked at the DAO Create
method and this is what it had at the top:
"Creates a new Relation object (Microsoft Jet workspaces only)." I
give up!

So now I am playing with CreateRelation method and it looks
promising. Thanks for your help though.

 

[Hans Up]

I would be trying this if it wasn't for the 1-1 etc joins I need to
do. I have just recently stumbled on the following in my MS Help:
"Note The Microsoft Jet database engine does not support the use of
CONSTRAINT, or any of the data definition language (DDL) statements,
with non-Microsoft Jet databases. Use the DAO Create methods instead."

This is about using the CONSTRAINT Clause and I would think it means
you CAN use it purely with Access tables/databases. It states it
accepts the ON DELETE etc clauses. BUT I looked at the DAO Create
method and this is what it had at the top:
"Creates a new Relation object (Microsoft Jet workspaces only)." I
give up!

You totally lost me there, Lester. DAO and ADO can accomplish similar
tasks, but their approaches are different.

So now I am playing with CreateRelation method and it looks
promising. Thanks for your help though.

OK. I looked at that, too. I think you can use the Attributes property
with CreateRelation to do what you want.

Good luck,
Hans