Re: dropping a relationship (i.e. constraint?)

Okay, so they may have been something in that SQL statement that JET was not
able to execute through DAO.

There are some other side effects of enabling the ANSI 92 format, such as
using % as the wildcard with the Like operator, instead of *.

An alternative might have been to try executing the DDL SQL statement under
ADO in code, e.g.:
strSql = "ALTER ...
CurrentProject.Connection.Execute strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David Mueller" <DavidMueller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:71F8D07F-7C48-4348-A298-A2B83486CAA8@xxxxxxxxxxxxxxxx
Here's an update - and my solution!

(1) I created the relationships using the GUI, and I've been completely
ignorant of constraint names until last week. Sure, I knew they existed
but
until last week I never need to know about them.

(2) This morning I discovered that I couldn't use "ALTER COLUMN ... DROP
DEFAULT" either. I went searching and found this setting...

Tools | Options | Tables/Queries | SQL Server Compatible Syntax (ANSI 92)
|
<Check> This database.

This was NOT checked. After I checked it, my DDL statements worked. I'm
hoping that checking/un-checking this setting isn't a problem. I couldn't
find much about this on MS website.

(3) Should this have been checked from the beginning? Had I known about
this checkbox, I think I wouldn't developed with it checked from the
beginning. Perhaps a question for another day (or now if you're up for
it):
Why doesn't Jet support this very common syntax? especially in 2003. I'm
sure I'm just ignorant of a reasonable explanation - just like I was
regarding constraint names.

Thank you both for the good code, confirmations, and education in
using/creating contraint names.

David
x5169

"Chris O'C via AccessMonster.com" wrote:

Allen Browne wrote:
As you said, JET uses Table1Table2 type names for the index if the name
is
available. If it's not available, JET uses a GUID.

I can't test this out in earlier versions, but I'll show you what I'm
seeing
in Access 2003. If you create the foreign key constraint using SQL and
don't
name this constraint, the resulting constraint name is a different
pattern
from what you'd get if you generated a GUID (Replication ID) and put that
into the query. For example:

CREATE TABLE tblParent
(
RName Text (40) NOT NULL,
Team Text (40) NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY (RName)
);

CREATE TABLE tblChild
(
ID Long NOT NULL,
Region Text (40) NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY (ID),
FOREIGN KEY (Region) REFERENCES tblParent (RName)
);

The resulting constraint name in MSysRelationships is
Rel_390B6EE5_5801_4C13.
Notice there's no quotes or braces. If I drop the table tblChild and
recreate it and name the constraint this time:

CREATE TABLE tblChild
(
ID Long NOT NULL,
Region Text (40) NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY (ID),
CONSTRAINT "{44BC5CFE-B4AD-4D2F-943E-42C76EDAAB50}"
FOREIGN KEY (Region) REFERENCES tblParent (RName)
);

The resulting constraint name in MSysRelationships is
"{44BC5CFE-B4AD-4D2F-
943E-42C76EDAAB50}". Notice the quotation marks and the braces. I have
to
use quotation marks and braces in my SQL to get it to work without a
syntax
error, and this name appears in MSysRelationships *exactly* as I've typed
it
in the SQL pane. The pattern is very different from
Rel_390B6EE5_5801_4C13.

If it's not available, JET uses a GUID. The GUIDs are unsightly,
but don't really have the data-matching issues michka refers to when
discussing fields.

He mentioned that in different versions of Access, comparing GUID's
changes.
He didn't list "{44BC5CFE-B4AD-4D2F-943E-42C76EDAAB50}" as one of the
valid
syntaxes, which makes me think David is issuing his drop statement in an
earlier version. Maybe {GUID{44BC5CFE-B4AD-4D2F-943E-42C76EDAAB50}}
would
work.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200612/1




.

Relevant Pages

  • Re: SQL
    ... Even SQL cannot avoid them. ... If you add X with one constraint to Y with other constraint and what would ... RDBMS exist only because in 70s there were no languages capable to provide ... There is no numeric type for all purposes. ...
    (comp.object)
  • Re: SQL
    ... SQL is more or less based on "COBOL thinking". ... possible nor most type-free that a relational query language can be. ... If you add X with one constraint to Y with other constraint and what would ... A relational interface fits to a kd-tree as a beach umbrella does ...
    (comp.object)
  • Re: Parameter??
    ... in common with most available SQL ... given month' Validation Rule or constraint? ... date_col DATETIME NOT NULL, ... The above relies on the Jet implementation to coerce a DATETIME to ...
    (microsoft.public.access.queries)
  • Re: How do you mulitply in a field?
    ... asked how one places a unique constraint on a combination of fields ... This will work for Jet in ANSI-92 Query Mode Jet SQL. ... I apply security so that the database can't be hacked. ... If I am selecting only one name, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: dropping a relationship (i.e. constraint?)
    ... ignorant of constraint names until last week. ... RName Text NOT NULL, ... CONSTRAINT PrimaryKey PRIMARY KEY (RName) ...
    (microsoft.public.access.queries)
Loading