Access vs SQL

[lgbjr]

Hello All,

I've been developing a VB.NET app that requires the use of a DB. Up to now,
I've been using Access. It's a bit slow, but everything works. I'm at a
point now where I need to decide if I should stay with Access or move the DB
to SQL. I'm trying to come up with a list of Pros/Cons for such a move. My
list is a bit lopsided, as I have very little experience with SQL and quite
a bit with Access.

PROS for moving to SQL:
Increased Performance?
Increased Reliability?
Lifecycle of Access?
Future Access Version compatibility issues?

CONS for moving to SQL:
My limited knowledge of SQL
Clients not required to have an SQL server

I've added a few items to the PROS list, but with ?s, as I don't really
know.

If there are a few Access advocates and SQL advocates out there that could
give me some viewpoints, I'd be more comfortable making a decision based on
the facts, rather than my limited knowledge.

TIA
Lee

 

[Ken Tucker [MVP]]

Hi,

The MSDE is a free scaled back version of sql server. So the
client isn't required to buy sql server. If you design an app using msde
and later need the networking features of sql server it is real easy to
upgrade you app.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/usingmsde.asp

http://www.microsoft.com/downloads/...03-c4ba-4d98-bb0b-2c9d6414071f&DisplayLang=en

Ken
--------------------
Hello All,

I've been developing a VB.NET app that requires the use of a DB. Up to now,
I've been using Access. It's a bit slow, but everything works. I'm at a
point now where I need to decide if I should stay with Access or move the DB
to SQL. I'm trying to come up with a list of Pros/Cons for such a move. My
list is a bit lopsided, as I have very little experience with SQL and quite
a bit with Access.

PROS for moving to SQL:
Increased Performance?
Increased Reliability?
Lifecycle of Access?
Future Access Version compatibility issues?

CONS for moving to SQL:
My limited knowledge of SQL
Clients not required to have an SQL server

I've added a few items to the PROS list, but with ?s, as I don't really
know.

If there are a few Access advocates and SQL advocates out there that could
give me some viewpoints, I'd be more comfortable making a decision based on
the facts, rather than my limited knowledge.

TIA
Lee

 

[Scott M.]

You've got a CON for SQL that "Clients not required to have an SQL server".
I'd say that's a PRO.

Here are some other major issues to consider:

Access has limited concurrent connection support while SQL Server was
designed as a server, so it supports many concurrent connections.
Access does not support Stored Procedures but SQL Server does.
Access does not implement any db security (beyond a db password), but SQL
Server has a robust security model.

 

[lgbjr]

Scott and Ken,

For staying with Access, you're right, that should be a PRO.

Thanks for the tips. I guess I sort of knew these things, just from the
reading I've done. And, I'm almost certain that the right long term decision
is to move to SQL Server. I think I might take Ken's advice and take a baby
step in what is probably the right direction by using MSDE to start.

My basic concern is not my lack of SQL Server knowledge. I just felt that
using SQL Server for what my app needs was a bit of overkill. And, thus,
requiring my clients to purchase SQL Server seemed wrong. But, this thinking
is based on what my app does today.

So, no decision yet, but, I think MSDE gives me the easy scalability for the
future, without the overkill of SQL Server today.

Thanks!

Lee

 

[Cor Ligthert]

lgbjr,

Not that I am against any database however the major pro for me from access.
And that only because that I have not seen it yet.

Access is very easy to install and it is portable.

Cor

 

[Cor Ligthert]

Scott,

Access does not support Stored Procedures but SQL Server does.

See in this procedure
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acadvsql.asp

Cor

 

[J L]

Hi Igbjr,
I am in exactly the same situation as you. My biggest concerns about
SQL or any other server based DB are (1) the initial
installation/setup issues and (2) the skill level required by my
customers to maintain it. My customer base is the food industry and
for the most part they do not have IT departments and DBA's in the
packing plants to support "high technology". And it does not have to
be very "high" to exceed their limits.

