Re: About MS-Access RDBMS

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

"Jamie Collins" <jamiecollins@xxxxxxxxxx> wrote in
news:1113308772.786482.194960@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:


>> it does not use the same langauge for security features or the
>> system catalog or queries, so this is a fail.
>
> Jet has DML (SELECT, UPDATE, DELETE etc), DDL (CREATE TABLE, CREATE
> VIEW, DROP PROCEDURE etc), DCL (CREATE GROUP, DROP USER, ROLLBACK
> TRASACTION etc), which can be considered sublanguages of the SQL
> language. So why do you think this is a fail?

I take the point about the DCL, but I've never used those commands and
wasn't aware that they were there. It's still a fail because the system
catalog is not _formally_ available to SQL, and Codd keeps repeating that
the system tables should be accessible in exactly the same way as the
normal data tables. There is nothing to stop MS changing the way the
catalog tables are built in a future version of Access (and God knows
they have taken worse decisions in the past) that will simply break loads
of existing applications that do SELECTs FROM _msysObjects and whatever.

>> Access can define ... validation rules that cover one table and rules
>> across multiple tables. ... this rule is a pass
>
> A Jet CHECK constraint cannot refer to rows in the same table in which
> it is defined. I assume the same applies to Validation Rules in the MS
> Access UI. A fail, then?

No: using ADO and Jet 4, this sort of thing works:

ALTER TABLE MyTable
ADD CONSTRAINT NoCopy CHECK 0 =
( SELECT COUNT(*) FROM MyOtherTable
WHERE MyOtherTable.IDNumber = MyIDNumber
)

Stupid example I know, but you get the picture. It can be useful, for
example, in subtyping, where you want to make sure that a master record
can be echoed in only one of the subtypes at a time.

It's not available to the Access UI out of the box, which appears to use
DAO and Jet 3.6, but it can be done. This was pointed out quite recently
by someone else (sorry if it was you!!!) in these NGs. I didn't know
about it before either and was delighted when I tested it and it worked.

> Jet supports a VIEW syntax, of course. Your comment reminded my how
> Jet's syntax for performing an UPDATE with a JOIN is 'non-relational'
> and that doing things the 'proper' way i.e. with an EXISTS (<subquery>)
> clause results in the baffling "updateable query" error. Perhaps 7)
> should be a fail then due to a fundamental design flaw... sorry,
> feature, rather than being a 'limitations of the language'?

I confess to being baffled by SQL and views, and in particular how to get
Access to update what I want it to. Rule 7 is meant to make sure that all
four commands are available to SQL (compare with other dbs that will
SELECT but everything else has to be row-by-row). Full View independence
is probably one of those areas that is genuinely a "theoretical
construct" and not able to be physically implemented. This is where I
start to get out of my depth, though.

And by then I was starting to get fed up with typing what turned out to
be a much longer response than I started with!!

All the best


Tim F

.

Relevant Pages

  • Re: RunSQL vs Execute
    ... Yes, these are JET 4 SQL extensions, but if your SQL statement using them is ... called with a DAO.Database, it will fail, with an error, the statement would ...
    (microsoft.public.access.formscoding)
  • Re: 2003 Server - Indexing Service catalog corrupt
    ... Empty the catalog. ... After a reboot of the server the searches fail until we kill the catalog. ... there is no script that uses ole automation objects to empty the ...
    (microsoft.public.inetserver.indexserver)
  • Re: JET bandsaw update
    ... but give them a call and ask them to send you the catalog: ... It is a resource that every owner of a 14" Jet, Delta, or Grizzly, who is ... interested in getting the maximum benefit from their saw, ...
    (rec.woodworking)