Re: Access vs SQL


"lgbjr" <lgbjr@xxxxxxxxxxxxx> wrote in message
news:exGmoIPPFHA.2144@xxxxxxxxxxxxxxxxxxxxxxx
> 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


.

Relevant Pages

  • Re: Poly Couples
    ... Essentially - different versions for different clients. ... insist that you use their existing database which is from a different ... vendor, and the structure is different than the one you use originally. ... characters would mark variable insertion place-holders in the SQL. ...
    (comp.object)
  • Re: "Automated" updates..
    ... a SQL DB call is made every few seconds and it's causing ... new HTML file/s whenever the database updates, ... The software that updates the HTML will normally require far less ...
    (comp.lang.javascript)
  • Re: Like I said...
    ... In the end, SQL and DB is the only place where everyone understands everyone, in a clear, unambiguous way, right from the start. ... DB updates are performed on a "single ... The cases where we encountered them had PC clients running the app via Application Mode, so that was not a bother. ... Usually the only customers where auto-update is still allowed are the small ones -- where there aren't enough clients to make autoupdates really useful:p -- those with large IT droids services have it forbidden, ...
    (borland.public.delphi.non-technical)
  • Re: Opinions needed about the best "Middleware suite" kbmMW vs. RODA
    ... >> record it deletes all detail records, and of course when you apply the ... >> updates to the database, it deletes first the detail records then the ... > properties that allow you to control how cascade deletes and updates work. ... so it could be used with diffrent types of clients. ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: Programatically adding Fields to an existing Database (using SQL)
    ... database to new clients, ... This piece of code is expected to return the datatype of the field ... I found SQL to be the only way to add the field on the fly but I ...
    (borland.public.delphi.database.ado)