I have used Access for many years and found it easy on both accounts.
Since my databases are only accessed programatically and I do not use
bound controls but open/close very quickly, I have never ran into
concurrency issues. I have also not found problems with MDB file size
yet but that is my major concern.

In addition to MSDE as a starting point, I am also considering MySql
and Firebird.

So I will continue to lurk this thread and hope you get more insight
form the Gurus. Also would appreciate hearing how you will make your
decision to move away from Access.

John

 

[Gerald Hernandez]

Hello All,

I've been developing a VB.NET app that requires the use of a DB. Up to now,
I've been using Access. It's a bit slow, but everything works. I'm at a
point now where I need to decide if I should stay with Access or move the DB
to SQL. I'm trying to come up with a list of Pros/Cons for such a move. My
list is a bit lopsided, as I have very little experience with SQL and quite
a bit with Access.

<Snipped>

As you have found, sometimes there is no clear winner out of the gate.
As others have mentioned, let's add the 3rd option of MSDE, so the basic
choices we are considering are Access, MSDE, and SQL.
Each of those options can be made to do just about anything you need, with
increased options as you go up the list.
So I propose that instead of basing your decisions on the merits of each DB
engine, consider the needs of your application and customers. Then go down
the list and see which one fits each item and score it.

How many users will need to access the database simultaneously?
Are you using the database primarily as a storage place, or do you have many
updates?
How much data are you storing?
How processor intensive is your application?
Do the customers need to use and/or manage the database outside of your
application?

Access:
Best for single user. But can support many users if necessary.
Pros:
Works great as a storage container. Works best for smaller amounts of data,
say dozens of MB. Although you can get close to 2GB.
Fairly decent if your application is processor intensive.
Works great in low memory environment.
Easy to manage.
Very portable and easy to install just about anywhere.
Cons:
If you have many updates then you need to constantly compact it. The 2GB
limit can be reached fairly easily if you have lots of updates.
Inefficient across a network.
Version updates can be problematic.
Best performance if you use DAO. Future upgrade to MSDE or SQL is not
seemless and requires many code changes.
Can use ADO for near seemless upgrade to MSDE or SQL, but performance is
much less than DAO.

MSDE:
Best for 2-5 users. Can support more, but don't exceed 25.
Pros:
Handles many updates more efficiently than Access.
Can store up to 2GB per database.
No additional cost to your clients.
Near seemless upgrade to full SQL. Usually few, if any, code changes needed.
Cons:
If your application is processor intensive, then you should set up a
seperate server machine. If loaded on the same machine, then the processor
requirements of your application could negate any performance gains.
Requires more memory. If your database is small, can be less efficient than
Access.
Designed to be managed completely by your application. Not easy for clients
to manage.
Less portable than Access.

SQL:
Best for 6+ users.
Pros:
Handles queries and updates very efficiently.
Can store up to 2GB+ (depending upon version, OS, etc) per database.
Can be easily managed and accessed by your clients.
Cons:
Should really have a seperate machine, preferrably a server, dedicated to
it.
Requires a great deal of memory.
Can require significant additional cost to your clients. Need Server OS and
client licensese, SQL Server software, server license, plus client licenses.
I wouldn't consider it portable at all.


It is quite possible that moving up to MSDE might be a great choice.
However, if you really don't need it, it is also quite likely that with
tweaking you could significantly improve the performance of your Access
code. Quite often, simple things like the choice of your Cursor location and
recordset type can have large performance differences when using Access.

Hope this helps.
Gerald

 

[Richard Myers]

My basic concern is not my lack of SQL Server knowledge. I just felt that

using SQL Server for what my app needs was a bit of overkill. And, thus,
requiring my clients to purchase SQL Server seemed wrong. But, this thinking
is based on what my app does today.

