您可能会出于各种原因决定将数据库拆分为前端应用程序或后端应用程序。后端数据库包含表并且存储在文件服务器上。链接到后端表的前端数据库包含所有窗体、查询、报表、宏和模块。前端数据库将分布在用户的工作站中。
http://support.microsoft.com/kb/304932/zh-cn
http://www.opengatesw.net/ms-access-tutorials/Access-Articles/MSAccess-Deployment-Best-Practices.htm
ntegrating MS Access with SQL Server
Express Essentials: Use SQL Server Express as a Back End to Access
拆分数据库 | 络文件夹 | 网站 | 库服务器 | |
|---|---|---|---|---|
| 库服务器软件？ | ||||
| Windows Server 2003 或更高版本？ | ||||
将 Access 与数据库服务器一起使用
可以将 Access 与数据库服务器产品（如 SQL Server）一起使用以共享数据库。此方法可提供许多好处，但需要其他软件（数据库服务器产品）。
使用数据库服务器共享数据库的好处
- 高性能和可伸缩性 在许多情况下，数据库服务器提供的性能比 Access 数据库文件单独提供的性能更好。许多数据库服务器产品还提供对千吉字节 (GB) 大小的超大型数据库的支持，此大小大约是 Access 数据库文件的当前限制 (2 GB) 的 500 倍。通过并行处理查询（在单个进程中使用多个本机线程来处理用户请求）并在添加更多用户时将额外内存要求降至最小，数据库服务器产品通常可以高效率地进行工作。
- 可用性更好 大部分数据库服务器产品允许您备份正在使用中的数据库。因此，不必强制使用户退出数据库即可备份数据。此外，数据库服务器产品通常还可以高效率地处理并发编辑和记录锁定。
- 安全性提高 没有任何数据库可以做到完全安全。但数据库服务器产品提供了强大的安全性，可帮助防止数据的未经授权使用。大多数数据库服务器产品都提供了基于帐户的安全性，允许您指定哪些用户可以查看哪些表。即使在有人不正当获得 Access 前端的情况下，基于帐户的安全性也会阻止数据的未经授权使用。
- 自动恢复 如果发生系统失败（例如操作系统崩溃或断电），一些数据库服务器产品具有自动恢复机制，利用这些机制，只需几分钟即可将数据库恢复到上次的一致状态，而无需数据库管理员干预。
- 基于服务器的处理 通过在服务器上处理数据库查询然后将结果发送给客户端，在客户端/服务器配置中使用 Access 可帮助减少网络通讯量。由服务器执行处理操作的效率通常更高，尤其是在处理大型数据集时
Here's two good reading materials:
10+ reasons why IT Pro hate Access
Database Evolution: Microsoft Access within an Organization's Database Strategy
To help you understand the difference, it will help uf you have a better understanding of Access. This way you will know what you really are comparing.
Is Access a database?
No. 
It is not. 
Access really is a just Front end. Access by default uses the JET database engine in 2003 and eariler versions. With Access 2007, there is also a new database engine called ACE.
When making a database comparison between mySQL or any SQL server with "Access", you are really comparing the the datasbe engine that ships with Acess, not the actual Access program.
Since Access is just a front end to a database engine, you can also use other back ends besides JET/ACE. You can use mySQL serve, Microsoft SQL server, Oracle, an other database that have ODBC drivers available.
You do not create forms in an SQL Server (mySQL included). You still need a front end tool, like Access or PHP, to create the user interface.
What does this mean?
- Access really is a front end (User Interface/GUI).
- Jet/ACE (Access's default) is a back end (tables).
- mySQL is a back end (tables).
Note: Access stores all the objects (Forms, reports, etc) in tables using JET/ACE.
So if you are asking about mySQL (or any SQL Server) you need to compare back end to back end.
To compare just a few major things between an SQL server and the JET/ACE (Access's database engine).
About JET/ACE :
- NOT true Client Server
- They support 255 concurrent users
- They have a 2 GIG file size limit.
- Security level is considered low (not very secured)
SQL Server: Including mySQL
- True Client Server
- Support more that 255 concurrent users
- Can support larger that 2 GIG files (depends on the version)
- Security level is considered High
There are lots of over thigs that should be considered, but there as the major differences.
Can JET/ACE database (normally called an Access database for ease) be used as a back end for a web site?
Yes. It is good for site that have very few concurrent users. Not very good for high traffic sites that have lots of users at the same time.
How do users work with an SQL Server?
You must build a front end. You can build a front end to an SQL server, like mySQL, with:
- PHP, Asp, etc. for browser based
- .NET
- Access
- VB
- Any programming language that support ODBC
How do developers work with an SQL Server?
There are also GUI tools available to work with SQL server that make it as easy as using Access.
Security important?
If data security is important, I recommend using an SQL server. With Access, I like to use an SQL server for added back end security.
Your Questions
Based on your questions, I think you are comparing mySQL server (a back end) to the Access front end where you design form, reports, etc. They are not the same thing. You can compare Access to .NET, PHP, ASP, ASP.NET, Delphi, etc.
I think the other people that you are referring to as comparing mySQL server to JET/ACE databse engine, not the Access front end (forms, reports, etc.
Quote:
| 1. I can understand that maybe mysql is faster and can hold more information, but none of my current uses will ever need the speed and storage of 1,000,000+ records. |
Quote:
| 2. My other reason for not agreeing with the general population on this debate is for the general ease of designing an Access database. It has a very neat graphical interface where you can create queries with a few clicks, design forms graphically on screen to look whatever way you please, create reports, navigate so easily through records and have them returned to you in the graphical manor you like. |
Access is a front end. This is where you create the user interface forms, reports, etc. You still need a back end.
mySQL Server is a back end. You still need a front end for crating the user interface.
Quote:
| 3. To my understanding with a mysql database, the user that wants to add, view, or manipulate data/records needs to be fluent in the SQL language and type out long complex "select" statements each time they want to retrieve data, and then on top of that, the data is returned in a spreadsheet type format, as opposed to a easily readable form as Access does. |
Quote:
| 4. How to access the mysql database seems more difficult than Access to me also. Again, to my understanding, the main way to connect/access data in a mysql database is through a web browser and the mysql database NEEDS some type of server to be hosted on, where Access is just a single file located on your computer or can easily be split into front and back end files for a home or small office network, and no need for a server. |
Quote:
| 5. I'm contradicting myself on this one a little here and may actually be answering my own question, but with Access, a database designer designs it so the user can easily use it, and I'm guessing it is the same with a mysql database, but with mysql, I don't understand what platform the user uses and also can mysql database records be viewed in "form" or "report" view as is MS Access? |
Hope this helps
__________________