access vs SQL Server

I have quite a bit of experience using Access, and the limitations on Access
caused me to upgrade to SQL Server. My advice -- forget Access. I cannot
imagine a reason to use Access for any project with more than 1 user, and
even with 1 user if there is lots of data being processed. Access is good
for your checkbook or something like that, but you should completely forget
about it otherwise. Write a VB front end for the DB, and do not allow Access
users to link to your tables. Writing an app with forms and complex queries
is just as easy using SQL Server as it is in Access. A Database App is a
Database App for any good DB. The App doesn't vary much from one DB back
end to the other, and a really well written App could work off several DB
back ends with little or no modification. By the phrase "any good DB", I
do not include Access. I mean SQL Server, Oracle, DB2, etc.

Some of the people responding to your question refer to the "Access skill
set". Forget that. The form engine in Access is no easier to use than the
form engine in VB.

MSDE is just a cut-down version of SQL Server. Use it if you do not have
lots of concurrent users, and if the DB size is small enough.

These attitudes were formed by the experience of moving projects created
in Excel to Access. The performance improved, but when we went to SQL Server,
using stored procedures, we got more than an order of magnitude improvement.
Writing, maintaining, and modifying the Apps is easier. Control of user
access to the data is easier. Etc. I learned the hard way. Access sucks
for anything but 1 person & 1 connection processing.

- Ray Clough

amen brother

Access has it's place...just like any other tool. Multi-user systems can
be written, written successfull, and work...if written correctly. Access
provides a avenue for quick development and in some cases prototyping...at
a much lower cost for small organizations that the cost ofa VB/SQL Server
solution. Not every organization has the expertise and/or the resources
to manage a SQL Server box. MSDE offers a level of 'intermediate' functionality,
but if your client needs the ability to possibly modify the system you write
for them, MSDE presents a major problem because you'd then have to write
tools for that purpose if the client wasn't familiar with or if they didn't
have the tools/skills to deal with the database.

Just depends on the project at hand, in most cases SQL Server would be a
better choice...but no database offers the end all solution to a particular
problem or project.

Chris

I agree that Access has it's place, but I don't believe that it's place is
in a production environment. I suppose that it's relevant to the importance
of the data that you are storing in the db...and the application that it
supports.



"Chris Hylton" <chrishylton@excite.com> wrote in message
news:3b8d4329$1@news.devx.com...
>
> Access has it's place...just like any other tool. Multi-user systems can
> be written, written successfull, and work...if written correctly. Access
> provides a avenue for quick development and in some cases prototyping...at
> a much lower cost for small organizations that the cost ofa VB/SQL Server
> solution. Not every organization has the expertise and/or the resources
> to manage a SQL Server box. MSDE offers a level of 'intermediate'
functionality,
> but if your client needs the ability to possibly modify the system you
write
> for them, MSDE presents a major problem because you'd then have to write
> tools for that purpose if the client wasn't familiar with or if they
didn't
> have the tools/skills to deal with the database.
>
> Just depends on the project at hand, in most cases SQL Server would be a
> better choice...but no database offers the end all solution to a
particular
> problem or project.
>
> Chris

"Chris Hylton" <chrishylton@excite.com> wrote in message
news:3b8d4329$1@news.devx.com...
>
> Access has it's place...just like any other tool. Multi-user systems can
> be written, written successfull, and work...if written correctly. Access
> provides a avenue for quick development and in some cases prototyping...at
> a much lower cost for small organizations that the cost ofa VB/SQL Server
> solution. Not every organization has the expertise and/or the resources
> to manage a SQL Server box. MSDE offers a level of 'intermediate'
functionality,
> but if your client needs the ability to possibly modify the system you
write
> for them, MSDE presents a major problem because you'd then have to write
> tools for that purpose if the client wasn't familiar with or if they
didn't
> have the tools/skills to deal with the database.

Or they could buy MSDE Admin. It worked really well for a client of mine
that didn't want (to pay for) SQL Server proper. It gives them most of
what you get from enterprise manager for35.00

I am in no way related to the company that wrote this product. They just
saved me having to spend a couple weeks writing my own mini enterprise
manager

I'm not trying to say that (if possible) you shouldn't choose SQL or MSDE
over Access...just that you should choose the right tool for the client...especially
if the client has requirements that can't be met by SQL Server. There are
those cases out there. Anyway, that was my only point. The original couple
of posts here seem to indicate that SQL is always the answer and that's obviously
NEVER the case, there are always exceptions. And, some of those exceptions
may require some level of multi-user interaction and/or protection of the
data.

I have my issues w/ Access just like everybody else, but to make a statement
that would steer someone reading this thread into purchasing or building
a app that presents more expense to their client or their company than necessary
or that they want isn't exactly acting w/ the clients best interest. Yes,
you could argue that the client's 'best interest' is to choose an industrial
strength database. It's just not always necessary.

If you are talking the best 'technical choice' here, then yes...I'd have
to agree. But, there is a 'logistical' and 'financial' component with every
project you undertake as well.

Chris

"The form engine in Access is no easier to use than the
form engine in VB."

This was very funny ! :))) I think that you don´t have to use it yourself...


Elias

Ray
In general, I almost completely agree with your sentiment. However, there
are a few cases where Access is more flexible than SQL. When I came to my
present company, I had to have my app tie into a set of dlls that provide
contact/pim functionality. These dlls were driven on top of Access, in a
multi-user environment. I suggested moving to SQL Server/MSDE, but I had
the "straight dope" given to me. First, our apps are actual production commercial
apps, not apps that are used by our own compnay internally. We have over
3000 customers using these apps, many of which are sales-people, and the
PIM functionality of each app had to support both connected and disconnected
access. I said sure, SQL Server/MSDE can do that with Merge Replication,
and probably do it more quickly. However, I came to find out that the actual
load on the Access database was very minor, and the dll's used something
called JRO (Jet Replication Objects) to support the replication. The deployment
was simple.. simply MDAC 2.5
I have since changed my opinion. For this *ONE* case, Access is the best
solution of the tools that I have available. Our customers cannot afford
SQL Server licenses. That leaves us with MSDE. As a SQLServer/MSDE veteran,
I do not want to support the deployment and setup of databases to over 3000
locations, remotely. On top of that, I don't want to have to configure each
database for Merge Replication. The Access solution is simple, and it works.
It DOES run in a multi-user environment with very good performance (although
the database and its load are extremely small), so MSDE would have been an
overkill.

Just wanted to share my experience
Trey Hutcheson