You can administer MSDE through an Access front via *.adp Projects. There
are also a bunch of free utilities out there to do this for you.
Your right to base your decision on what your application does today not
tomorrow..... thats why you undertake a requirements definition and
vigilantly guard against *creeping scope*.
..
From a coding standpoint the best thing you can do re: future proofing is
too ensure you have proper separation of your data access layers.

A change of DAL 18 months from now should not neccessitate a complete
rewrite. In fact it could be considered a marketable feature/benefit
if you design your DAL correctly using say the *Provider Pattern* that your
application can run on the *customers* choice of data store - XML, my sql-
whatever - this could be decided on a per client basis ( subject to your
support preferences of course).

I appreciate you're on a knowledge quest but if you really want future
proofing Id start with application design, proper nTier development and
pattern usage. If you do this correctly choice of datastore should almost
be as easy as plug and play.

Richard

 

[lgbjr]

Hello All,

Thank You for your responses! All of you have given me quite a bit to think
about!

Richard, I understand completely. A bit of background: Even though I am
developing a software package, I would not, by any stretch of the
imagination, consider myself a software developer. I'm an Engineering
Consultant for the Mobile Communications Industry. For the past 10 years,
I've been writing PERL apps (when necessary) to automate repetitive tasks
and make my life easier. These apps were for my use only. Over the years, I
have had several clients ask me to provide these small apps to them. So, I
moved from writing plain code, to writing an app with a user-friendly
interface. Now, my clients want more. And they're willing to pay for it
(above and beyond what they pay for my engineering services). Fortunately, I
knew enough to write a scope of work (basically for myself), just as I would
do for a typical engineering project. So, I do understand 'creeping scope'.

Regarding the ability to 'Plug and Play' a variety of DB engines, I have
considered this as well. At the beginning of this project, I was using C1
Express components (no data adapters, no datasets, just Express Tables and
an Express connection (OLEDB)). While this was extremely easy to implement,
I realized that there was no upgrade path (upgrade = rewrite). Now, still
using C1 components, I know (think) I can upsize to MSDE or SQL Server with
only minor code changes.

Gerald, I agree. Rather than trying to decide which DB engine is better,
it's safe to say that all are good, if used in the environment they were
designed for. Right now, wihtout thinking about growth, I could deploy with
Access (1-2 users performing updates, additional 3-4 users viewing data,
approximately 1-1.5GB size). But, this, based on what I have read, and your
comments, is near the upper limit of what Access was designed for.

JL, though it may not seem so, I am in a similar situation regarding IT
support. Even though all of the mobile operators have extensive IT
departments, the engineering and IT departments are always fighting. So, I
need my app to be self-maintainable.

After posting this, I'm going to install SQL2KDesktop (MSDE) and try to get
a feel for what it will take to do a migration (I already have SQL Server
running on another machine). I like Richard's idea of allowing the client to
decide which DB engine to use. Hopefully my coding and forward-thinking was
robust enough to allow this with only some minor tweaks. However, if I get
lost in a sea of errors, I think my backup will be to use MSDE.

Again, thanks for your insights!!

Regards,
Lee

 

[aatcbbtccctc]

Haven't read all the replies, so apologies if this has aready been
said:

1. Access is *not* a database. It is a front-end IDE. The underlying
database for Access is normally MS Jet, a comlementary but completely
seperate product. You can have an Access front-end with any compatible
database including but not limited to Jet, Oracle, SQL Server, etc.
Conversely, you can use a Jet database with any complementary front-end
including but not limited to Access, VB, etc.

2. SQL also is *not* a database. SQL is a data access language which is
used in many databases, including but not limited to Jet, SQL Server,
Oracle, etc.

IOW, please let's get the terminolgy right

HTH,
TC

 

[Steve]

After posting this, I'm going to install SQL2KDesktop (MSDE) and try to

