Opening Access db via DAO in vb.net

[Arvin Meyer [MVP]]

Well after thoroughly thinking about this question , the answer seems

You don`t



Or maybe we are both missing something here

What you're missing is that it's not .NET that is using DAO as much as the
JET engine. And you can use ADO very successfully with the JET engine, but
is will be slower. Typically, that speed in a well designed database with a
well designed query is of little concern, becuase it is not likely that you
will be searching through more than 100K rows in JET, so a 1/2 second
doesn't mean much. But if you are looping through a recordset with 100K of
rows, it can mean a significant difference.

 

[Arvin Meyer [MVP]]

Agreed - Acecss is the way of the Dodo in terms of application
development.
Microsoft has pretty much pigeon holed it into a pure personal desktop
database now.

My clients which include Fortune 500 companies, Federal and local
government, and numerous others will be quite surprised. Departmental and
low user count applications are Access's forte. I do suspect that there are
far more single user Access applications, but the number multi-user apps
runs in the millions.

 

[Guest]

My clients which include Fortune 500 companies, Federal and local
government, and numerous others will be quite surprised. Departmental
and low user count applications are Access's forte. I do suspect that
there are far more single user Access applications, but the number
multi-user apps runs in the millions.

Yes, it "works", butmost developers are only using it because it is what
they're comfortable with. But with all the limitations with Access such as
the threat of DB corruption in a multi-user environment, why not use a real
embedded DB like SQL Compact Edition?

Even Microsoft acknowledges that Access isn't intended to be a general
purposes database engine - rather a personal DB:

http://www.microsoft.com/sql/editions/compact/sscecomparison.mspx

I just don't see the point of using Access anymore when there are better
alternatives out there.

 

[Guest]

What you're missing is that it's not .NET that is using DAO as much as
the JET engine. And you can use ADO very successfully with the JET
engine, but is will be slower. Typically, that speed in a well
designed database with a well designed query is of little concern,
becuase it is not likely that you will be searching through more than
100K rows in JET, so a 1/2 second doesn't mean much. But if you are
looping through a recordset with 100K of rows, it can mean a
significant difference.

If you're processing 100K rows, shouldn't that be a red flag that you're
making access do something it's not designed to do? Access is a personal
database - for that much data I'm sure you'll get better performance with
SQL Server Express. Yes, it's more software to install, but it can always
be installed on a central server and shared amongst multiple clients?

 

[Arvin Meyer [MVP]]

Yes, it "works", butmost developers are only using it because it is what
they're comfortable with. But with all the limitations with Access such as
the threat of DB corruption in a multi-user environment, why not use a
real
embedded DB like SQL Compact Edition?

Since I'm a database developer, and I do use several different database
engines, including SQL-Server, I might be able to shed some light on the
reasons.

First of all, we must separate Access from JET, which is one of the engines
which is usable with Access. I am assuming that you mean the JETengine. JET,
being a file-server based engine can corrupt if the data is used in bound
forms, if there is a disconnect during a write. Server based engines are
less likely to corrupt in that situation. Now practically speaking, that
doesn't happen much with decent quality equipment. As a matter of fact, in
my own experience, the last time I had any corruption was in the Spring of
2003 when the client had a faulty Dell WiFi card. Actually, I've only
experience corruption there's a memo field in the table being written to
during the write action. Multi-user has nothing at all to do with it, even a
single user writing to a database located on a server can trigger
corruption.

So when is there a good reason to use JET? Usually when cost is involved
because development is faster with JET and it's already on most user's
machines. There are also a few other reasons, like being able to use VBA
functions and even api's within queries, connecting multiple dissimilar
engines, etc.

When would I choose SQL-Server or another server based engine? A number of
reasons:

1. Large number of users.
2. The data is already in SQL-Server.
3. Security needs to be enforced at a high level.
4. Database is in use 24/7 and must be maintained without interference.
5. Flaky hardware or network that can't be fixed
6. Large project with more than 500 MB of data
7. Most of the users are on a WAN

Most databases do not fall into the above 7 reasons. The overwhelming number
are small business or departmental databases, used by less than a dozen
users and maintained by one of them. JET is an excellent engine for them.

 

[Arvin Meyer [MVP]]

If you're processing 100K rows, shouldn't that be a red flag that you're
making access do something it's not designed to do? Access is a personal
database - for that much data I'm sure you'll get better performance with
SQL Server Express. Yes, it's more software to install, but it can always
be installed on a central server and shared amongst multiple clients?

100K rows is a pittance for JET. I've searched through a half million rows
of data in a few seconds with properly designed indexes. SQL-Server Express
is about the same speed. Now if you have millions of rows of data, the full
SQL-Server product is definitely the one to choose.

I'm really sorry that you think that Access/JET is a single-user database.
You're obviously missing giving some of your users a fast, easily
maintainable solution.

 

[Ed Metcalfe]

If you're processing 100K rows, shouldn't that be a red flag that you're
making access do something it's not designed to do?

No! I have developed, and continue to support, three systems in which there
is a table of nearly three million records. To date I have had no corruption
issues and no performance issues.

Ed Metcalfe.

 

[Ed Metcalfe]

You talk as a ACCESS developer , a person who writes solutions completely
in ACCESS , throws in some Access forms and or VBA

No, I talk as a developer who uses Access for certain situations. When it is
not appropriate and a better alternative is available then I will pick an
alternative.

Ed Metcalfe.

 

[Ed Metcalfe]

No! I have developed, and continue to support, three systems in which
there is a table of nearly three million records. To date I have had no
corruption issues and no performance issues.

Ed Metcalfe.

And just to clarify, these are multi-user systems not single-user systems.

Ed Metcalfe.

 

[Ed Metcalfe]

As you can see, that article hasn't been updated in over 4 years. There
may be some info on the Access Team blog:

http://blogs.msdn.com/access/

I did a cursory search using DAO vs ADO and found:

http://blogs.msdn.com/access/search.aspx?q=DAO+vs+ADO&p=1

which seems to mention several postings by the MS-Access PMs on using DAO
in Access 2007. Sorry I did not do further research, but you're welcome
to.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thank you. I'll do some further reading tomorrow.

Ed Metcalfe.

 

[Arvin Meyer [MVP]]

And to rebuff the myth that Access can't handle more than a few users, one
of my systems had 53 users with up to 75 connections (i.e. multiple
front-ends could be opened by a single user. The users included 36 on the
LAN, 8 accessing remotely using an ASP front-end, and 9 with Terminal Server
connections. Again, no corruption and no performance issues.

This particular system has now been replaced with a PeopleSoft system
running Oracle, with numerous connection issues and down-time. It has less
than 3/4 of the functionality, takes more users to do the same work, and the
performance is abysmal when compared to what it replaced. It cost millions
to build and millions to maintain. I just love it when "real" developers go
to work. It validates my entire raison d'etre for being a database
developer.

 

[William Vaughn]

An interesting discussion. Since my name was brought up, I thought I should
weigh in.

As as consultant/mentor I spend an inordinate amount of time undoing
the exuberance of Access/JET developers and the applications the databases
that seem to spread like a virus through one department in an organization
after another. Case in point: a local hospital has thousands of JET
databases that contain patient data. They aren't HIPA-or
Sarbanes-Oxley-compliant and can't be so they must be converted and secured.
These and many other IT organizations have spent an unbelievable amount of
money and time to undo this mess.
Security is not the only issue. We still hear reports of applications
that outgrow their design because of success, lack of foresight or skill. We
hear that a popular application in one department spreads to others as
everyone wants to share the data. But the fundamental CP/M-class DBase
shared-file architecture is not designed for scalability. Sure, you can
store a "lot" of data, but each and every application that's accessing the
shared file is doing physical IO over the LAN. More data means more network
traffic and more vulnerability--lose a packet and the data is ???,
disconnect a client and the data is ???. While having uncommitted cached
rows can threaten data integrity, JET does not write to the database when
you tell it to--it caches writes to improve performance and unless you give
up performance and scalability for immediate data IO operations.
Unlike SQL Server, JET is not logged--when the power fails is committed
data written to the database and uncommitted transactions backed out? Ah,
no. IMHO JET is designed for small, light, 1 to few user database
applications where security is not important, where client data is
unimportant, where scalability is not important. Yes, there are lots of
places where you'll find applications like this--like the choir roster.
Sure, there are a lot of JET/DAO/Access applications out there and lots of
developers who are proficient at keeping them running--just like there are a
lot of '57 Chevy experts in Cuba as that's all they have to work with and
that's what they know best. When all you have is apples, you don't make a
lot of turkey sandwiches.

The other issue is application conversion scalability. So, your JET
application is successful. Your customer wants to share the data all over
the world or perhaps just all over the 14th floor--all 65 or 650 clients.
Now what? JET/Access/DAO/ADO.NET code is tough to port as it probably uses
the features that make JET fast and scalable but make it harder to convert
to a Web-based or client-server architecture. What do you do? Start over?
Get new developers? IMHO it makes more sense to think about scalability,
security and stability long before the first line of code is written. That's
tough when the foundation is adobe bricks.

It's for these and other reasons I can't in good conscious recommend it
to any business customers.

Microsoft has many divisions with many agendas. Access is a powerful
tool with a large, loyal (and often skilled) following of developers. I can
see how MS would want to continue to support Access and JET and it
is--Access 2007 still supports it. MS does not support DAO--we've all heard
that any number of times. That said, I agree DAO is the fastest way (closest
to the "native" way) to access JET--ADO and ADO.NET shove many layers in the
way. Layers :: slow and loss of features. And yes, JET/DAO make a sad
interface to SQL Server.
Yes, MS has improved and tuned the OLE DB providers for COM and .NET.
However, Microsoft has also spent considerably more time and money tuning
and configuring SQL Server to replace JET. SQL Express is one edition. Yes,
I'll be the first to agree that it's overkill for many applications where
JET is the current DBMS engine. However, SQL Server Compact Edition is not.
This is not a new player but an experienced engine that's ideal for the
single-user to the disconnected shared-database-via-synchronization
architectures of the 21st century. Like SQL Server 2008, SQLCe can encrypt
the entire database. This means when it's on that laptop containing the
Veterans Administration data and ends up in the hands of a creep, the data
is still safe. SQLCe has it's own native managed .NET provider. JET still
needs an OLE DB provider. That alone should imply MS's long-term intent.

I think MS would be well served to have Access add (or replace) JET with
SQL Server Express and Compact Edition... but that's just me. Yes, it's
going to be hard to take. I've been in this business for over 35 years.
We've all seen old technologies hold on long after their replacements have
come. Those that ride the wave of change seem to go on to the next wave.
Those that get behind sometimes drown.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Cor Ligthert[MVP]]

Nice written Bill,

Cor

An interesting discussion. Since my name was brought up, I thought I
should weigh in.

As as consultant/mentor I spend an inordinate amount of time undoing
the exuberance of Access/JET developers and the applications the databases
that seem to spread like a virus through one department in an organization
after another. Case in point: a local hospital has thousands of JET
databases that contain patient data. They aren't HIPA-or
Sarbanes-Oxley-compliant and can't be so they must be converted and
secured. These and many other IT organizations have spent an unbelievable
amount of money and time to undo this mess.
Security is not the only issue. We still hear reports of applications
that outgrow their design because of success, lack of foresight or skill.
We hear that a popular application in one department spreads to others as
everyone wants to share the data. But the fundamental CP/M-class DBase
shared-file architecture is not designed for scalability. Sure, you can
store a "lot" of data, but each and every application that's accessing the
shared file is doing physical IO over the LAN. More data means more
network traffic and more vulnerability--lose a packet and the data is ???,
disconnect a client and the data is ???. While having uncommitted cached
rows can threaten data integrity, JET does not write to the database when
you tell it to--it caches writes to improve performance and unless you
give up performance and scalability for immediate data IO operations.
Unlike SQL Server, JET is not logged--when the power fails is
committed data written to the database and uncommitted transactions backed
out? Ah, no. IMHO JET is designed for small, light, 1 to few user database
applications where security is not important, where client data is
unimportant, where scalability is not important. Yes, there are lots of
places where you'll find applications like this--like the choir roster.
Sure, there are a lot of JET/DAO/Access applications out there and lots of
developers who are proficient at keeping them running--just like there are
a lot of '57 Chevy experts in Cuba as that's all they have to work with
and that's what they know best. When all you have is apples, you don't
make a lot of turkey sandwiches.

The other issue is application conversion scalability. So, your JET
application is successful. Your customer wants to share the data all over
the world or perhaps just all over the 14th floor--all 65 or 650 clients.
Now what? JET/Access/DAO/ADO.NET code is tough to port as it probably uses
the features that make JET fast and scalable but make it harder to convert
to a Web-based or client-server architecture. What do you do? Start over?
Get new developers? IMHO it makes more sense to think about scalability,
security and stability long before the first line of code is written.
That's tough when the foundation is adobe bricks.

It's for these and other reasons I can't in good conscious recommend it
to any business customers.

Microsoft has many divisions with many agendas. Access is a powerful
tool with a large, loyal (and often skilled) following of developers. I
can see how MS would want to continue to support Access and JET and it
is--Access 2007 still supports it. MS does not support DAO--we've all
heard that any number of times. That said, I agree DAO is the fastest way
(closest to the "native" way) to access JET--ADO and ADO.NET shove many
layers in the way. Layers :: slow and loss of features. And yes, JET/DAO
make a sad interface to SQL Server.
Yes, MS has improved and tuned the OLE DB providers for COM and .NET.
However, Microsoft has also spent considerably more time and money tuning
and configuring SQL Server to replace JET. SQL Express is one edition.
Yes, I'll be the first to agree that it's overkill for many applications
where JET is the current DBMS engine. However, SQL Server Compact Edition
is not. This is not a new player but an experienced engine that's ideal
for the single-user to the disconnected
shared-database-via-synchronization architectures of the 21st century.
Like SQL Server 2008, SQLCe can encrypt the entire database. This means
when it's on that laptop containing the Veterans Administration data and
ends up in the hands of a creep, the data is still safe. SQLCe has it's
own native managed .NET provider. JET still needs an OLE DB provider. That
alone should imply MS's long-term intent.

I think MS would be well served to have Access add (or replace) JET
with SQL Server Express and Compact Edition... but that's just me. Yes,
it's going to be hard to take. I've been in this business for over 35
years. We've all seen old technologies hold on long after their
replacements have come. Those that ride the wave of change seem to go on
to the next wave. Those that get behind sometimes drown.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Cor Ligthert[MVP]]

Arvin,

DOA and ADO don't really fit in .Net.

You can use it, as you can use as you want probably even a punch card reader
which has drivers for Windows, but therefore a punch card reader is not a
part of Net.

Cor

 

[Michel Posseth [MCP]]

Well does this tell how good your Access program is , or does this tell
what it tells me, that the Developers that wrote this new system did not do
there job right
Or do you really believe Access outperfoms MS SQL or Oracle ?

There are lots of people out there that call themselves Developer , Software
engineer ,DBA`s ,,,,,,, but do not deserve there working titles i had once
to explain to a Company`s SQL DBA what BCNF stands for , and so i have
numerous examples that i encountered .

And yes a well designed ACCESS database, with a well designed ACCESS GUI
may perfom bether as a poorly designed Oracle database with a spaghetti
coded Visual studio application .

Michel

 

[Michel Posseth [MCP]]

Cor ,

To stay in our previous analogy

i believe Columbus has arrived

Bill ,

You just smashed the egg on the table


Michel

Nice written Bill,

Cor

An interesting discussion. Since my name was brought up, I thought I
should weigh in.

As as consultant/mentor I spend an inordinate amount of time undoing
the exuberance of Access/JET developers and the applications the
databases that seem to spread like a virus through one department in an
organization after another. Case in point: a local hospital has thousands
of JET databases that contain patient data. They aren't HIPA-or
Sarbanes-Oxley-compliant and can't be so they must be converted and
secured. These and many other IT organizations have spent an unbelievable
amount of money and time to undo this mess.
Security is not the only issue. We still hear reports of applications
that outgrow their design because of success, lack of foresight or skill.
We hear that a popular application in one department spreads to others as
everyone wants to share the data. But the fundamental CP/M-class DBase
shared-file architecture is not designed for scalability. Sure, you can
store a "lot" of data, but each and every application that's accessing
the shared file is doing physical IO over the LAN. More data means more
network traffic and more vulnerability--lose a packet and the data is
???, disconnect a client and the data is ???. While having uncommitted
cached rows can threaten data integrity, JET does not write to the
database when you tell it to--it caches writes to improve performance and
unless you give up performance and scalability for immediate data IO
operations.
Unlike SQL Server, JET is not logged--when the power fails is
committed data written to the database and uncommitted transactions
backed out? Ah, no. IMHO JET is designed for small, light, 1 to few user
database applications where security is not important, where client data
is unimportant, where scalability is not important. Yes, there are lots
of places where you'll find applications like this--like the choir
roster. Sure, there are a lot of JET/DAO/Access applications out there
and lots of developers who are proficient at keeping them running--just
like there are a lot of '57 Chevy experts in Cuba as that's all they have
to work with and that's what they know best. When all you have is apples,
you don't make a lot of turkey sandwiches.

The other issue is application conversion scalability. So, your JET
application is successful. Your customer wants to share the data all over
the world or perhaps just all over the 14th floor--all 65 or 650 clients.
Now what? JET/Access/DAO/ADO.NET code is tough to port as it probably
uses the features that make JET fast and scalable but make it harder to
convert to a Web-based or client-server architecture. What do you do?
Start over? Get new developers? IMHO it makes more sense to think about
scalability, security and stability long before the first line of code is
written. That's tough when the foundation is adobe bricks.

It's for these and other reasons I can't in good conscious recommend
it to any business customers.

Microsoft has many divisions with many agendas. Access is a powerful
tool with a large, loyal (and often skilled) following of developers. I
can see how MS would want to continue to support Access and JET and it
is--Access 2007 still supports it. MS does not support DAO--we've all
heard that any number of times. That said, I agree DAO is the fastest way
(closest to the "native" way) to access JET--ADO and ADO.NET shove many
layers in the way. Layers :: slow and loss of features. And yes, JET/DAO
make a sad interface to SQL Server.
Yes, MS has improved and tuned the OLE DB providers for COM and .NET.
However, Microsoft has also spent considerably more time and money tuning
and configuring SQL Server to replace JET. SQL Express is one edition.
Yes, I'll be the first to agree that it's overkill for many applications
where JET is the current DBMS engine. However, SQL Server Compact Edition
is not. This is not a new player but an experienced engine that's ideal
for the single-user to the disconnected
shared-database-via-synchronization architectures of the 21st century.
Like SQL Server 2008, SQLCe can encrypt the entire database. This means
when it's on that laptop containing the Veterans Administration data and
ends up in the hands of a creep, the data is still safe. SQLCe has it's
own native managed .NET provider. JET still needs an OLE DB provider.
That alone should imply MS's long-term intent.

I think MS would be well served to have Access add (or replace) JET
with SQL Server Express and Compact Edition... but that's just me. Yes,
it's going to be hard to take. I've been in this business for over 35
years. We've all seen old technologies hold on long after their
replacements have come. Those that ride the wave of change seem to go on
to the next wave. Those that get behind sometimes drown.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

http://msdn2.microsoft.com/en-us/library/ms810810.aspx
scroll to the bottom to see that DAO is officially declared Obsolete
( a long time ago )
note: that this paper was released the first time in January 2002 but
it was known to the programming comunity long time before that

I am a person with a strong readers memory ( everything i once read
stays in my mind ) so i just looked in my library i knew it was
somewhere there Chapter 8 Databases page 393 and page 394 of the
official Core reference guide of VB6 "Programming Microsoft Visual Basic
6.0".

If you read these 2 pages you will see that DAO is already in the
replacement phase in favor of ADO , the core VB6 book tells you between
the lines that DAO is VB 5.0 and in VB6 projects you should favor the
new ADO engine for the simple reasson that DAO is going to be replaced
by ADO remember that this book is written in early 1999 !!!

A funny thing i just encountered is that the writer of the book (
Balena ) tells you if you really need to use DAO or RDO buy the superb
book "Hitchiker`s guide to Visual basic and SQL server by William R.
Vaughn "

