SQL Server Express vs MS Access

sql-server ms-access sql-server-express
scarpacci picture scarpacci · Apr 18, 2011 · Viewed 53.7k times · Source

A colleague I work with recently told me that SQL Express and MS Access were essentially the same thing; that does not seem to be an accurate statement. I know you can convert Access to a SQL DB and maybe under the covers they are similar, but I would assume that the SQL DB engine and what is used to run access are not the same. Not only that, but the SQL statement syntax, etc. I know are not the same.

I am mainly trying to understand so that I am more informed about the versions.

Answer

mwolfe02 picture mwolfe02 · Apr 18, 2011

Um, no, not the same.

First off, I need to clear up some terminology. MS Access is a Rapid Application Development (RAD) tool that allows you to quickly build forms and reports that are bound to relational data. It comes with a file-based database engine (Jet/ACE).

Access the RAD tool can be used with many different backend databases (Jet, SQL Server, any db that supports ODBC, etc). I have to assume your colleague was specifically commenting on Jet/ACE, ie the database engine that MS Access uses.

I think the single biggest difference between the Jet/ACE database engine and MS SQL Server Express is that Jet/ACE is file-based and SQL Server Express uses a client/server model. This means that SQL Server Express requires a running service to provide access to the datastore. This can complicate deployment in some scenarios.

SQL Server Express is really just a throttled-back version of SQL Server: max database size of 4GB (10GB in 2008R2), only uses a single physical CPU, etc. These limitations are imposed to prevent large organizations from using the freely available Express edition in place of a full-blown SQL Server install. The upshot to this is that SQL Server Express offers a truly seamless upgrade path to SQL Server. It is also (generally speaking) a more robust and fully featured database management system then Jet/ACE.

Similarities

  • relational database management systems
  • written by Microsoft

Differences

  • MS Access
    • File based
    • free distributable runtime (2007 or later)
    • RAD tools (form/report designer)
    • uses Jet SQL
    • max file size 2GB
  • SQL Server Express
    • Client/Server model
    • free
    • no RAD tools
    • uses Transact-SQL
    • max database size 4GB (10GB for SSE R2), max one physical CPU

Related questions

MS Access call SQL Server stored procedure

I have an MS Access application that contains all tables linked to SQL Server, so in MS Access VBA code or query I work with those tables very simple, I access them via name, like [Customers]. Also I have a …

Read More
How do I create a table alias in MySQL

I am migrating an MS Access application (which has linked tables to a MSSQL Server) to MySQL. As a means to overcome some MSAccess table naming problems, I am seeking a solution to add a MySQL table alias that will …

Read More
Editing Record issues in Access / SQL (Write Conflict)

a problem has come up after a SQL DB I used was migrated to a new server. Now when trying to edit a record in Access (form or table), it says: WRITE CONFLICT: This record has been changed by another …

Read More