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/de.../usingmsde.asp

http://www.microsoft.com/downloads/d...DisplayLang=en

Ken
--------------------
"lgbjr" <lg***@online.n ospam> wrote in message
news:ex******** ******@TK2MSFTN GP09.phx.gbl...
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" <lg***@online.n ospam> wrote in message
news:ex******** ******@TK2MSFTN GP09.phx.gbl...

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

[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
"Scott M." <s-***@nospam.nosp am> wrote in message
news:eT******** ******@TK2MSFTN GP09.phx.gbl...

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" <lg***@online.n ospam> wrote in message
news:ex******** ******@TK2MSFTN GP09.phx.gbl...

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

[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/de...l/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

On Sat, 9 Apr 2005 20:29:05 +0800, "lgbjr" <lg***@online.n ospam>
wrote:

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
"Scott M." <s-***@nospam.nosp am> wrote in message
news:eT******* *******@TK2MSFT NGP09.phx.gbl.. .

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" <lg***@online.n ospam> wrote in message
news:ex******** ******@TK2MSFTN GP09.phx.gbl...

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

[Gerald Hernandez]

"lgbjr" <lg***@online.n ospam> wrote in message
news:ex******** ******@TK2MSFTN GP09.phx.gbl...

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

"lgbjr" <lg***@online.n ospam> wrote in message
news:ex******** ******@TK2MSFTN GP09.phx.gbl...

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