Well "William R. Vaughn" is also known as "Bill Vaughn" active in
these newsgroups and he is the person who convinced me that there is
absolutely no reasson at all to stick using Access now we have SQL
server CE

If a person walked into my office and dare to propose a desktop app
written in VS.Net 2008 with a ACCESS db backend wich uses DAO i would
laugh an not take this person serious annymore , i doubt if this person
is qualified to do his job right



Michel



"Michel Posseth [MCP]" <[email protected]> schreef in bericht
<snip>
DAO is still the recommended method for accessing data in Microsoft
Access databases if you are using the Jet database engine.
</snip>

Recomended by who ? as MS did not even bother to develop a 64 bit Jet
oledb driver for Access this means that even ADO.Net can`t work with
Access
so in my opinion MS doesn`t want you to use Access at all in newly to
develop products . that is probably also the reasson why in all study
materials , examples etc etc only connections to one of the SQL
family`s is shown ( wich by the way do have 64 bit equivalants )


<snip>
Its performance is
significantly better than ADO in this scenario.

</snip>

DAO `s perfomance is superb on ACCESS , as it is a specialized engine
optimized for this database

However my question is should you use a long time ago fased out
technology in a newly developed product ?
as i said when i was programming in VB6 ADO was already declared
Obsolete technology , so using it now in VB.Net is in my opinion
foolish