get a feel for what it will take to do a migration (I already have SQL
Server running on another machine). I like Richard's idea of allowing the
client to decide which DB engine to use. Hopefully my coding and
forward-thinking was robust enough to allow this with only some minor
tweaks. However, if I get lost in a sea of errors, I think my backup will
be to use MSDE.

You might want to check this out too Lee

http://www.asql.biz/DbaMgr.shtm

It's a very nice (and free) admin GUI for MSDE. Very useful since MSDE comes
with nothing

Steve

 

[Cor Ligthert]

1. Access is *not* a database.

In my opinion is this sentence wrong.

It is not a database *server* however it is a database system in a file.

I hope this helps,

Cor

 

[lgbjr]

I agree, and apologies. I do understand the difference between SQL
(Structured Query Language) and an SQL Server (which provides a database
environment in which SQL is used to access and manipulate data). And I also
argree that MS Access as a product is not a database, it's an IDE for
developing a DB front end.

However, I think everyone that has replied to my post understood what I was
talking about (I hope)

Either way, I'll try to be more careful with my wording.

Cheers
Lee

 

[Cor Ligthert]

lgbjr,

And I also agree that MS Access as a product is not a database, it's an IDE
for developing a DB front end.

As I wrote in my previous message this is not true, I have the idea you are
talking about the MS office Access product. You can create an access
database using ADO in your program as a part of your development.

Cor

 

[lgbjr]

Cor,

I think this is a symantics issue. I will agree that throughout the user
populance, many people refer to Access, MS Access, an Access DB, etc. as
being a database. While this has been / is acceptable for most people, I
believe what TC said is technically correct.

Most people open MS Access, create some tables, queries, forms, modules,
etc. and the language used to drive the front end is VBA (forms, modules,
etc). Most are unaware that the actual DB engine is not Access (as there's
no such thing), it's Jet (by default).

After loading MSDE, since it doesn't have a GUI, I am using MS Access, via
an ADP project, to make modifications to the MSDE DB (SQL Server DB). If MS
Access were, as you're implying, a database engine, you wouldn't be able to
change database engines in the product. Therefore, MS Access should really
be thought about and talked about as an IDE that supports several DB engines
(similar to VS.NET being an IDE that supports several programming
languages).

Again, I believe this is a symantics issue. Everyone knows/knew what I was
talking about, but, from now on, when I catch myself refering to an Access
DB, I'll replace that with a Jet DB with a front end built in MS Access, or
whatever DB engine I happen to be using.

Of course, if I talk to my clients about a Jet DB, I'll just get blank
stares and questions about what happened to the Access DB. ;-)

Cheers
Lee

 

[Cor Ligthert]

Lee,

An answer without words,

http://support.microsoft.com/default.aspx?scid=kb;en-us;823927

))

Cor

 

[aatcbbtccctc]

Not so, Cor.

MS Access does not contain any of the base technologies for creating &
managing tables & indexes; parsing, optimizing & executing SQL; or any
of the other things that database products have to do.

Those technologies all reside in a completely seperate product, MS Jet.
Access uses the Jet API to create & maintain tables & indexes, execute
SQL, and so on.

Access does ask Jet (via a Jet API) to create, within the MDB file,
some containers for Access to store its own things (forms, reports
etc.) - but that does not make Access, a database.

I suspect your mistake is in believing that an MDB file is an *Access*
file. It is not - it is a *Jet* file, in which Access is able to store
its own things (in addition to the standard Jet things).

A VB program can use a Jet MDB file for database storage; but VB is not
Access, and does not require Access to be present in any way, shape or
form.

HTH
TC

 

[aatcbbtccctc]

You can create an access database using
ADO in your program as a part of your
development.

That is not an Access database. It is a Jet database. It does not
require MS Access to be present (before, now, or in the future) on the
PC.

HTH,
TC

 

[aatcbbtccctc]

Microsoft's use of the term "Access Database" in that article, is
incorrect.

If I happened to mis-spell my own name, that would not make the
mis-spelling correct!

HTH,
TC