Re: Add constraint returns syntax error on constrain clause

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Maybe an example will be more useful about what I have in mind :-)


Using Northwind, if you try, in the query designer:

ALTER TABLE Products ADD CONSTRAINT ProductInCategory FOREIGN KEY
([categoryID]) REFERENCES Categories ([categoryID]) ON DELETE CASCADE


you get a syntax error. In fact, the query designer, by default, works with
DAO and DAO is not aware of many Jet 4.0 extensions.


Always in Northwind, this time, in the immediate (debug) window, generally
open with Ctrl_G,


CurrentProject.Connection.Execute "ALTER TABLE Products ADD CONSTRAINT
ProductInCategory FOREIGN KEY ([categoryID]) REFERENCES Categories
([categoryID]) ON DELETE CASCADE"



(it is one line, the news reader probably make it two or three lines )

then, you still get an error, but NOT a syntax error anymore, but a conflict
error:

"The cascading options for the new reference conflicts with existing
reference 'CategoriesProducts'"


That is why I asked you to tried in the Immediate (Debug) window, rather
than through the query designer... to see if Access is REALLY in a mood to
agree to accept the cascading constraint. If not, well, ...


If there is no error while using the immediate (debug) window, using ADO as
proposed, then I am clueless about what is wrong with doing the same, from
DotNet and ADONet.





Vanderghast, Access MVP



"Shnizles" <Shnizles@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:77328B54-C172-4392-BFBF-BFA4091B3A21@xxxxxxxxxxxxxxxx
when i use access 2007 , i go to create sql query
i enter the following line:
ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id])

it runs fine , i go to database tools tab and viewing relationships ,
i can see the relationship have been created successfuly ,
i can then double click the relation and manualy check the Cascade delete
check box
which means there is no problem with key or design what so ever ,
cause it works like that.

but however if i create a query in the access 2007 sql section with the
following line:

ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

Then i get syntax error on constrain clause which keeps amazing me
everytime.
let me know if i can asist with more info,
thanks.

"Michel Walsh" wrote:

I should have asked, first, if the statement works in Access itself, in
the
immediate debug window? if not, then, with the User Interface itself?
Maybe
there is a problem with the design, such as a missing key, or something.



Vanderghast, Access MVP


"Shnizles" <Shnizles@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1ABEC8A2-43BE-46CD-AE9D-B5F65DF99DB0@xxxxxxxxxxxxxxxx
hi , thanks for the efort ,
i know how to set a connection to a database ,
the problem is the error i get no matter what i do.
:|

"Michel Walsh" wrote:

I am not in a position to test it right now, and my experience with
DotNet
is with MS SQL Server, rather than with Jet, but I would have tried
(sorry,
it is in C# ) something like :


// references
using System;
using System.Data;
using System.Data.SqlClient; // to be changed if you use Jet


// ... in your code ...

String connectionString = "Provider=... " // ADO.Net connection
string
// probably something like
"Provider=Microsoft.Jet.OLEDB.4.0;
....
// in your case

using ( SqlConnection xnn = new SqlConnection(connectionString) )
{
System.Diagnostics.Debug.Assert(
null != xnn,
"Invalid connection string ? ");

xnn.Open() ;
System.Diagnostics.Debug.Assert(
xnn.State == ConnectionState.Open,
"Cannot open the db ? busy? poor connectivity ?
");

using ( SqlCommand cmd = xnn.CreateCommand() )
{
cmd.CommandType= CommandType.Text ;
cmd.CommandString = "ALTER TABLE ... " ;

cmd.ExecuteNonQuery() ;
}
}



Note: You have to change the data type for xnn, and cmd, here, they
are
specific for MS SQL Server.

Vanderghast, Access MVP



"Shnizles" <Shnizles@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D7B8C672-B3B3-421F-97F8-9EF810ACAD96@xxxxxxxxxxxxxxxx
im not using vba to excute the queries ,
im using vb.net ,
and well i tried to connect with OLEDB and ADODB connectors
both resulted with the same errors ,
as well when i run the sql query in access 2007 sql design tab ,
same error.

thanks for the response any other ideas?

"Michel Walsh" wrote:

Have you tried using ADO?

CurrentProject.Connection.Execute "ALTER TABLE Holiday ADD
CONSTRAINT
CalendarHoliday FOREIGN KEY (calendarid) REFERENCES Calendar (id)
ON
DELETE
CASCADE"


in the immediate (debug) window...

Once added, the constraint will work as well in DAO than in ADO,
but
it
is
just that many Jet 4.0 extensions work only under ADO, like here,
adding
a
cascading referential integrity.



Vanderghast, Access MVP


"Shnizles" <Shnizles@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:60A150D0-B80F-4878-9AB5-C52BFC314E85@xxxxxxxxxxxxxxxx
hi everyone ,
i am trying to add a constraint to a table ,
as the ms access guides suggested i have crafted the following
query:
ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

the above query result in a syntax error ,
the problem seems to be "ON DELETE CASCADE" , when i remove it
the
relationship is being added just fine , however the constraint
must
have
Delete cascade enabled ,

can anyone point me to the problem ,how can i solve it ,
thank you for your help!











.

Relevant Pages

  • Re: Data Definition Query
    ... Below is the code I typed in a pass through query. ... that what I am doing will not alter or change the SQL tables in any way as I ... ADD CONSTRAINT ...
    (microsoft.public.access.queries)
  • Re: Droping constraint
    ... i am trying to add a constraint with sql query, ... ALTER TABLE Holiday DROP CONSTRAINT CalendarHoliday ... ALTER TABLE Calendar DROP CONSTRAINT CalendarHoliday ...
    (microsoft.public.access.queries)
  • Add constraint returns syntax error on constrain clause
    ... as the ms access guides suggested i have crafted the following query: ... ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY ...
    (microsoft.public.access.queries)
  • Re: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
    ... Perhaps you have existing data that prevents the new constraint from being ... You can identify this data with the query below: ... > "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY ...
    (microsoft.public.sqlserver.server)
  • Re: Enterprise Manager confusion
    ... View designer over QA. ... as it does like the Access query builder. ... By the way, you can drag columns individually in QA, you don't have to ... entering it in the first place, and the View designer offers the ability ...
    (microsoft.public.sqlserver.tools)