And if we are talking about perfomance lets put the cards right and
compares against SQL server CE wich is the substitute for a Access
database in .Net


Michel



"Ed Metcalfe" <[email protected]> schreef in bericht

What am I doing wrong?
<snip>
2 Is using DAO while it is already a long , long time ago declared
obsolete even in the end of VB6 it was already declared as obsolete
technollogy and ADO was prefered

If you nowadays still use ACCESS in my opinion for new projects you
should not ! but use one of the SQL engines closest to Access is the
SQL CE engine
, e.g. sql server everywhere then you should connect with ADO.Net.

So having said this it is still possible to connect with DAO ,
however i will sure not recomend it to you
<snip>

DAO is still the recommended method for accessing data in Microsoft
Access databases if you are using the Jet database engine. Its
performance is significantly better than ADO in this scenario.

Ed Metcalfe.

 

[William Vaughn]

JET can indeed outperform SQL Server or Oracle or any full-blown DBMS. Of
course it can. It's far, far simpler. A text file can out perform JET. Just
as a screwdriver takes less power to operate than a jackhammer but can it
cut through a foot of concrete? No but, it can be the right tool for the
job--as long as you don't ask too much of it and know how to use it.

I've seen good Access applications and good Access/JET developers and I've
seen sloppy, ignorant, uninformed and inexperienced developers for every
type of data access paradigm. Customers often can't tell the difference
until it's too late and they have made a career commitment to the consultant
or employee--then it's even harder to purge the old and move over to a
working solution.

