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

Re: Access vs SQL

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" <lgbjr@online.n ospam> wrote in message
news:exGmoIPPFH A.2144@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.]

Re: Access vs SQL

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" <lgbjr@online.n ospam> wrote in message
news:exGmoIPPFH A.2144@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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
>
>
>[/color]

[lgbjr]

Re: Access vs SQL

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-mar@nospam.nosp am> wrote in message
news:eTQdo0PPFH A.3928@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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" <lgbjr@online.n ospam> wrote in message
> news:exGmoIPPFH A.2144@TK2MSFTN GP09.phx.gbl...[color=green]
>> 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
>>
>>
>>[/color]
>
>[/color]

[Cor Ligthert]

Re: Access vs SQL

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]

Re: Access vs SQL

Scott,
[color=blue]
> Access does not support Stored Procedures but SQL Server does.[/color]

See in this procedure
http://msdn.microsoft.com/library/de...l/acadvsql.asp

Cor

[J L]

Re: Access vs SQL

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" <lgbjr@online.n ospam>
wrote:
[color=blue]
>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-mar@nospam.nosp am> wrote in message
>news:eTQdo0PPF HA.3928@TK2MSFT NGP09.phx.gbl.. .[color=green]
>> 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" <lgbjr@online.n ospam> wrote in message
>> news:exGmoIPPFH A.2144@TK2MSFTN GP09.phx.gbl...[color=darkred]
>>> 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
>>>
>>>
>>>[/color]
>>
>>[/color]
>[/color]

[Gerald Hernandez]

Re: Access vs SQL


"lgbjr" <lgbjr@online.n ospam> wrote in message
news:exGmoIPPFH A.2144@TK2MSFTN GP09.phx.gbl...[color=blue]
> Hello All,
>
> I've been developing a VB.NET app that requires the use of a DB. Up to[/color]
now,[color=blue]
> 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[/color]
DB[color=blue]
> 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[/color]
quite[color=blue]
> a bit with Access.
>[/color]
<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]

Re: Access vs SQL

> My basic concern is not my lack of SQL Server knowledge. I just felt that[color=blue]
> 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[/color]
thinking[color=blue]
> is based on what my app does today.[/color]

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]

Re: Access vs SQL

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" <lgbjr@online.n ospam> wrote in message
news:exGmoIPPFH A.2144@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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
>
>
>[/color]

[aatcbbtccctc@yahoo.com]

Re: Access vs SQL

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]

Re: Access vs SQL

> After posting this, I'm going to install SQL2KDesktop (MSDE) and try to[color=blue]
> 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.[/color]

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]

Re: Access vs SQL

>[color=blue]
> 1. Access is *not* a database.[/color]

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]

Re: Access vs SQL

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

<aatcbbtccctc@y ahoo.com> wrote in message
news:1113106897 .126133.12200@o 13g2000cwo.goog legroups.com...[color=blue]
> 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
>[/color]

[Cor Ligthert]

Re: Access vs SQL

lgbjr,
[color=blue]
>And I also agree that MS Access as a product is not a database, it's an IDE
>for developing a DB front end.[/color]

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