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.
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
Differences
I think what your colleague had in mind was SQL Server CE, which is a super-lightweight embedded database, which is still (IMO) far superior to Access in database-management aspect. SQL Express cannot even be compared with Access without offending the former.
Just remember that with MS-Access you don't have size limitations if you play your cards right. There is no reason, for example, not to have many 2 to 4 Gig tables each contained singularly in their own database. Your ODBC applications can open a connection to multiple MS-Access databases and query the single table in each. So you can have a database containing trillions of records, stored in multiple MDB files. One company I went to work for was using a single MS-Access database to run a issue tracking system done in MS-Access forms. They could only use it one person at a time because of sharing issues that would lock MS-Access up. I wrote a Win32 Perl native Windows GUI user-interface to the database that was better at field/record validation, and my ODBC code was able to manage the connection for simultaneous user access. I managed the opening and reading and writing and closing of the database for each user through my Perl program. I did not leave the database open. I did not maintain a persistent connection for each user, but instead only maintained a connection long enough to retrieve a record for edit. Then I closed the connection until it was time to write the record back to the database. Also, I wrote my own record locking program logic by maintaining a user login table that contained the record id of the record a user was currently editing, then erased that entry when no longer editing that record. When another user went to edit the same record, the program checked if that record was currently open for edit by another user. The system worked flawlessly. MS-Access never locked up via ODBC and multi-user access. I even embedded the password to the database in my compiled Perl program so that no one could get to the data in the Access database other than through my Perl program.
Here are the datasheets for both products so you can see some hard facts on the difference between the two databases.
Access: http://office.microsoft.com/en-us/access-help/access-specifications-HP005186808.aspx
SQL (Express is listed on the far right column): http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx
The comment I have always read is that Access is great for single user single access database use, the minute you scale beyond a single user look elsewhere. While that may be a "bit" of a stretch, Access really does not do well in a multi-user environment. From experience we've had a client who has ignored and ignored our requests to migrate a backend database from Access to SQL, and there have been numerous occasions where we have had to restore from backups, or take the Access database offline due to corruption.
They are two completely different technologies with two different target markets. The database engines are indeed different, as you mention T-SQL is different than Access SQL.
You can "scale up" an Access database to SQL by creating an SSIS package or other tool to do the import, but this takes the Access schema and data and migrates it to a true SQL database. It does more than just attach the Access database or the like.
Anytime you need a "real" database I'd highly recommend looking at any of the SQL versions that are available over Access.