I've also seen deadly serious SQL Server applications that should never have
been allowed to run. Good and bad applications are a function of the
developer, the dba, the architect and the people that manage them--not the
tools, engines and Visual Studio. The best tools in the world in
inexperienced hands can still build crap.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Michel Posseth [MCP]]

Bill

I get your point ,,,


However i was refering to the business solution Arvin described as it
sounded that this needed the power of a full blown rdbms so see my comment
in this context .


michel

 

[Arvin Meyer [MVP]]

Well, Bill, I am going to beg to differ with you. Small applications written
by unskilled "developers" who are really just users pressed into service by
necessity, cannot be compared with professionally designed and built
databases. I'm hate to tell you, but I've dealt with as many poorly design
databases written by professional developers who have no real database
skills, as I have with databases written by non-professionals.

Now to be specific. It may sound professional to bandy about phrases like
HIPAA and SOX (Sarbanes-Oxley) but the fact of the matter is that both are
mutually exclusive, and unless the personal patient data is also part of an
accounting or income reporting program, SOX doesn't have anything to do with
HIPAA. I have worked on both SOX and HIPAA Access databases, and both have
passed rigorous testing by independent auditors and IT professionals. All
that is necessary is to use Windows security and Group Policies to maintain
them. If the Windows servers aren't secure, then nothing written on any
Microsoft platform is secure.

