Is Access true RDBMS

[Brad Wood]

I've read some info about Access that I'm not sure is
correct. If someone can answer any of the following
questions with certainty or refer me to some documentation
I would appreciate it:

- Is Access truly a "set based" RDBMS? I read that it is
not; that it is really just a table based DBMS.

- Does Access truly support SQL? I read that it doesn't
(this one seems really suspect since you can see Access
creating SQL statements as you design queries).

- When the database resides on a server, does Access
really send all records from table(s) to a client even
when a SQL statement filters fields and values? Maybe
this is dependent on the cursor type, but I thought even
with a client side cursor, you're only retrieving the data
you ask for (so if you ask for Field1 where UniqueID=1 you
only get one record with one field and no other data would
pass over the network).

 

[robert]

1. Yes, Access is a true RDBMS. Sets are supported with
queries.

2. Yes, Access truly supports SQL. Your observation
about queries is correct.

3. Access uses a process called "Rushmore Optimization"
to speed the execution of queries. Whether this actually
causes only the subset of SQL records to be passed over
the wire is unclear. There are "good" and "bad" ways of
writing queries in Access, that allow Access to utilize
Rushmore Optimization to a greater or lesser degree.

Certainly, if an Access query contains conditions, enough
data is probably passed over the wire to allow the
frontend query to select the correct records, but only the
complete recordset that actually satisfies the query
conditions need to be passed over the wire.

In that sense, Access will clearly not be as fast as a
full-blown client/server database like SQL Server, because
in that kind of environment the queries are typically run
on the SERVER, not the WORKSTATION (such queries are
called "Stored Procedures"). This technique guarantees
that only the final recordset is passed over the wire, and
nothing more.

Hope this helps.

 

[Tony Toews]

I've read some info about Access that I'm not sure is
correct. If someone can answer any of the following
questions with certainty or refer me to some documentation
I would appreciate it:

- Is Access truly a "set based" RDBMS? I read that it is
not; that it is really just a table based DBMS.

Dunno about the "set based" RDBMS. However some have stated that
Access is not an RDMS. That said, by the strictest of definitions,
there are currently no products on the market place, including Oracle
and SQL Server, which are true RDMSs. Some people are also way too
anal in my not so humble opinion.

- Does Access truly support SQL? I read that it doesn't
(this one seems really suspect since you can see Access
creating SQL statements as you design queries).

Again this depends on definitions. Yes, Access supports SQL but
maybe not exactly as per the definition and it has exensions.

- When the database resides on a server, does Access
really send all records from table(s) to a client even
when a SQL statement filters fields and values? Maybe
this is dependent on the cursor type, but I thought even
with a client side cursor, you're only retrieving the data
you ask for (so if you ask for Field1 where UniqueID=1 you
only get one record with one field and no other data would
pass over the network).

If the selection and sorting criteria involve an index then only those
pages containing the relevant index entries are downloaded. Then the
relevant pages containing the selected records are downloaded.

If the selection and sorting criteria can't use an index, for example
a data field which doesn't happen to have an index on it, then yes,
the entire table is downloaded to the computer.

Now yes, Jet does create temp files on the PC as required but this
does not mean that the entire table gets put in the hard drive either.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[david epsom dot com dot au]

1) No DBMS is a true RDBMS: a true RDBMS is an idea,
a theory. Some DBMS come closer to meeting an arbitrary
set of the theoretical rules for a true RDBMS. Access
is a true RDBMS when compared to DBASE or FOXPRO: it
is not regarded as a true RDBMS by academics, Oracle
DBA's or other people convinced of their own importance.

2) No true RDBMS is 'set based'. The true RDBMS rules
are slightly different from set rules.

3) No true RDBMS supports SQL. SQL breaks the rules
of a true RDBMS.

4) One of the ideas of a true RDBMS is that the only
relations between items of data are relations that are
stored in the database, and can be manipulated like
data. Access stores data in a file, on a disk, with
file system record locking and file locking, with
records stored in pages. A distinction may be made
between views (queries) and views (tables). To the
extent this is visible to the user, or effects the
user, Access is not a true RDBMS.

5) Access supports two SQL syntaxes, sometimes called
'JET SQL' and 'ANSI SQL' JET SQL is a subset, with
proprietary extensions, of an earlier standard SQL.
ANSI SQL is a subset, with proprietary extensions, of
a later standard SQL. All other DBMS that support
SQL use a subset, with proprietary extensions, of
a standard SQL.....

6) Access uses the JET database engine. JET is a
distributed engine. When a database resides on a File
Server, the local part of JET requests indexes and
database statistics from the Server, decides what data
is necessary for scanning or reporting, and requests
the relevant data. Because the analysis is done by
the local part of the JET engine, more data must
be local than if the analysis was done at the server
side. This may, or may not, include all data from
all records from all tables. Generally it is less
than 'all records'. Inevitably it is more than 'only
the data you ask for'.


(david)

 

[Mike Painter]

1. Yes, Access is a true RDBMS. Sets are supported with
queries.

2. Yes, Access truly supports SQL. Your observation
about queries is correct.

Be aware that there are lots of flavors of SQL and Access is one of them. It
taastes a lot like the one used on the relational database that MSFT calls
SQL (SQL means Strructured Query Language and is not a database)

In that sense, Access will clearly not be as fast as a
full-blown client/server database like SQL Server, because
in that kind of environment the queries are typically run
on the SERVER, not the WORKSTATION (such queries are
called "Stored Procedures"). This technique guarantees
that only the final recordset is passed over the wire, and
nothing more.

That depends on the environment and in a single user situation pure Access
is much faster than SQL server because SQL Server has a lot of overhead
critical to reliability.

Queries in a single user environment over the same table, one in SQL Server
2000 and the other one copied to an Access mdb file are always faster in
Access.