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/

[Bob Alston]

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...

Thanks in advance,
Alex

Do some googling on "Jet database engine" and you may find your answers.

Bob

[Trevor Best]

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.

--
[OO=00=OO]

[MLH]

Trevor's comments are dead on the money.
Well worth the reading.

[Jim M]

"It also has a not undocumented but seldom exploited mode of access
called ISAM"

What is this 'mode of access', and how/when is it utilized by amateur
developers like myself? Thanks!

Jim

[Trevor Best]

Jim M wrote:

"It also has a not undocumented but seldom exploited mode of access
called ISAM"

What is this 'mode of access', and how/when is it utilized by amateur
developers like myself? Thanks!

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),
e.g.

Set db = dbengine(0).ope ndatabase("f:\m ybackend.mdb")

Then you open a table directly, specify an index to search on and use
that to find records, e.g.

set rst = db.openrecordse t("MyTable", dbOpenTable)
with rst
.index = "MyIndexNam e"
.Seek "=", MyValue
if .Nomatch then
msgbox "eeek"
else
msgbox "Yip"
end if
.close
end with
set rst = nothing

If you have many records to visit with the same value, e.g.

select * from MyTable where field1='somethi ng'

you can loop these as after the first .Seek the recordset is sorted in
that index order so you can use a combination of .seek then .MoveNext
and check for a value change to get to the end of that block matching
the criteria as there is no .Seeknext method.

The above code would not be portable so if you ever conceive of
upgrading to a SQL Server back end then you could be in for a major
re-write.

--
[OO=00=OO]

[David W. Fenton]

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*.

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

[Trevor Best]

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.

--
[OO=00=OO]

[Hank]

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

[A.P. Hofstede]

Thanks for the pointer.

On Tue, 31 May 2005 20:25:17 +0200, Bob Alston
<tu************ ****@cox.net> 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...
Thanks in advance,
Alex

Do some googling on "Jet database engine" and you may find your answers.

Bob

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