Access vs SQL Server

[BobC]

What are major differences in MS Access vs SQL Server,
e.g. security, performance, recovery, etc?

 

[Douglas J. Steele]

Access is a file-based application. File-based applications cannot be
completely secured: anytime users have the ability to get directly at the
file, they have the ability to hack it. Access has no built-in logging, so
there's no recovery built in.

SQL can handle much larger databases, with more concurrent users. It's a
server-based application, so it has much better security.

Of course, SQL Server is considerably more expensive than Access. As well,
you can't built your application in SQL Server, as it's only a database,
with no capability to build user interfaces.

 

[Kevin @ 3NF]

SQL Server's built-in backup and recovery technologies blow Access away.
You have to set them up according to the amount of pain you are willing to
accept if you lose the database, but it isn't that difficult. Performance
is better with SS in a multi-user environment, security is better, etc.

Approximate cost of SQL Server, 5 users: $1500 + cost of whatever front-end
tool you use
Approximate cost of 5 Access licenses: $1145 from Microsoft's site

 

[Ben]

An option, if you are unable/unwilling to pony up for a
SQL server is to use MSDE. Its on the Office 2K/XP CD,
but is not installed by setup. MSDE is SQL Server without
some of the Enterprise features, for example it can't act
as a replication publisher (but can be a subscriber).
Check out:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnmsde/html/msdeforvs.asp
and
http://support.microsoft.com/default.aspx?
sd=msdn&scid=kb;en-us;324998
or search MS for more info.
I use MSDE all the time for prototyping and for
departmental apps. It works great and is easy to roll up
to SQL server if you need to (not true for Access in my
experience).
HTH