What you say is only true when done by non-professionals, or by
professionals who are little more than code jockeys. That isn't the fault of
the medium as much is it is of the practitioner, and I can assure you that
there are thousands of server based databases which would never pass muster
with either SOX or HIPAA.

That said, there is no doubt that SQL-Server is a more robust platform than
Access/JET, or any other file-server based engine. No one ever said that it
wasn't. But to tell us that data is never lost with SQL-Server is simply not
true. I have seen more badly corrupted SQL-Server databases than Access
ones, and the data loss was much harder to replace. The data that was lost
was due to viruses (Slammer) or very poor administration (failing to
truncate logs when necessary, and poor power management) and the cost to the
companies was much more severe, both in time lost and recovery expense.

The Access/JET engine has it's place, as do server engines like SQL-Server
and Oracle. I have probably spent more time than you undoing poor Access
database design, but that doesn't make the tool at fault. Keep in mind that
there are more Access/JET databases, doing more combined work, than all the
server based systems combined. They are there for a reason, and that is
usually because there is an immediate need that can't be filled by larger
systems either because of time or budget. If Access wasn't there, it
wouldn't be done at all, and overall productivity would suffer.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

JET can indeed outperform SQL Server or Oracle or any full-blown DBMS. Of
course it can. It's far, far simpler. A text file can out perform JET.
Just as a screwdriver takes less power to operate than a jackhammer but
can it cut through a foot of concrete? No but, it can be the right tool
for the job--as long as you don't ask too much of it and know how to use
it.

I've seen good Access applications and good Access/JET developers and I've
seen sloppy, ignorant, uninformed and inexperienced developers for every
type of data access paradigm. Customers often can't tell the difference
until it's too late and they have made a career commitment to the
consultant or employee--then it's even harder to purge the old and move
over to a working solution.

I've also seen deadly serious SQL Server applications that should never
have been allowed to run. Good and bad applications are a function of the
developer, the dba, the architect and the people that manage them--not the
tools, engines and Visual Studio. The best tools in the world in
inexperienced hands can still build crap.

We are in total agreement on the above.