What kind of database is access?

[A.P. Hofstede]

Could someone tell me where MS-Access (current and 97?) fit(s) on the
RDBMS - ORDBMS - ODBMS spectrum?
I gather it's relational, but how does it size up against/follow SQL2/3/4
definitions and how does it compare to other database vendors?
Any articles that might be of interest? It's for an essay on database
models...

Thanks in advance,
Alex

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

[A.P. Hofstede]

Thanks, that helped.

On Wed, 01 Jun 2005 01:57:12 +0200, Trevor Best <no****@besty.o rg.uk>
wrote:

A.P. Hofstede wrote:

Could someone tell me where MS-Access (current and 97?) fit(s) on the
RDBMS - ORDBMS - ODBMS spectrum?
I gather it's relational, but how does it size up against/follow
SQL2/3/4 definitions and how does it compare to other database vendors?
Any articles that might be of interest? It's for an essay on database
models...

Not this ol' chestnut.

I'll forgo the Access isn't a database but Jet is malarkey as this is
after all comp.databases. ms-access and not comp.databases. jet so:
Set semantics_mode off

If by RDBMS you mean a relational database management system that
conforms to all 12 rules laid down by Edgar Codd, there isn't one. Only
close approximations.

Like many database vendors, you can create a relational database in it
but it doesn't force you to. Some of the biggies like Oracle, DB/2, SQL
Server, etc are considered by many to be RDBMSs but it is possible to
create databases in them that are totally unrelational and break nearly
every rule in the book. Much of the resulting database lies sqaurely on
the shoulders of the DBA.

Access (or Jet) differs from most databases labelled as RDBMSs by being
a desktop product or file server based product so that the workstation
does the selection/sorting instead of those tasks being performed on a
server. With standard SQL methods of retrieval this can be quite slow
depending on bandwidth/amount of data.

It also has a not undocumented but seldom exploited mode of access
called ISAM, this can make a bigger more expensive database look like a
slouch, it's as far removed from SQL as a pork pie is from a vegan's
diet but if you have a shed load of data (<2GB obviously) and want
little bits of it faster than a speeding bullet then that may be for you.

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

[Trevor Best]

Hank wrote:

Trevor,
Very interesting what you have to say about ISAM files. I
tired to run your code to do benchmarks and it worked. But how do you
traverse a table with parameters? Your function does not seem to allow
Queries...only Tables.
Could you give us a code snippet where you traverse a table
with a particular criteria like ( [Employee ID] = 126 )?
Thanks for the help
Hank Reed

Firstly, you cannot use it on queries, only tables, to traverse what you
want, since the recordset will get sorted by the index that you seek on
you'd do something like (assuming Index is called idxEmpID)

lngID = 126
with rst
.index = "idxEmpID"
.seek "=", lngID
if not .Nomatch then
do until .eof or .fields("Employ ee ID")<>lngID
' do something
.movenext
loop
end if
end with

I've not tested if you need the .nomatch check as I'm not sure if the
cursor would go to .EOF if not found.

--
[OO=00=OO]

[David W. Fenton]

Trevor Best <no****@besty.o rg.uk> wrote in
news:42******** **************@ news.zen.co.uk:

David W. Fenton wrote:

Trevor Best <no****@besty.o rg.uk> wrote in
news:42******** *************** @news.zen.co.uk :

ISAM = Indexed Sequential Access Method
It's part of DAO.
You can use it only on local tables or tables from a back end
database if you've opened that database in code (i.e can't use on
linked tables),

Er, why, then, is the term ISAM used in the drivers that support
linking to a number of non-Jet data formats, many of which have
no indexing support?

In other words, I think you're misidentifying what ISAM actually
*is*.

True, they are called ISAM but you can only use the .Index and
.Seek methods on native jet tables AFAIK.

Well, doesn't that mean, then, than those features have zilch to do
with ISAM?

I've never needed the speed of a .Seek, so I've never used it
myself.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

David W. Fenton wrote:

