cascase

[Wilson]

Dear friends,

I tried to execute this sql in access 2003
------------
1. create table master (id text(10), name text(100))
2. create table sub (id text(10), mas_id text(10), sub_name text(100))
3. alter table master alter column id text(10) constraint MyKey primary key
4. alter table sub alter column id text(10) constraint MyKey primary key

5. alter table sub add constraint MyFKey foreign key (mas_id) references
master ON UPDATE CASCADE ON DELETE CASCADE
---------

1 to 4 steps are working perfect. 5th step failes with an error "syntax
error in constraint"

I tried by removing "ON UPDATE CASCADE ON DELETE CASCADE", it is working!
but, no RI created.

Can anyone point out the syntax error?

thanks in advance

Wilson

 

[giorgio rancati]

Hi Wilson,

In VBA you can use the ADODB Execute method
----
CurrentProject.Connection.Execute _
"alter table sub add constraint MyFKey foreign key (mas_id) references "
& _
" master (Id) ON UPDATE CASCADE ON DELETE CASCADE"
----

you can also cerate the tables in only two steps
----
Dim strSql As String

'Create Master Table
strSql = "create table master (" & _
"id text(10) Constraint MyKey primary key, " & _
"name text(100)) "
CurrentProject.Connection.Execute strSql

'Create Sub Table
strSql = "create table sub (" & _
"id text(10) constraint MyKey primary key, " & _
"mas_id text(10), sub_name text(100), " & _
"constraint MyFKey foreign key (mas_id) " & _
"references master (id) " & _
"ON UPDATE CASCADE ON DELETE CASCADE)"
CurrentProject.Connection.Execute strSql
----

If you want to use query builder you must set *SQL Server Compatible Syntax
(ANSI 92)* in Tools-->Option-->Table/Query.
nb. read the warning message

bye