C# 和 Access 2000
我开发了一个网络应用程序，过去几年一直在我的公司使用。 一开始它是管理有关用户、权限等的信息。 随着时间的推移，它与其他功能一起发展。它发展到我的表有 10-20 列，甚至有 20,000 - 40,000 条记录。
我一直听说 Access 不适合多用户环境。 第二件事是，当我尝试通过网络从表中读取一些记录时，必须将整个表拉到客户端。 出现这种情况是因为服务器端没有数据库引擎，数据过滤是在客户端完成的。
我想将此项目迁移到 SQL Server，但不幸的是在这种情况下无法完成。
我想知道是否有比使用 Access 数据库更可靠的解决方案，并且仍然使用单文件数据库系统。
我们有相当庞大的系统使用 dBase IV。 据我所知，它是完全多用户数据库系统。
也许用它代替 Access 会更好？ 让我不确定的是 dBase IV 比 Access 2000 要老得多。 我不确定这是否是一个好的解决方案。
也许还有其他选择？
I have developed an network application that is in use in my company for last few years.
At start it was managing information about users, rights etc.
Over the time it grew with other functionality. It grew to the point that I have tables with, let's say 10-20 columns and even 20,000 - 40,000 records.
I keep hearing that Access in not good for multi-user environments.
Second thing is the fact that when I try to read some records from the table over the network, the whole table has to be pulled to the client.
It happens because there is no database engine on the server side and data filtering is done on the client side.
I would migrate this project to the SQL Server but unfortunately it cannot be done in this case.
I was wondering if there is more reliable solution for me than using Access Database and still stay with a single-file database system.
We have quite huge system using dBase IV.
As far as I know it is fully multiuser database system.
Maybe it will be good to use it instead of Access?
What makes me not sure is the fact that dBase IV is much older than Access 2000.
I am not sure if it would be a good solution.
Maybe there are some other options?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
如果您的 Jet/ACE 后端遇到您提到的记录数量问题，那么听起来您遇到了架构设计问题或应用程序结构效率低下。
正如我在对您原来问题的评论中所说，Jet 不会检索完整的表。这是由那些不知道自己在说什么的人传播的神话。如果您有适当的索引，则只会从文件服务器请求索引页（然后仅请求满足您的条件所需的那些页），然后检索的唯一数据页将是那些具有与条件匹配的记录的数据页。您的要求。
因此，如果您看到全表扫描，则应该查看索引。
你没有提到你的用户群体。如果超过 25 左右，您可能会从扩大后端规模中受益，特别是如果您已经熟悉 SQL Server。
但是您为如此小的表描述的问题表明某个地方存在设计错误，无论是在您的架构中还是在您的应用程序中。
FWIW，我拥有带有 Jet 后端的 Access 应用程序，多个表中包含数百条数千条记录，由十几个同时添加和更新记录的用户使用，并且检索单个记录和小数据集的响应时间几乎是瞬时的（除了一些复杂的操作，例如检查新输入的记录是否与现有数据重复——这会比较慢，因为它使用大量的 LIKE 比较和表达式求值来进行比较）。您所经历的虽然不是 Access 前端，但与我在各种规模的 Jet 数据库方面的长期经验并不相称。
If you're having problems with your Jet/ACE back end with the number of records you mentioned, it sounds like you have schema design problems or an inefficiently-structured application.
As I said in my comment to your original question, Jet does not retrieve full tables. This is a myth propagated by people who don't have a clue what they are talking about. If you have appropriate indexes, only the index pages will be requested from the file server (and then, only those pages needed to satisfy your criteria), and then the only data pages retrieved will be those that have the records that match the criteria in your request.
So, you should look at your indexing if you're seeing full table scans.
You don't mention your user population. If it's over 25 or so, you probably would benefit from upsizing your back end, especially if you're already comfortable with SQL Server.
But the problem you described for such tiny tables indicates a design error somewhere, either in your schema or in your application.
FWIW, I've had Access apps with Jet back ends with 100s of thousands of records in multiple tables, used by a dozen simultaneous users adding and updating records, and response time retrieving individual records and small data sets was nearly instantaneous (except for a few complex operations like checking newly entered records for duplication against existing data -- that's slower because it uses lots of LIKE comparisons and evaluation of expressions for comparison). What you're experiencing, while not an Access front end, is not commensurate with my long experience with Jet databases of all sizes.
您可能希望阅读有关 Access 的信息主题：Is MS Access (JET ）适合多用户访问？
You may wish to read this informative thread about Access: Is MS Access (JET) suitable for multiuser access?
作为记录，此答案是从 复制/编辑的我回答了另一个问题。
Aristo，
您可以使用 Access 作为您的集中式数据存储。
在多用户场景（至少有 15-20 个用户）中，访问会被阻塞，这是不正确的。
确实，您需要一个良好的 Access 数据文件备份策略。但最后我发现您也需要一个良好的 SQL Server 备份策略。 （非常重要的警告是 SQL Server 可以执行“热”备份，但不能执行 Access。）
因此...您可以使用 Access 作为数据存储。然后，如果您能够超越公司政治控制您的网络，也许您可以开始升级当前的应用程序以使用 SQL Server。
我最近回答了另一个关于如何将数据库拆分为两个文件的问题。这是链接。
创建前端 MDE
拆分数据库文件到前端：后端是提高性能的关键。 （假设，正如大卫芬顿提到的，你有一个相当好的设计。）
如果我可以提到最后一件事......你的公司不会为你提供其他部署选项，这是荒谬的。肯定有人有一定的权力，你可以让他“想象没有你的申请的生活”。我只是想知道你是否拥有比你想象的更多的力量。
赛斯
For the record this answer is copied/edited from another question I answered.
Aristo,
You CAN use Access as your centralized data store.
It is simply NOT TRUE that access will choke in multi-user scenarios--at least up to 15-20 users.
It IS true that you need a good backup strategy with the Access data file. But last I checked you need a good backup strategy with SQL Server, too. (With the very important caveat that SQL Server can do "hot" backups but not Access.)
So...you CAN use access as your data store. Then if you can get beyond the company politics controlling your network, perhaps then you could begin moving toward upfitting your current application to use SQL Server.
I recently answered another question on how to split your database into two files. Here is the link.
Creating the Front End MDE
Splitting your database file into front end : back end is sort of a key to making it more performant. (Assume, as David Fenton mentioned, that you have a reasonably good design.)
If I may mention one last thing...it is ridiculous that your company won't give you other deployment options. Surely there is someone there with some power who you can get to "imagine life without your application." I am just wondering if you have more power than you might realize.
Seth
您在用户之间共享 Access 数据库时遇到的问题与任何基于文件的数据库都相同。
读取会将大量数据拉入内存，并且写入会受到某种类型的文件锁的保护。在你的环境下，听起来你必须充分利用你所拥有的。
The problems you experience with an Access Database shared amongst your users will be the same with any file based database.
A read will pull a lot of data into memory and writes are guarded with some type of file lock. Under your environment it sounds like you are going to have to make the best of what you have.
“第二件事是，当我尝试通过网络从表中读取一些记录时，必须将整个表拉到客户端。”
实际上不是。这是一种常见的错误陈述，由那些不了解 Jet（Access 内的数据库引擎）工作原理的人传播。下拉所有记录或过多记录的情况是因为您没有在索引中选择条件或排序中使用的所有字段。我们还发现，索引是/否（又名布尔字段）也可以在某些查询中产生巨大的差异。
真正发生的是 Jet 关闭所需的索引页和数据页。虽然这比数据库引擎创建的数据多得多，但这并不是整个表。
我的客户的各个表中也有 600K 和 800K 记录，性能也很好。
"Second thing is the fact that when I try to read some records from the table over the network, the whole table has to be pulled to the client. "
Actually no. This is a common misstatement spread by folks who do not understand the nature of how Jet, the database engine inside Access, works. Pulling down all the records, or excessive number of records, happens because you don't have all the fields used in the selection criteria or sorting in the index. We've also found that indexing yes/no aka boolean fields can also make a huge difference in some queries.
What really happens is that Jet brings down the index pages and data pages which are required. While this is a lot more data than a database engine would create this is not the entire table.
I also have clients with 600K and 800K records in various tables and performance is just fine.
我们有一个使用非常频繁的 Access 数据库应用程序。我之前曾有 23 个用户同时使用，没有出现任何问题。只要他们不访问相同的记录，那么我就没有任何问题。
我确实有几个由多个不同部门使用和更新的表格。例如，我有一个报价表单，其中包含 13 个不同的选项卡，每个选项卡上有 10-20 个字段。用户通常需要在单个记录中花几分钟时间编辑和查找信息。为了避免任何写入冲突，每当字段更改时我都会调用以下函数。只要不是输入新记录，它就会更新。
我的一切设置方式如下：
PDC.mdb <-- 前端，存储在用户计算机上。每个用户都有自己的副本。指向 PDC_be.mdb 中的表的链接。包含所有表单、报告、查询、宏和模块。我创建了一个表单，可用于打开/关闭 Shift 键双通道。只有我有权访问它。
PDC_be.mdb <-- 后端，存储在服务器上。包含所有数据。它包含的唯一形式和 VBA 是打开/关闭 Shift 键双通道。只有我有权访问它。
Secured.mdw <-- 安全文件，存储在服务器上。
然后，我在用户桌面上放置一个快捷方式，将安全文件与前端联系起来，并提供他们的登录凭据。
该数据库已运行 6 年多，没有出现错误或损坏。
We have an Access database application that is used pretty heavily. I have had 23 users on all at the same time before without any issues. As long as they don't access the same record then I don't have any problems.
I do have a couple of forms that are used and updated by several different departments. For instance I have a Quoting form that contains 13 different tabs and 10-20 fields on each tab. Users are typically in a single record for minutes editing and looking for information. To avoid any write conflicts I call the below function any time a field is changed. As long as it is not a new record being entered, then it updates.
They way I have everything setup is as follows:
PDC.mdb <-- Front End, Stored on the users machine. Every user has their own copy. Links to tables found in PDC_be.mdb. Contains all forms, reports, queries, macros, and modules. I created a form that I can use to toggle on/off the shift key bipass. Only I have access to it.
PDC_be.mdb <-- Back End, stored on the server. Contains all data. Only form and VBA it contains is to toggle on/off the shift key bipass. Only I have access to it.
Secured.mdw <-- Security file, stored on the server.
Then I put a shortcut on the users desktop that ties the security file to the front end and also provides their login credentials.
This database has been running without error or corruption for over 6 years.
Access 不是平面文件数据库系统！它是一个关系数据库系统。
Access is not a flat file database system! It's a relational database system.
您无法使用 SQL Server Express？
否则，MySQL是一个很好的数据库。
但是，如果您无法安装任何东西（您应该尽早参与这些政治活动，否则就会晚一些），只需使用现有的数据库系统即可。
基本上，使用 Access 时，它不能同时处理超过 5 人的连接，否则它会损坏您。
You can't use SQL Server Express?
Otherwise, MySQL is a good database.
But if you can't install ANYTHING (you should get into those politics sooner rather than later -- or it WILL be later), just use you existing database system.
Basically with Access, it cannot handle more than 5 people connected at the same time, or it will corrupt on you.