SQL Server and Microsoft Access

B

Bob Rice

Some very stupid questions:

It is my understanding that SQL Server is a relational database. If so,
what is the difference between it and Microsoft Access?
What would indicate that one is better for an application than the other?
Is SQL a standalone software that can be purchased like Access?
 
A

Arvin Meyer

Access is more than just a database engine (the JET db engine). It includes
the ability to make forms, reports, and write VBA code and macros. It is
limited in size to 2 GB (although practically significantly less) and 255
users (also practically, significantly less). For most database applications
this is not a significant limitation.

SQL-Server is a database engine only. There is an interface to build tables
and queries, and some extensions to the SQL language in the form of T-SQL
code. It has more datatypes than Access's Jet, but there is no ability to
use VBA code within queries like Access, and there is no built in capacity
for forms or reports. SQL-Server's engine runs as a server so it is far more
robust than the JET engine, and can service thousands of users and Terabytes
of data. Although it costs more to develop and deploy a SQL database, it
generally is not significant when there are large numbers of users. There is
a desktop verson of SQL-Server that is somewhat less expensive to build
databases than the full version. The desktop version has similar capacities
to Access but tends to be slower with more than 5 users since it has been
throttled back. SQL-Server, being a server based engine is also more secure
than JET, so it should be used for public financial and medical database
requirements.

Based on my experience as a database developer with both systems,
approximately 85 to 90% of all database can probably be built in Access JET
with maybe 5 to 10% of those eventually being upsized to SQL-Server, keeping
the front-end in Access. The rest should start out in SQL-Server and stay
there.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
R

Rick Brandt

Bob said:
Some very stupid questions:

It is my understanding that SQL Server is a relational database. If
so, what is the difference between it and Microsoft Access?
What would indicate that one is better for an application than the
other? Is SQL a standalone software that can be purchased like Access?
Click to expand...

Access is a RAD tool for creating database applications. By default is uses the
Jet (file based) database engine. The marriage between the Jet engine and the
Access application is so tight that the term "Access Database" has become common
vernacular, but in reality "Access" is not a database at all.

In contrast SQL Server is a server based database engine period. To create a
database application with SQL Server as the database engine you still have to
use something to create the user interface. That can be VB, Access, C++,
dot-net, or any of a variety of tools that create web-based interfaces.

As a general rule server based engines scale better, have higher capacity,
security, and data integrity as well as support things like triggers and stored
procedures. If those are a priority for an application then SQL Server is a
better choice than using Jet (or any other file-based db engine). A server
based engine though might require a proper server to run on and if used for a
large enough data store that is mission critical to the organization might
require a dedicated person to support the server and database. That means it
can be a much bigger investment than a solution like Jet.
 
Top