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.
我最近一起工作的一位同事告诉我，SQL Express和MS Access基本上是一回事;这似乎不是一个准确的陈述。我知道你可以将Access转换为SQL DB，也许它们是相似的，但我认为SQL DB引擎和用于运行访问的内容是不一样的。不仅如此，而且SQL语句的语法等我知道的不一样。
I am mainly trying to understand so that I am more informed about the versions.
我主要是想了解，以便我更了解版本。
4 个解决方案
#1
40
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).
首先，我需要澄清一些术语。 MS Access是一种快速应用程序开发（RAD）工具，允许您快速构建绑定到关系数据的表单和报表。它带有基于文件的数据库引擎（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.
访问RAD工具可以与许多不同的后端数据库（Jet，SQL Server，任何支持ODBC的数据库等）一起使用。我必须假设你的同事专门评论Jet / ACE，即MS Access使用的数据库引擎。
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.
我认为Jet / ACE数据库引擎和MS SQL Server Express之间最大的区别在于Jet / ACE是基于文件的，SQL Server Express使用客户端/服务器模型。这意味着SQL Server Express需要运行服务才能提供对数据存储的访问。在某些情况下，这可能会使部署复杂化。
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.
SQL Server Express实际上只是SQL Server的一个限制版本：最大数据库大小为4GB（2008R2中为10GB），仅使用单个物理CPU等。这些限制是为了防止大型组织使用免费的Express版本代替完整的SQL Server安装。这样做的结果是SQL Server Express提供了一个真正无缝的SQL Server升级路径。它（一般来说）也是一个比Jet / ACE更强大和功能齐全的数据库管理系统。
Similarities
相似
- relational database management systems
- 关系数据库管理系统
- written by Microsoft
- 由微软撰写
Differences
差异
- MS Access
- File based
- 基于文件
- free distributable runtime (2007 or later)
- 免费分发运行时（2007或更高版本）
- RAD tools (form/report designer)
- RAD工具（表单/报表设计器）
- uses Jet SQL
- 使用Jet SQL
- max file size 2GB
- 最大文件大小2GB
- 基于MS Access File的免费可分发运行时（2007或更高版本）RAD工具（表单/报表设计器）使用Jet SQL最大文件大小2GB
- SQL Server Express
- Client/Server model
- 客户/服务器模型
- free
- *
- no RAD tools
- 没有RAD工具
- uses Transact-SQL
- 使用Transact-SQL
- max database size 4GB (10GB for SSE R2), max one physical CPU
- 最大数据库大小为4GB（SSE R2为10GB），最大物理CPU为1
- SQL Server Express客户端/服务器模型免费没有RAD工具使用Transact-SQL最大数据库大小4GB（SSE R2为10GB），最多一个物理CPU
#2
1
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.
我认为你的同事想到的是SQL Server CE，它是一个超轻量级的嵌入式数据库，在数据库管理方面仍然（IMO）远优于Access。 SQL Express甚至无法与Access进行比较而不会冒犯前者。
#3
0
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
访问：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
SQL（Express列在最右侧的列）：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.
我一直在阅读的评论是，Access非常适合单用户单一访问数据库使用，只要您在单个用户之外进行扩展，就可以在其他位置查看。虽然这可能是一个“一点点”，但Access在多用户环境中确实表现不佳。根据经验，我们有一个客户端忽略并忽略了我们将后端数据库从Access迁移到SQL的请求，并且在很多情况下我们不得不从备份中恢复，或者由于损坏而使Access数据库脱机。
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.
它们是两种完全不同的技术，具有两个不同的目标市场数据库引擎确实不同，正如您所提到的，T-SQL与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.
您可以通过创建SSIS包或其他工具来将Access数据库“扩展”为SQL，但这会获取Access架构和数据并将其迁移到真正的SQL数据库。它不仅仅是附加Access数据库等。
Anytime you need a "real" database I'd highly recommend looking at any of the SQL versions that are available over Access.
只要您需要“真正的”数据库，我强烈建议您查看可通过Access获得的任何SQL版本。
#4
0
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.
请记住，使用MS-Access，如果您正确地玩牌，则没有尺寸限制。例如，没有理由不在自己的数据库中单独包含许多2到4个Gig表。您的ODBC应用程序可以打开与多个MS-Access数据库的连接，并查询每个数据库中的单个表。因此，您可以拥有一个包含数万亿条记录的数据库，存储在多个MDB文件中。我去上班的一家公司使用单个MS-Access数据库来运行以MS-Access形式完成的问题跟踪系统。由于共享会阻止MS-Access的问题，他们一次只能使用一个人。我为数据库写了一个Win32 Perl本机Windows GUI用户界面，它更好地进行了字段/记录验证，我的ODBC代码能够管理连接以便同时进行用户访问。我通过我的Perl程序为每个用户管理了数据库的打开和读取以及写入和关闭。我没有让数据库保持打开状态。我没有为每个用户维护持久连接，而是只保持足够长的连接以检索要编辑的记录。然后我关闭了连接，直到将记录写回数据库为止。此外，我通过维护一个用户登录表来编写自己的记录锁定程序逻辑，该用户登录表包含用户当前正在编辑的记录的记录ID，然后在不再编辑该记录时删除该条目。当另一个用户去编辑同一条记录时，程序检查该记录当前是否已打开以供其他用户编辑。该系统运作完美。 MS-Access从未通过ODBC和多用户访问锁定。我甚至在我编译的Perl程序中将密码嵌入到数据库中，这样除了通过我的Perl程序之外，没有人能够访问Access数据库中的数据。
#1
40
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).
首先，我需要澄清一些术语。 MS Access是一种快速应用程序开发（RAD）工具，允许您快速构建绑定到关系数据的表单和报表。它带有基于文件的数据库引擎（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.
访问RAD工具可以与许多不同的后端数据库（Jet，SQL Server，任何支持ODBC的数据库等）一起使用。我必须假设你的同事专门评论Jet / ACE，即MS Access使用的数据库引擎。
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.
我认为Jet / ACE数据库引擎和MS SQL Server Express之间最大的区别在于Jet / ACE是基于文件的，SQL Server Express使用客户端/服务器模型。这意味着SQL Server Express需要运行服务才能提供对数据存储的访问。在某些情况下，这可能会使部署复杂化。
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.
SQL Server Express实际上只是SQL Server的一个限制版本：最大数据库大小为4GB（2008R2中为10GB），仅使用单个物理CPU等。这些限制是为了防止大型组织使用免费的Express版本代替完整的SQL Server安装。这样做的结果是SQL Server Express提供了一个真正无缝的SQL Server升级路径。它（一般来说）也是一个比Jet / ACE更强大和功能齐全的数据库管理系统。
Similarities
相似
- relational database management systems
- 关系数据库管理系统
- written by Microsoft
- 由微软撰写
Differences
差异
- MS Access
- File based
- 基于文件
- free distributable runtime (2007 or later)
- 免费分发运行时（2007或更高版本）
- RAD tools (form/report designer)
- RAD工具（表单/报表设计器）
- uses Jet SQL
- 使用Jet SQL
- max file size 2GB
- 最大文件大小2GB
- 基于MS Access File的免费可分发运行时（2007或更高版本）RAD工具（表单/报表设计器）使用Jet SQL最大文件大小2GB
- SQL Server Express
- Client/Server model
- 客户/服务器模型
- free
- *
- no RAD tools
- 没有RAD工具
- uses Transact-SQL
- 使用Transact-SQL
- max database size 4GB (10GB for SSE R2), max one physical CPU
- 最大数据库大小为4GB（SSE R2为10GB），最大物理CPU为1
- SQL Server Express客户端/服务器模型免费没有RAD工具使用Transact-SQL最大数据库大小4GB（SSE R2为10GB），最多一个物理CPU
#2
1
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.
我认为你的同事想到的是SQL Server CE，它是一个超轻量级的嵌入式数据库，在数据库管理方面仍然（IMO）远优于Access。 SQL Express甚至无法与Access进行比较而不会冒犯前者。
#3
0
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
访问：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
SQL（Express列在最右侧的列）：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.
我一直在阅读的评论是，Access非常适合单用户单一访问数据库使用，只要您在单个用户之外进行扩展，就可以在其他位置查看。虽然这可能是一个“一点点”，但Access在多用户环境中确实表现不佳。根据经验，我们有一个客户端忽略并忽略了我们将后端数据库从Access迁移到SQL的请求，并且在很多情况下我们不得不从备份中恢复，或者由于损坏而使Access数据库脱机。
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.
它们是两种完全不同的技术，具有两个不同的目标市场数据库引擎确实不同，正如您所提到的，T-SQL与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.
您可以通过创建SSIS包或其他工具来将Access数据库“扩展”为SQL，但这会获取Access架构和数据并将其迁移到真正的SQL数据库。它不仅仅是附加Access数据库等。
Anytime you need a "real" database I'd highly recommend looking at any of the SQL versions that are available over Access.
只要您需要“真正的”数据库，我强烈建议您查看可通过Access获得的任何SQL版本。
#4
0
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.
请记住，使用MS-Access，如果您正确地玩牌，则没有尺寸限制。例如，没有理由不在自己的数据库中单独包含许多2到4个Gig表。您的ODBC应用程序可以打开与多个MS-Access数据库的连接，并查询每个数据库中的单个表。因此，您可以拥有一个包含数万亿条记录的数据库，存储在多个MDB文件中。我去上班的一家公司使用单个MS-Access数据库来运行以MS-Access形式完成的问题跟踪系统。由于共享会阻止MS-Access的问题，他们一次只能使用一个人。我为数据库写了一个Win32 Perl本机Windows GUI用户界面，它更好地进行了字段/记录验证，我的ODBC代码能够管理连接以便同时进行用户访问。我通过我的Perl程序为每个用户管理了数据库的打开和读取以及写入和关闭。我没有让数据库保持打开状态。我没有为每个用户维护持久连接，而是只保持足够长的连接以检索要编辑的记录。然后我关闭了连接，直到将记录写回数据库为止。此外，我通过维护一个用户登录表来编写自己的记录锁定程序逻辑，该用户登录表包含用户当前正在编辑的记录的记录ID，然后在不再编辑该记录时删除该条目。当另一个用户去编辑同一条记录时，程序检查该记录当前是否已打开以供其他用户编辑。该系统运作完美。 MS-Access从未通过ODBC和多用户访问锁定。我甚至在我编译的Perl程序中将密码嵌入到数据库中，这样除了通过我的Perl程序之外，没有人能够访问Access数据库中的数据。