为多用户访问设置 MS-Access 数据库 [英] Setting up an MS-Access DB for multi-user access

问题描述

我们正在考虑增长"一个小的 MS-Access 数据库，其中包含几个表、表单和多个用户的查询.(使用不同的后端是另一种但更长期的选择，遗憾的是目前不可接受.)
大多数用户将是只读的，但会有少数(目前一两个)用户必须能够进行更改(而只读用户也在使用数据库).我们不太关心安全方面，但更关心以下一些问题:

We're thinking of "growing" a little MS-Access DB with a few tables, forms and queries for multiple users. (Using a different back-end is another, but more long-term option that is unfortunately currently not acceptable.)
Most users will be read-only, but there will be a few (currently one or two) users that have to be able to do changes (while the read-only users are also using the DB). We're not so much concerned about the security aspects, but more about some of the following issues:

• 我们如何确保写入用户可以在其他用户使用数据时更改表数据?读取用户是否在表上加锁?写用户是否必须在表上加锁?Access 是为我们做这件事还是我们必须明确地对此进行编码?
• 是否存在我们应该注意的MS Access 事务"常见问题?
• 我们可以在使用表单、查询等时处理它们吗?我们如何在不妨碍用户的情况下编程"?
• MS Access 中的哪些设置会影响处理方式?
• 我们的背景主要是在 Oracle 中，Access 在处理多个用户方面有什么不同?Access 中是否有隔离级别"之类的东西?

任何有用文章的提示或指示将不胜感激.

Any tips or pointers to helpful articles would be greatly appreciated.

推荐答案

我觉得这个问题的答案有问题、混乱和不完整，所以我会努力做得更好.

I find the answers to this question to be problematic, confusing and incomplete, so I'll make an effort to do better.

没有人真正以任何完整的方式回答过这个问题.Access 选项中有关设置锁定的信息与读锁定和写锁定无关.No Locks vs. All Records vs. Edited Record 是你为 WRITES 设置默认记录锁定的方式.

Nobody has really answered this in any complete fashion. The information on setting locks in the Access options has nothing to do with read vs. write locking. No Locks vs. All Records vs. Edited Record is how you set the default record locking for WRITES.

• 无锁意味着您正在使用 OPTIMISTIC 锁定，这意味着您允许多个用户编辑记录，然后在事后通知他们自他们启动自己的编辑以来记录是否已更改.您应该从乐观锁定开始，因为它不需要编码来实现它，而且对于小用户群体来说，它几乎不会引起问题.

• No locks means you are using OPTIMISTIC locking, which means you allow multiple users to edit the record and then inform them after the fact if the record has changed since they launched their own edits. Optimistic locking is what you should start with as it requires no coding to implement it, and for small users populations it hardly ever causes a problem.

所有记录意味着每次启动编辑时都会锁定整个表.

All Records means that the whole table is locked any time an edit is launched.

Edited Record 意味着锁定的记录较少，但它是单条记录还是多条记录取决于您的数据库是否设置为使用记录级锁定(首次在 Jet 4 中添加)或页级锁定.坦率地说，我从不认为设置记录级锁定值得麻烦，因为乐观锁定可以解决大部分问题.

Edited Record means that fewer records are locked, but whether or not it's a single record or more than one record depends on whether your database is set up to use record-level locking (first added in Jet 4) or page-level locking. Frankly, I've never thought it worth the trouble to set up record-level locking, as optimistic locking takes care of most of the problems.

有人可能认为您想要使用记录级悲观锁定，但事实是在绝大多数应用程序中，两个用户几乎从不编辑同一个记录.现在，很明显，某些类型的应用程序可能是例外，但如果我遇到这样的应用程序，我可能会尝试通过重新设计架构来设计它，这样两个用户编辑相同的应用程序将是非常罕见的记录(通常改为进行某种形式的事务性编辑，通过添加记录而不是编辑现有数据来进行更改).

One might think that you want to use record-level pessimistic locking, but the fact is that in the vast majority of apps, two users are almost never editing the same record. Now, obviously, certain kinds of apps might be exceptions to that, but if I ran into such an app, I'd likely try to engineer it away by redesigning the schema so that it would be very uncommon for two users to edit the same record (usually by going to some form of transactional editing instead, where changes are made by adding records, rather than editing the existing data).

