MS Access数据库的自动压缩和修复
我们有一个基于 MS Access 的系统，该系统依赖于存储在联网 MS Access 数据库中的后端数据。有时，当发生随机网络问题时，这些数据库会被损坏。因此我们认为应该添加某种可以定期启动紧凑型并进行修复的流程。
问题是这个数据库一直被多个用户使用，并且不能保证知道数据库不使用时某个时间可用。所以我的问题如下。
1) 如果当前端应用程序（MS Access 或 .NET）尝试连接数据库时数据库正在被 c/r'ed，会发生什么？可以吗？
2）SO推荐什么作为一个好的解决方案，让前端应用程序都等到后端MS Access数据库准备好使用？前端应用程序所需的更改越少越好。
谢谢你！
We have an MS Access based system that relies on back-end data stored in networked MS Access database. Sometimes those databases get corrupted when random networking issues occur. So we thought we should add some sort of process that can routinely fire off a compact and repair.
The problem is that this database is used all the time by multiple users, and there is no guarantee to know that a certain time will be available when the database is not in use. So my question(s) are as follows.
1) If the database is being c/r'ed when a front-end app (MS Access or .NET) tries to connect to it, what will happen? Is that ok?
2) What does SO recommend as a good solution to make the front-end apps all wait until the backend MS Access database is ready for use? The fewer changes needed to the front-end apps the better.
Thank you!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这是 Access 开发人员中比较常见的问题。
它由两部分组成：
用于备份和压缩数据库的代码，您将在服务器上安排此操作。
您在前端中放置的代码，用于在允许 #1 中的代码运行的时间自动注销所有人。
一般来说，办公室只在当地时间早上 8 点到下午 6 点开放，因此在午夜左右将所有人赶出去通常是安全的，这样后端就可以在凌晨 1 点等时间进行备份和压缩，为常规文件做准备服务器备份可能发生在凌晨 2 点。
用于备份/压缩的 VBScript 相对容易编写。如果您需要这方面的帮助，请询问。
对于前端，您需要有一个隐藏表单，该表单在应用程序打开时打开，并带有一个计时器，该计时器设置为在午夜启动并关闭应用程序。你想做得有多复杂取决于。潜在的陷阱是，如果用户已开始记录并且记录处于无法保存的状态（由于某种原因未通过验证规则）。这可能很复杂（您必须捕获错误，撤消树桩记录，然后就可以关闭），但最好设计您的应用程序，以便用户永远无法创建无法保存的记录。
关键点是您必须计划这两端，即计划并关闭备份/压缩计划运行时剩余的任何连接。
This is a relatively frequently-asked question among Access developers.
There are two parts to it:
the code to backup and compact the database, which you'd schedule on the server.
code that you put in your front end to automatically log everybody out at a time that allows the code in #1 to run.
Generally, an office is only open from, say, 8am to 6pm local time, so it's generally safe to kick everybody out around midnight so that the back end is available for backup and compacting at, say, 1am, in preparation for your regular file server backup which might happen, say, at 2am.
A VBScript for the backup/compact is relatively easy to write. If you need help with that, ask.
For the front end, you'll need to have a hidden form that opens when the app is open with a timer on it that's set to kick in at midnight and close out the app. How elaborate you want to get with that depends. Potential pitfalls would be if a user has started a record and it's in a state that can't be saved (fails validation rules for some reason). That can be complicated (you'd have to trap for the error, undo the stump record and then you could shut down), but it's better to engineer your apps so users can't ever create records that can't be saved.
The key point is that you have to plan both ends of this, the scheduling and shutting down any connections that remain at the time your backup/compact is scheduled to run.
据我了解，让它自动压缩数据库是一个坏主意。如果在压缩之前存在可恢复的错误，那么之后您可能会得到一个完全无法读取的数据库 - 需要从备份中恢复。
1>如果前端尝试访问正在压缩/修复的数据库，我相信前端客户端会被告知数据库不可用并被毫不客气地弹出。我相信您可以为此编写一个错误处理程序，但这需要比我所能提供的更多的 VBA 知识。
2>如果可能的话，在处理多个客户端时，后端的 RDBMS 总是会表现得更好，即使它是备用 Windows 机器上的 Microsoft SQL Server Express。当“坏事”发生时（网络断开、客户端锁定等），数据将有更好的机会不被损坏。具有事务的 RDBMS 通常是实现此目的的好方法。
至于解决这个问题，恐怕我缺乏编程上的想法。对不起。
From what I understand, having it automatically compact the database is a bad idea. If there was a recoverable error BEFORE compacting, you might end up with a completely unreadable database afterward - requiring a restore from backups.
1> If the front-end tries to access a database that is being compacted/repaired, I believe that front-end client is told the database is unavailable and is unceremoniously ejected. I believe you could write an error handler for this, but that requires more VBA knowledge than I have to offer.
2> If at all possible, when dealing with multiple clients - a RDBMS on the backend will always perform better, even if it's Microsoft SQL Server Express on a spare Windows box. Data is going to have a better chance of not getting corrupted when 'bad things' happen (network disconnections, client lockups, etc). A RDBMS with transactions is usually a good route for this.
As far as a solution to this, I am afraid I am short of ideas programmatically. Sorry.
Garry Robinson 关于访问安全的书讨论了这个问题。非常值得一读。在 Garry 的网站 (vb123.com) 上，他出售一个工具箱，其中包含许多管理和安全工具，其中一个向每个连接的工作站发出警告，然后在 x 秒后关闭前端。
这本书是：
真实世界的 Microsoft Access 数据库保护和安全
如果您在企业环境中使用 Access，这是必须拥有和阅读的。
Garry Robinson's book about Access Security deals with this issue. It is well worth a read . On Garry's website (vb123.com) he sells a toolbox with a number of admin and security tools included, including one that gives each connected workstation a warning and then it shuts down the front end x seconds later.
The book is:
Real World Microsoft Access Database Protection and Security
If you are working with Access in a corporate environment, it is a must have and read.