SQL Server Express versus Access (In the World Series of Love)

[dch3]

Question - since SQL Server Express is available for free, why WOULDN'T it be
better to use it in stead of Access for a database backend? Other than
overall ease of portability which is minimal (copy & paste a file as opposed
to backup/restore) and system requirements, I can't think of too many other
reasons.

Feedback...

P.S. The title of the post is a reference to 'U Got The Look' by Prince &
Sheena Easton...its something that stuck with me for twenty some odd years
(yes, I probably need some shock treatments.)

 

[Brian]

Here's one: creating/maintaining table relationships by drag-and-drop within
the Access development environment. A definite convenience during
development. Not necessarily a reason to switch, though.

 

[david]

A reason. Not a definitive reason, just a reason.

Jet transactions are broken against SQL Server. Access QBE
query design grid doesn't work on SQL Server. If you want to
use Access with transactions with a SQL Server backend, you
have to work in two development environments.

(david)

 

[Dirk Goldgar]

Question - since SQL Server Express is available for free, why WOULDN'T it
be
better to use it in stead of Access for a database backend? Other than
overall ease of portability which is minimal (copy & paste a file as
opposed
to backup/restore) and system requirements, I can't think of too many
other
reasons.

I think you're overstating it in saying that portability of SS-Express is as
simple as backup and restore. Doesn't SQL Server Express have to be
installed on the target computer? Just transferring the file will not work.
Jet support (for the MDB file format) is built into the operating system
these days, so you don't have to install anything to use a Jet database. If
your front-end is an Access application, then yes, you need to install
Access (full version or run-time). However, using other development tools
you can program against the Jet database without even having Access even
installed.

That said, it wouldn't surprise me at all to find SQL Server supported
directly by the operating system in some future Windows version, in which
case this particular distinction may disappear.

 

[Michel Walsh]

First, nice to make a difference between Jet (the db engine) and Access (an
application USING a db engine). After all, you CAN use Access with MS SQL
Server too (with linked tables).


Copy is easier than Detach/Attach, so installation is easier with Jet.
Advantage to Jet.

Both are limited in size to 2Gig. No advantage.

VBA is of more general purpose than T-SQL. Advantage to Access for
debugging, speed of development, leverage of knowledge (if you already know
VBA; while T-SQL is useful to nothing outside MS SQL Server).

VBA is better integrated to Access than CLR to MS SQL Server Advantage to
Access, if you use Access. Advantage for MS SQL Server if you use another
application AND know a framework language (VBNet, C#, Managed C++, ... ).

MS SQL Server has better tool with the framework than Jet (which has close
to nothing). Advantage to MS SQL Server if you use DotNet or LINQ.

Speed of execution: Jet is faster if the db is on the same PC (no log to
slow you). Advantage for Jet.

Speed of execution is probably faster with MS SQL Server on queries
returning aggregated data. Advantage for MS SQL Server.

No log with Jet, Access requires a continuous connection. Advantage to MS
SQL Server if your link to the db may be 'disconnected'.

Updateability through a join (view). Advantage to Jet.

Updateability implying an aggregate. Advantage to MS SQL Server.

Recursion, hierarchy, non traditional data: Advantage to MS SQL Server 2008


Access and Jet makes a very well integrated environment (so good that few
people makes the difference between both) for development (even supplying
its own forms). Advantage to Jet ( with Access) for classical database
development. Even if Visual Studio 2008 + MS SQL Server can almost do the
same with the same development time, it is not as intuitive and still come
short on some points (example, Updatability, with LINQ) in comparison with
Access + Jet.




probably more, those are the ones coming to mind without thinking much.

Vanderghast, Access MVP

 

[dch3]

I'm a bit concerned that most of the posts seemed to deal with the interface
and not the inner workings of the underlying Engines - perhaps the thread
should have been entitled SSE versus Jet - as I now realize that was the
question at hand. No one mentioned that SQLServer supports triggers (how many
times have you had a summary table that you've had to update a value when the
detail changes? and how often do you have to remember that if you build a new
screen that allows the detail to be edited that you also have to call the
code that updates the corresponding header record), or the fact that SQL
Server security can be integrated with Windows/Active Directory - not to
mention that it can be setup at the COLUMN level, I would assume that
upsizing from SQL Server Express to Big Daddy SQL Server would be easier
(than Access to SQL Server) as Express is for all basic purposes SQL Server
with limitations (only 1 processor, max database size 4GB).

 

[Michel Walsh]

As far as the triggers are concerned, the first case is something to avoid
(storing computed values), unless you want to archive something, but then,
if you archive your data, you don't expect data to change and have other
mechanics to cover such exceptional cases. For the second example, it is
something related to the UI, not to the database engine. Sure, triggers may
have their usefulness, for updateability, but Jet has already updateability
covered automatically over saved query while MS SQL Server requires INSTEAD
OF triggers to achieve the same, say, over view, so, advantage to Jet. Sure,
MS SQL Server may have a horse shoe maker right out of the box, and Jet does
not, but if we don't own horse... is that an advantage?

Vanderghast, Access MVP

 

[dch3]

Ah, but storing computed values while not the theoretical ideal is sometimes
necessary. Case in point if you're working with a hotel reservation system
and need an screen that displays rooms available for sale a summary table
would be acceptable as the amount of time necessary to compute the
availability (or quantity sold) would take (seemingly) forever.

 

[Michel Walsh]

If your need involve a form, then triggers are not an appropriate tool: they
can do something about the database, but they won't ... trigger ... any
action in the form itself, after a change in the database. The trigger
domain of influence, scope, is limited to the database realm. An EVENT, a
form event, can solve the problem but that is the domain of the User
Interface, not of the domain of the database. Sure, you supplied an example,
and I end up with a discussion about the example rather than on the object
itself, "a trigger", but like Joe Celko, I claim that triggers are quite
often a bad choice, to not say an inappropriate choice, or just half a
solution that cannot be completed efficiently. Again in the case of a form
displaying summary, VBA-events based solution could be simpler and more
appropriate than trigger and ... and what would you use to redisplay the
form when the trigger action, in the database, completed its intended job?


Vanderghast, Access MVP