现在，对于您的实际问题，有多种方法可以将某些用户限制为只读并授予其他用户写入权限.Jet 用户级安全性就是为此目的而设计的，只要它是该术语的任何有意义定义的安全性"，它就可以正常工作.一般来说，只要您使用的是 Jet/ACE 数据存储，您将获得的最佳安全性就是 Jet ULS 提供的安全性.是的，它是可破解的，但是您的用户会因为破解它而犯下严重的罪行，所以这可能就足够了.

Now, for your actual question, there are a number of ways to accomplish restricting some users to read-only and granting others write privileges. Jet user-level security was intended for this purpose and works fine insofar as it's "security" for any meaningful definition of the term. In general, as long as you're using a Jet/ACE data store, the best security you're going to get is that provided by Jet ULS. It's crackable, yes, but your users would be committing a firable offense by breaking it, so it might be sufficient.

我倾向于根本不实施 Jet ULS，而只是构建数据编辑表单，以便他们检查用户的 Windows 登录并将表单设为只读或可写，具体取决于哪些用户应该获得哪些访问权限.是否要在数据表中记录组成员身份，或为此目的维护 Windows 安全组取决于您.您也可以使用 Jet 工作组文件来处理它，并为写入用户提供不同的 system.mdw 文件.只读用户将以管理员身份透明地登录，而以管理员身份登录的用户将被授予只读访问权限.写入用户将使用其他用户名登录(显然，在您为他们提供的用于启动应用程序的快捷方式中，不提供密码)，这将用于将表单设置为读取或写入.

I would tend to not implement Jet ULS at all and instead just architect the data editing forms such that they checked the user's Windows logon and made the forms read-only or writable depending on which users are supposed to get which access. Whether or not you want to record group membership in a data table, or maintain Windows security groups for this purpose is up to you. You could also use a Jet workgroup file to deal with it, and provide a different system.mdw file for the write users. The read-only users would log on transparently as admin, and those logged on as admin would be granted only read-only access. The write users would log on as some other username (transparently, in the shortcut you provide them for launching the app, supplying no password), and that would be used to set up the forms as read or write.

如果您使用 Jet ULS，它可能会变得非常麻烦.它涉及将所有表锁定为只读(甚至可能不是)，然后使用 RWOP 查询提供对数据的访问.在我 14 年的专业 Access 开发生涯中，我只做过一款这样的应用.

If you use Jet ULS, it can become really hairy to get it right. It involves locking down all the tables as read-only (or maybe not even that) and then using RWOP queries to provide access to the data. I haven't done but one such app in my 14 years of professional Access development.

总结我对您问题部分的回答:

To summarize my answers to the parts of your question:

我建议在应用程序中执行此操作，根据用户登录将表单设置为只读/仅或在运行时可编辑.最简单的方法是将表单设置为只读，并在写入用户打开表单时更改为可编辑.

I would recommend doing this in the application, setting forms to read/only or editable at runtime depending on the user logon. The easiest approach is to set your forms to be read-only and change to editable for the write users when they open the form.

没有任何意义.Jet/ACE 确实有读锁，但它们只是为了维护单个视图的状态，以及为用户刷新数据.它们不会锁定任何类型的写操作，尽管理论上跟踪它们的开销会减慢速度.担心是不够的.

Not in any meaningful sense. Jet/ACE does have read locks, but they are there only for the purpose of maintaining state for individual views, and for refreshing data for the user. They do not lock out write operations of any kind, though the overhead of tracking them theoretically slows things down. It's not enough to worry about.

Access 与 Jet/ACE 结合使用会自动为您执行此操作，尤其是当您选择乐观锁定作为默认值时.这里的关键是 Access 应用程序是数据绑定的，所以一旦加载表单，记录就有读锁，而一旦编辑记录，是否对其他用户进行写锁定取决于无论您使用的是乐观锁还是悲观锁.同样，这是 Access 使用绑定表单中的默认行为为您处理的事情.在遇到问题之前，您无需担心任何问题.

Access in combination with Jet/ACE does this for you automatically, particularly if you choose optimistic locking as your default. The key point here is that Access apps are databound, so as soon as a form is loaded, the record has a read lock, and as soon as the record is edited, whether or not it is write-locked for other users is determined by whether you are using optimistic or pessimistic locking. Again, this is the kind of thing Access takes care of for you with its default behaviors in bound forms. You don't worry about any of it until the point at which you encounter problems.