True, they are called ISAM but you can only use the .Index and
.Seek methods on native jet tables AFAIK.

Well, doesn't that mean, then, than those features have zilch to do
with ISAM?

Well ISAM is what Microsoft used to call MDB files before they were used
in Access. The *index* is sorted in order so is *sequential* and it's an
*access method*. Do you see a pattern emerging here? If MS don't call it
ISAM any more than that doesn't stop it from being ISAM, hell since when
have MS been good at naming things? They call a rowversion column in SQL
Server a "timestamp" and it has nothing to do with time.
I've never needed the speed of a .Seek, so I've never used it
myself.

What do you want? A medal?

--
[OO=00=OO]

[David W. Fenton]

Trevor Best <no****@besty.o rg.uk> wrote in
news:42******** *************** @news.zen.co.uk :

David W. Fenton wrote:

True, they are called ISAM but you can only use the .Index and
.Seek methods on native jet tables AFAIK.

Well, doesn't that mean, then, than those features have zilch to
do with ISAM?

Well ISAM is what Microsoft used to call MDB files before they
were used in Access. The *index* is sorted in order so is
*sequential* and it's an *access method*. Do you see a pattern
emerging here? If MS don't call it ISAM any more than that doesn't
stop it from being ISAM, hell since when have MS been good at
naming things? They call a rowversion column in SQL Server a
"timestamp" and it has nothing to do with time.

It doesn't? Isn't the value derived from the time it's updated?

Seek with tabletype recordsets is a feature of DAO. I see no point
in dragging in the term ISAM, whatever historical appropriateness it
may have, because it is mostly used in Access for things that
*don't* provide indexed access to data, and, therefore, can't
provide the benefit you're attributing to ISAM.

I've never needed the speed of a .Seek, so I've never used it
myself.

What do you want? A medal?

No, but I'm just pointing out that speed is not everything. In my
experience, the number of situations where you need the speed and
are able to use a tabletype recordset are quite few and far between.
In 10 years of Access programming, it's never happened to me in any
of the dozens of applications I've created.

I think that's important context, so people who don't have the
experience won't mistakenly go down this road trying to use it where
it can't really help your application.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

David W. Fenton wrote:

naming things? They call a rowversion column in SQL Server a
"timestamp" and it has nothing to do with time.

It doesn't? Isn't the value derived from the time it's updated?

From BOL
timestamp
timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database. timestamp
is used typically as a mechanism for version-stamping table rows. The
storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp
data type defined in the SQL-92 standard. The SQL-92 timestamp data type
is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server™ may modify the behavior of
the Transact-SQL timestamp data type to align it with the behavior
defined in the standard. At that time, the current timestamp data type
will be replaced with a rowversion data type.


--
[OO=00=OO]

[Trevor Best]

David W. Fenton wrote:

Seek with tabletype recordsets is a feature of DAO. I see no point
in dragging in the term ISAM, whatever historical appropriateness it
may have, because it is mostly used in Access for things that
*don't* provide indexed access to data, and, therefore, can't
provide the benefit you're attributing to ISAM.
From BC7 Help
<---
ISAM
The Microsoft BASIC Compiler includes a set of ISAM (Indexed Sequential
Access Method) features for writing data management programs. See
Chapter 10, "Database Programming with ISAM," in the BASIC Programmer's
Guide for more information about using ISAM.
The OPEN statement used with the ISAM keyword creates an ISAM database
and tables where they do not exist, and opens them.

See Also BEGINTRANS BOF CHECKPOINT CLOSE COMMITTRANS CREATEINDEX
DELETE DELETEINDEX DELETETABLE EOF FILEATTR GETINDEX$ INSERT LOF
MOVEdest OPEN RETRIEVE ROLLBACK SAVEPOINT SEEKoperand SETINDEX
TEXTCOMP TYPE UPDATE

