您可能会出于各种原因决定将数据库拆分为前端应用程序或后端应用程序。后端数据库包含表并且存储在文件服务器上。链接到后端表的前端数据库包含所有窗体、查询、报表、宏和模块。前端数据库将分布在用户的工作站中。
拆分数据库络文件夹网站库服务器
库服务器软件
Windows Server 2003或更高版本网SHAREPOINT数据是否需要数据否否否是是否需要否否是否数据可用性良好适合于数据编辑量较少的小型组最好最好安全性取决于其他措施最不安全的方法最好最好灵活性很灵活。可以轻松地开发新的数据库功能而不会影响到工作。每个用户均可以在自己的副本中修改对象的设计。灵活性较差。可以使用数据库的脱机副本来完成开发工作然后替换该数据库。不允许用户单独修改数据库设计。很灵活。取决于共享方法。利用发布可以控制提供哪些窗体和报表。利用链接用户可以修改自己的数据库副本。很灵活。可以轻松地开发新的数据库功能而不会影响到工作。每个用户均可以在自己的副本中修改对象的设计。
将 Access 与数据库服务器一起使用
可以将 Access 与数据库服务器产品(如 SQL Server)一起使用以共享数据库。此方法可提供许多好处但需要其他软件(数据库服务器产品)。
使用数据库服务器共享数据库的好处高性能和可伸缩性 在许多情况下数据库服务器提供的性能比 Access 数据库文件单独提供的性能更好。许多数据库服务器产品还提供对千吉字节 (GB) 大小的超大型数据库的支持此大小大约是 Access 数据库文件的当前限制 (2 GB) 的 500 倍。通过并行处理查询(在单个进程中使用多个本机线程来处理用户请求)并在添加更多用户时将额外内存要求降至最小数据库服务器产品通常可以高效率地进行工作。
可用性更好 大部分数据库服务器产品允许您备份正在使用中的数据库。因此不必强制使用户退出数据库即可备份数据。此外数据库服务器产品通常还可以高效率地处理并发编辑和记录锁定。
安全性提高 没有任何数据库可以做到完全安全。但数据库服务器产品提供了强大的安全性可帮助防止数据的未经授权使用。大多数数据库服务器产品都提供了基于帐户的安全性允许您指定哪些用户可以查看哪些表。即使在有人不正当获得 Access 前端的情况下基于帐户的安全性也会阻止数据的未经授权使用。
自动恢复 如果发生系统失败(例如操作系统崩溃或断电)一些数据库服务器产品具有自动恢复机制利用这些机制只需几分钟即可将数据库恢复到上次的一致状态而无需数据库管理员干预。
基于服务器的处理 通过在服务器上处理数据库查询然后将结果发送给客户端在客户端/服务器配置中使用 Access 可帮助减少网络通讯量。由服务器执行处理操作的效率通常更高尤其是在处理大型数据集时
Heres two good reading materials:10 reasons why IT Pro hate AccessDatabase Evolution: Microsoft Access within an Organizations 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 (Accesss 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 (Accesss 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.I would have to disagree. Speed is ALWAYS ALWAYS IMPORTANT to users!
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.This is like comparing Apples to Oranges. You are comparing a Front end to a back end.
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.Again, this is like comparing Apples to Oranges. You are comparing a Front end to a back end.
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.Again, this is like comparing Apples to Oranges. You are comparing a Front end to a back end.
Quote:5. Im 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 Im guessing it is the same with a mysql database, but with mysql, I dont understand what platform the user uses and also can mysql database records be viewed in "form" or "report" view as is MS Access?Again, this is like comparing Apples to Oranges. You are comparing a Front end to a back end.
Hope this helps
__________________