基本上，除了在运行时设置可编辑性(根据谁具有写访问权限)之外，如果您使用乐观锁定，则不需要编码.使用悲观锁定，您不必编写代码，但您几乎总是需要编写代码，因为您不能让用户停留在默认行为和错误消息中.

Basically, other than setting editability at runtime (according to who has write access), there is no coding necessary if you're using optimistic locking. With pessimistic locking, you don't have to code, but you will almost always need to, as you can't just leave the user stuck with the default behaviors and error messages.

Jet/ACE 支持提交/回滚事务，但我不清楚你在这个问题中的意思.通常，除了维护原子性(例如，在创建发票或执行涉及多个表的任何更新时)之外，我不使用事务.它以您期望的方式工作，但对于 Access 应用程序中的绝大多数操作来说并不是真正必要的.

Jet/ACE has support for commit/rollback transactions, but it's not clear to me if that's what you mean in this question. In general, I don't use transactions except for maintaining atomicity, e.g., when creating an invoice, or doing any update that involves multiple tables. It works about the way you'd expect it to but is not really necessary for the vast majority of operations in an Access application.

也许这里的一个问题(尤其是第一个问题)是您可能不太明白 Access 旨在创建将数据绑定到表单的应用程序.事务"对于未绑定和无状态的应用程序(例如，基于浏览器的应用程序)是一个非常重要的主题，但对于数据绑定的应用程序，编辑和保存都是透明的.

Perhaps one of the issues here (particularly in light of the first question) is that you may not quite grasp that Access is designed for creating apps with data bound to the forms. "Transactions" is a topic of great importance for unbound and stateless apps (e.g., browser-based), but for data bound apps, the editing and saving all happens transparently.

对于某些类型的操作，这可能会出现问题，有时使用未绑定表单编辑 Access 中的数据是合适的.但根据我的经验，这种情况很少发生.并不是说我不使用未绑定的表单——我将很多表单用于对话框等——只是我的应用程序不编辑带有未绑定表单的数据表.几乎没有例外，我所有的应用程序都使用绑定表单编辑数据.

For certain kinds of operations this can be problematic, and occasionally it's appropriate to edit data in Access with unbound forms. But that's very seldom the case, in my experience. It's not that I don't use unbound forms -- I use lots of them for dialogs and the like -- it's just that my apps don't edit data tables with unbound forms. With almost no exceptions, all my apps edit data with bound forms.

现在，在 Access 中实现未绑定表单实际上相当容易(特别是如果您将编辑控件命名为与底层字段相同的名称)，但是使用未绑定数据编辑表单确实缺少使用 Access 的意义，即绑定已为您完成.解除绑定的主要缺点是您会丢失所有记录级别的表单事件，例如 OnInsert、BeforeUpdate 等.

Now, unbound forms are actually fairly easy to implement in Access (particularly if you name your editing controls the same as the underlying fields), but going with unbound data editing forms is really missing the point of using Access, which is that the binding is all done for you. And the main drawback of going unbound is that you lose all the record-level form events, such as OnInsert, BeforeUpdate and so forth.

这是已经得到很好解决的问题之一.所有多用户或复制的 Access 应用程序都应该拆分，大多数单用户应用程序也应该拆分.这是一个很好的设计，也使应用程序更加稳定，因为只有数据表最终会被多个用户打开.

This is one of the questions that's been well-addressed. All multi-user or replicated Access apps should be split, and most single-user apps should be, too. It's good design and also makes the apps more stable, as only the data tables end up being opened by more than one user at a time.

事情?"什么东西?

我对 Oracle 一无所知(即使我的客户愿意，也没有人负担得起)，但是要求对 Access 和 Oracle 进行比较会暴露出一个根本性的误解.

I don't know anything specifically about Oracle (none of my clients could afford it even if they wanted to), but asking for a comparison of Access and Oracle betrays a fundamental misunderstanding somewhere along the line.

Access 是一种应用程序开发工具.

Access is an application development tool.

Oracle 是具有工业实力的数据库服务器.

Oracle is an industrial strength database server.

苹果和橙子.

现在，当然，Access 附带了一个默认的数据库引擎，最初称为 Jet，现在修改并重命名为 ACE，但是在许多级别上，Access 开发平台可以与默认数据库引擎 Jet/ACE 完全解耦.