(MOVEdest)
MOVEFIRST [#]filenumber%
MOVELAST [#]filenumber%
MOVENEXT [#]filenumber%
MOVEPREVIOUS [#]filenumber%

filenumber% The number of an open ISAM table.

(SEEKoperand)
SEEKGT [#]filenumber% ,keyvalue [,keyvalue]...
SEEKGE [#]filenumber% ,keyvalue [,keyvalue]...
SEEKEQ [#]filenumber% ,keyvalue [,keyvalue]...

filenumber% The number of an open ISAM table.
keyvalue An expression less than 256 characters long.

--->
Some of that looks familiar?

Definitions of ISAM
http://www.webopedia.com/TERM/I/ISAM.html
http://search390.techtarget.com/sDef...214626,00.html
http://www.amr-usa.com/thindex.htm (Paragraph entitled "ISAM (Indexes)")

So DAO is updated, merged the SEEKoperand into one Seek and renamed
SETINDEX and made it a property instead of a command, added a few bits
for SQL support amongst other things and is OO orientated, but do you
still think the tables in the MDB file are anything other than ISAM?
No, but I'm just pointing out that speed is not everything. In my
experience, the number of situations where you need the speed and
are able to use a tabletype recordset are quite few and far between.
In 10 years of Access programming, it's never happened to me in any
of the dozens of applications I've created.
10 years ago was Access 2.0, not very quick with large data sets and as
I remember, .FindFirst and .FindNext were notoriously slow. Opening just
the bits you wanted in a dynaset improved things but wasn't a patch on
..Seek.
I think that's important context, so people who don't have the
experience won't mistakenly go down this road trying to use it where
it can't really help your application.

I've already pointed out the pitfall of using this method earlier in
this thread.

--
[OO=00=OO]

[David W. Fenton]

Trevor Best <no****@besty.o rg.uk> wrote in
news:42******** *************** @news.zen.co.uk :

David W. Fenton wrote:

naming things? They call a rowversion column in SQL Server a
"timestamp " and it has nothing to do with time.

It doesn't? Isn't the value derived from the time it's updated?

From BOL
timestamp
timestamp is a data type that exposes automatically generated
binary numbers, which are guaranteed to be unique within a
database. timestamp is used typically as a mechanism for
version-stamping table rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the
timestamp data type defined in the SQL-92 standard. The SQL-92
timestamp data type is equivalent to the Transact-SQL datetime
data type.

A future release of Microsoft® SQL Server™ may modify the behavior
of the Transact-SQL timestamp data type to align it with the
behavior defined in the standard. At that time, the current
timestamp data type will be replaced with a rowversion data type.

Access GUIDs don't include a time component that can be extracted
out of them, but time is used to derive the value.

I see nothing in what you've quoted that contradicts that.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

David W. Fenton wrote:

[snip: T-SQL timestamp nothing to do with time]

Access GUIDs don't include a time component that can be extracted
out of them, but time is used to derive the value.

I see nothing in what you've quoted that contradicts that.

FFS

http://groups-beta.google.com/group/...68fdc025bd1ed4

http://tinyurl.com/dklv3

--
[OO=00=OO]

[jimfortune]

Trevor Best wrote:

True, they are called ISAM but you can only use the .Index and .Seek
methods on native jet tables AFAIK.

--
[OO=00=OO]

Today I had to reinstall Access 2.0 using the 31 Office 4.3 diskettes
on a machine that got whacked by a virus and had been running a POS app
for about nine years. With moderate trepidation I performed the
install using the ancient 1.44 Meg HD disks. The only file that could
not be read from the disks was MSQUERY.EXE (whew!). All the necessary
functions of the app worked correctly. I did a custom install and
noticed that installing the ISAM drivers was a separate checkbox
option. That seemed to indicate that ISAM in Access went beyond table
seeks, at least in A2. ISAM brings back distant memories of using JCL
on an IBM mainframe and of how nearly everyone failed to realize how
important SQL would become. The page indexing method used by Access
queries is not intuitive. I only realized recently that Access is
using something other than an ISAM style indexing method.

James A. Fortune