Now, of course, Access ships with a default database engine, originally called Jet and now revised and renamed ACE, but there are many levels at which Access the development platform can be entirely decoupled from Jet/ACE, the default database engine.

在这种情况下，您选择使用 Jet/ACE 后端，这可能适合小用户群，即 25 岁以下.Jet/ACE 也可以适合 50 或 100，特别是当只有少数并发用户具有写入权限时.虽然 Jet/ACE 中的 255 个用户限制包括只读和写用户，但真正控制您可以支持多少并发用户的是写用户的数量，在您的情况下，您的应用程序主要是读- 仅限用户，因此设计一个没有后端问题的优秀应用应该不会非常困难.

In this case, you've chosen to use a Jet/ACE back end, which will likely be just fine for small user populations, i.e., under 25. Jet/ACE can also be fine up to 50 or 100, particularly when only a few of the simultaneous users have write permission. While the 255-user limit in Jet/ACE includes both read-only and write users, it's the number of write users that really controls how many simultaneous users you can support, and in your case, you've got an app with mostly read-only users, so it oughtn't be terribly difficult to engineer a good app that has no problems with the back end.

基本上，我认为您的 Oracle 背景可能会导致您误解如何在 Access 中进行开发，其中预期的方法是将您的表单绑定到无需编写任何代码即可更新的记录源.现在，为了提高效率，最好将表单绑定到记录的子集，而不是整个表，但即使在数据编辑表单后面的记录源中有整个表，Access 在编辑 Jet/只要您的数据表被有效地索引，ACE 表(关于将整个表拉过网络的古老神话仍然存在).

Basically, I think your Oracle background is likely leading you to misunderstand how to develop in Access, where the expected approach is to bind your forms to recordsources that are updated without any need to write code. Now, for efficiency's sake it's a good idea to bind your forms to subsets of records, rather than to whole tables, but even with an entire table in the recordsource behind a data editing form, Access is going to be fairly efficient in editing Jet/ACE tables (the old myth about pulling the whole table across the wire is still out there) as long your data tables are efficiently indexed.

记录锁定是您最不应该担心的事情，其中​​一个原因是绑定编辑，其中表单始终知道后端发生了什么(好吧，在间隔大约一秒，默认刷新间隔).也就是说，它不像一个网页，您可以在其中检索数据的副本，然后在完全与原始数据检索操作无关的事务中将您的编辑发送回服务器.在像 Access 这样的绑定环境中，后端数据文件上的锁定文件将始终跟踪某人打开记录进行编辑这一事实.这可以防止用户的编辑踩到其他人的编辑，因为 Access 知道状态并通知用户.这一切都发生在开发人员没有任何编码的情况下，这是绑定编辑模型的一大优势(除了不必编写代码来发布编辑).

Record locking is something you mostly shouldn't have any cause to worry about, and one of the reasons for that is because of bound editing, where the form knows what's going on in the back end at all times (well, at intervals about a second apart, the default refresh interval). That is, it's not like a web page where you retrieve a copy of the data and then post your edits back to the server in a transaction completely unconnected to the original data retrieval operation. In a bound environment like Access, the locking file on the back-end data file is always going to be keeping track of the fact that someone has the record open for editing. This prevents a user's edits from stomping on someone else's edits, because Access knows the state and informs the user. This all happens without any coding on the part of the developer and is one of the great advantages of the bound editing model (aside from not having to write code to post the edits).

对于所有第一次使用 Access 的熟悉其他平台的有经验的数据库程序员，我强烈建议像最终用户一样使用 Access.尝试所有点击功能.运行表单和报告向导并检查它们产生的结果.我不能保证所有这些都展示了良好的做法，但它们肯定展示了 Access 使用方式背后的默认假设.

For all those who are experienced database programmers familiar with other platforms who are coming to Access for the first time, I strongly suggest using Access like an end user. Try out all the point and click features. Run the form and report wizards and check out the results that they produce. I can't vouch for all of them as demonstrating good practices, but they definitely demonstrate the default assumptions behind the way Access is intended to be used.

如果您发现自己编写了大量代码，那么您很可能错过了访问点.

If you find yourself writing a lot of code, then you're likely missing the point of Access.

这篇关于为多用户访问设置 MS-Access 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！