I have created an MS Access 2003 application, set up as a split front-end/back-end configuration, with a user group of about five people. The front end .mdb sits on a network file server, and it contains all the queries, forms, reports, and VBA code, plus links to all the tables in the back end .mdb and some links to ODBC data sources like an AS/400. The back end sits on the same network file server, and it just has the table data in it.
我创建了一个MS Access 2003应用程序,设置为拆分前端/后端配置,用户组大约有五个人。前端.mdb位于网络文件服务器上,它包含所有查询,表单,报告和VBA代码,以及后端.mdb中所有表的链接以及一些指向ODBC数据源的链接,如AS / 400。后端位于同一个网络文件服务器上,它只包含表数据。
This was working well until I "went live" and my handful of users started coming up with enhancement requests, bug reports, etc. I have been rolling out new code by developing/testing in my own copy of the front-end .mdb in another network folder (which is linked to the same back-end .mdb), then posting my completed file in a "come-and-get-it" folder, alerting the users, and they go copy/paste the new front-end file to their own folders on the network. This way, each user can update their front end when they're at a 'stopping point' without having to boot everyone out at once.
这一点运行良好,直到我“上线”并且我的少数用户开始提出增强请求,错误报告等。我已经通过在我自己的前端.mdb副本中开发/测试来推出新代码。另一个网络文件夹(链接到同一个后端.mdb),然后将我完成的文件发布到“come-and-get-it”文件夹中,提醒用户,然后他们复制/粘贴新的前端文件到网络上自己的文件夹。这样,每个用户都可以在他们处于“停止点”时更新他们的前端,而无需立即启动所有人。
I've found that when I'm developing now, sometimes Access becomes extremely slow. Like, when I am developing a form and attempt to click a drop-down on the properties box, the drop-down arrow will push in, but it will take a few seconds before the list of options appears. Or there's tons of lag in selecting & moving controls on a form. Or lots of keyboard lag.
我发现,当我正在开发时,有时Access变得极其缓慢。就像,当我开发一个表单并尝试单击属性框上的下拉列表时,下拉箭头将会推入,但是在选项列表出现之前需要几秒钟。或者在表单上选择和移动控件时存在大量延迟。或者很多键盘滞后。
Then, at other times, there's no lag at all.
然后,在其他时候,根本没有滞后。
I'm wondering if it's because I'm linked to the same back end as the other users. I did make a reasonable effort to set up the queries, forms, reports etc. with minimal record locking, if any at all, depending on the need. But I may have missed something, or perhaps there is some other performance issue I need to address.
我想知道是不是因为我链接到与其他用户相同的后端。我确实做了一些合理的努力来设置查询,表单,报告等,最小的记录锁定,如果有的话,根据需要。但我可能错过了一些东西,或者可能还有其他一些我需要解决的性能问题。
But I'm wondering if there is an even better way for me to set up my own development back-end .mdb, so I can be testing my code on "safe" data instead of the same live data as the rest of the users. I'm afraid that it's only a matter of time before I corrupt some data, probably at the worst possible moment.
但我想知道是否有更好的方法来设置我自己的开发后端.mdb,所以我可以在“安全”数据上测试我的代码而不是与其他用户相同的实时数据。我担心损坏一些数据只是时间问题,可能是在最糟糕的时刻。
Obviously, I could just set up a separate back-end .mdb and manually reconfigure the table links in the front end every time, using the Linked Table Manager. But I'm hoping there is a more elegant solution than that.
显然,我可以设置一个单独的后端.mdb,并使用链接表管理器每次手动重新配置前端的表链接。但我希望有一个更优雅的解决方案。
And I'm wondering if there are any other performance issues I should be considering in this multi-user, split database configuration.
我想知道在这个多用户拆分数据库配置中是否还有其他性能问题需要考虑。
EDIT: I should have added that I'm stuck with MS Access (not MS-SQL or any other "real" back end); for more details see my comment to this post.
编辑:我应该补充说,我坚持使用MS Access(不是MS-SQL或任何其他“真正的”后端);有关详细信息,请参阅我对此帖的评论。
8 个解决方案
#1
If all your users are sharing the front end, that's THE WRONG CONFIGURATION.
如果所有用户都在共享前端,那就是错误的配置。
Each user should have an individual copy of the front end. Sharing a front end is guaranteed to lead to frequent corruption of the shared front end, as well as odd corruptions of forms and modules in the front end.
每个用户都应该拥有前端的单独副本。保证共享前端会导致共享前端频繁损坏,以及前端表单和模块的奇怪损坏。
It's not clear to me how you could be developing in the same copy of the front end that the end users are using, since starting with A2000, that is prohibited (because of the "monolithic save model," where the entire VBA project is stored in a single BLOB field in a single record in one of the system tables).
我不清楚如何在最终用户使用的前端的同一副本中进行开发,因为从A2000开始,这是禁止的(因为“整体存储模型”,整个VBA项目存储在其中)在一个系统表中的单个记录中的单个BLOB字段中)。
I really don't think the problems are caused by using the production data (though it's likely not a good idea to develop against production data, as others have said). I think they are caused by poor coding practices and lack of maintainance of your front end code.
我真的不认为这些问题是由使用生产数据引起的(尽管根据其他人的说法开发生产数据可能不是一个好主意)。我认为它们是由糟糕的编码实践和前端代码缺乏维护造成的。
-
turn off COMPILE ON DEMAND in the VBE options.
在VBE选项中关闭COMPILE ON DEMAND。
-
make sure you require OPTION EXPLICIT.
确保你需要OPTION EXPLICIT。
-
compile your code frequently, after every few lines of code -- to make this easy, add the COMPILE button to your VBE toolbar (while I'm at it, I also add the CALL STACK button).
在每几行代码之后经常编译代码 - 为了简化这一过程,将COMPILE按钮添加到VBE工具栏(当我在它时,我还添加了CALL STACK按钮)。
-
periodically make a backup of your front end and decompile and recompile the code. This is accomplished by launching Access with the /decompile switch, opening your front end, closing Access, opening your front end with Access (with the SHIFT key held down to bypass the startup code), then compacting the decompiled front end (with the SHIFT key held down), then compiling the whole project and compacting one last time. You should do this before any major code release.
定期备份前端并反编译并重新编译代码。这是通过使用/ decompile开关启动Access,打开前端,关闭Access,使用Access打开前端(按住SHIFT键以绕过启动代码),然后压缩反编译的前端(使用SHIFT)来实现的。按键),然后编译整个项目并最后一次压缩。您应该在任何主要代码发布之前执行此操作。
A few other thoughts:
其他一些想法:
-
you don't say if it's a Windows server. Linux servers accessed over SAMBA have exhibited problems in the past (though some people swear by them and say they're vastly faster than Windows servers), and historically Novell servers have needed to have settings tweaked to enable Jet files to be reliably edited. There are also some settings (like OPLOCKS) that can be adjusted on a Windows server to make things work better.
你不说它是不是Windows服务器。通过SAMBA访问的Linux服务器过去曾出现过问题(尽管有些人发誓说它们比Windows服务器快得多),而且历史上Novell服务器需要调整设置以使Jet文件可靠地编辑。还有一些设置(如OPLOCKS)可以在Windows服务器上进行调整,以使工作更好。
-
store your Jet MDBs in shares with short paths. \Server\Data\MyProject\MyReallyLongFolderName\Access\Databases\ is going to be much slower reading data than \Server\Databases. This really makes a huge difference.
将Jet MDB存储在具有短路径的共享中。 \ Server \ Data \ MyProject \ MyReallyLongFolderName \ Access \ Databases \读取数据要比\ Server \ Databases慢得多。这确实产生了巨大的差异。
-
linked tables store metadata that can become outdated. There are two easy steps and one drastic one to be taken to fix it. First, compact the back end, and then compact the front end. That's the easy one. If that doesn't help, completely delete the links and recreate them from scratch.
链接表存储可能过时的元数据。有两个简单的步骤,一个急剧的步骤来解决它。首先,压紧后端,然后压紧前端。这很简单。如果这没有帮助,请完全删除链接并从头开始重新创建它们。
-
you might also consider distributing an MDE to your end users instead of an MDB, as it cannot uncompile (which an MDB can).
您也可以考虑将MDE分发给最终用户而不是MDB,因为它无法解压缩(MDB可以)。
-
see Tony Toews's Performance FAQ for other generalized performance information.
有关其他广义性能信息,请参阅Tony Toews的性能常见问题解答。
#2
1) Relink Access tables from code http://www.mvps.org/access/tables/tbl0009.htm
1)从代码http://www.mvps.org/access/tables/tbl0009.htm重新链接访问表
Once I'm ready to publish a new MDE to the users I relink the tables, make the MDE and copy the MDE to the server.
一旦我准备好向用户发布新的MDE,我重新链接表,制作MDE并将MDE复制到服务器。
2) I specifically created the free Auto FE Updater utility so that I could make changes to the FE MDE as often as I wanted and be quite confident that the next time someone went to run the app that it would pull in the latest version. For more info on the errors or the Auto FE Updater utility see the free Auto FE Updater utility at http://www.granite.ab.ca/access/autofe.htm at my website to keep the FE on each PC up to date.
2)我专门创建了免费的自动FE更新程序实用程序,以便我可以根据需要随时更改FE MDE,并且相信下次有人去运行应用程序它会引入最新版本。有关错误或Auto FE Updater实用程序的详细信息,请参阅我的网站http://www.granite.ab.ca/access/autofe.htm上的免费Auto FE Updater实用程序,以使每台PC上的FE保持最新。
3) Now when working on site at a clients I make the updates to the table structure after hours when everyone is out of the system. See HOW TO: Detect User Idle Time or Inactivity in Access 2000 (Q210297) http://support.microsoft.com/?kbid=210297 ACC: How to Detect User Idle Time or Inactivity (Q128814) http://support.microsoft.com/?kbid=128814
3)现在,当客户在现场工作时,我会在几个小时后对每个人都不在系统中时对表结构进行更新。请参阅如何:在Access 2000中检测用户空闲时间或不活动(Q210297)http://support.microsoft.com/?kbid=210297 ACC:如何检测用户空闲时间或不活动(Q128814)http://support.microsoft的.com /?kbid = 128814
However we found that the code which runs on the timer event must be disabled for the programmers. Otherwise weird things start happening when you're editing code.
但是我们发现必须为程序员禁用在timer事件上运行的代码。当你编辑代码时,会发生奇怪的事情。
Also print preview would sometimes not allow the users to run a menu item to export the report to Excel or others. So you had to right click on the Previewed report to get some type of internal focus back on the report so they could then export it. This was also helped by extending the timer to five minutes.
此外,打印预览有时不允许用户运行菜单项以将报告导出到Excel或其他人。因此,您必须右键单击“预览”报告才能获得某种类型的内部焦点,以便他们可以将其导出。将计时器延长到五分钟也有助于此。
The downside to extending the timer to five minutes was if a person stays in the same form and at the same control for considerable parts of the day, ie someone doing the same inquiries, the routine didn't realize that they had actually done something. I'll be putting in some logic sometime to reset this timer whenever they do something in the program.
将计时器延长到五分钟的缺点是,如果一个人在一天的大部分时间里保持相同的形式和相同的控制,即有人做同样的查询,例程没有意识到他们实际上已经做了一些事情。每当他们在程序中执行某些操作时,我会在某个时候输入一些逻辑来重置此计时器。
4) In reference to another person commenting about scripts and such to update the schema see Compare'Em http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm. While it has its quirks it does create the VBA code to update tables, fields, indexes and relationships.
4)参考另一个评论脚本的人,以及更新架构,请参阅Compare'Em http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm。虽然它有它的怪癖,它确实创建了VBA代码来更新表,字段,索引和关系。
#3
Use VBA to unlink and re-link your tables to the new target when switching from dev to prod. It's been to many years for me to remember the syntax--I just know the function was simple to write.
从dev切换到prod时,使用VBA取消链接并将表重新链接到新目标。多年来我一直记住语法 - 我只知道函数编写起来很简单。
Or use MS-Access to talk to MS-Access through ODBC, or some other data connection that lives outside of the client mdb.
或者使用MS-Access通过ODBC或客户端mdb之外的其他数据连接与MS-Access通信。
As with all file base databases, you will eventually run into problems with peak usage or when you go over a small magical number somewhere between 2 and 30.
与所有文件库数据库一样,您最终会遇到峰值使用问题,或者当您在2到30之间查看一个小的神奇数字时。
Also, Access tends to corrupt frequently, so backup, compact and repair need to be done on an frequent basis. 3rd party tools used to exist to automate this task.
此外,Access往往会经常损坏,因此需要经常进行备份,紧凑和修复。过去存在的第三方工具可以自动执行此任务。
As far as performance goes, the data is being processed client side, so you might want to use something like netmeter to watch how much data is going over the wire. The same principle about indexing and avoiding table scans apply to file base dbs as well.
就性能而言,数据正在客户端进行处理,因此您可能希望使用netmeter这样的内容来监视线路上有多少数据。关于索引和避免表扫描的相同原则也适用于文件库dbs。
#4
Many good suggestions from other people. Here's my 2 millicents worth. My backend data is on server accessed through a Drive mapping. In my case, the Y drive. Production users get the mapping through a login script using active directory. Then the following scenarios are easily done by batch file:
其他人的许多好建议。这是我2百万的价值。我的后端数据在通过Drive映射访问的服务器上。在我的情况下,Y驱动器。生产用户使用活动目录通过登录脚本获取映射。然后,批处理文件可以轻松完成以下方案:
- Develop against local computer by doing a subst command in a batch file
- run reports against last nights data by pointing Y to the backup server (read only)
- run reports against end of month data by pointing to the right directory
- test against specialized scenarios by keeping a special directory
通过在批处理文件中执行subst命令来针对本地计算机进行开发
通过将Y指向备份服务器来运行针对昨晚数据的报告(只读)
通过指向正确的目录来运行针对月末数据的报告
通过保留特殊目录来针对特定方案进行测试
In my environment (average 5 simultaneous users, 1000's of rows, not 10,000's.) corruption has occurred, but it's rare and manageable. Only once in the last several years have we resorted to the previous days backup. We use SQL Server for our higher volume stuff, but it's not as convenient to develop against, probably because we don't have a SQL admin on site.
在我的环境中(平均5个并发用户,1000个行,而不是10,000个。)已经发生了腐败,但它很少见且易于管理。在过去的几年中,我们只使用了前几天的备份。我们使用SQL Server来实现更高容量的东西,但是开发起来并不方便,可能是因为我们没有现场的SQL管理员。
#5
You might also find some of the answers to this question (how to extract schemas from access) to be useful as well. Once you've extracted a schema using one of the techniques that were suggested you gain a whole range of new options like the ability to use source control on the schemas, as well as being able to easily build "clean" testing environments.
您可能还会发现此问题的一些答案(如何从访问中提取模式)也很有用。一旦使用建议的技术之一提取了模式,就可以获得一系列新选项,例如在模式上使用源代码控制的能力,以及能够轻松构建“干净”的测试环境。
Edit to respond to comment: There's no easy way to source control an Access database in it's native format, but schema files are just text files like any other. Hence, you can check them in and out of the source control software of your choice for easy version control/rollbacks.
编辑以回复评论:没有简单的方法来源控制Access数据库的原生格式,但架构文件只是像任何其他文本文件一样。因此,您可以在您选择的源控制软件中进行检查,以便轻松进行版本控制/回滚。
Or course, it relies on you having a series of scripts set up to re-build your database from the schema. Once you do, it's normally fairly trivial to create an option/alternative version that rebuilds it in a different location, allowing you to build test environments from any previous committed version of the schema. I hope that clarifies a bit!
或者,它依赖于您设置一系列脚本以从架构重建数据库。一旦这样做,创建一个在不同位置重建它的选项/替代版本通常是相当简单的,允许您从任何以前提交的模式版本构建测试环境。我希望澄清一下!
#6
If you want to update the back end MDB schema automatically when you release a new FE to the clients then see Compare'Em http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm will happily generate the VBA code need to recreate an MDB. Or the code to create the differences between two MDBs so you can do a version upgrade of the already existing BE MDB. It's a bit quirky but works.
如果要在向客户端发布新FE时自动更新后端MDB架构,请参阅Compare'Em http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm将很乐意生成VBA代码需要重新创建MDB。或者用于创建两个MDB之间差异的代码,以便您可以对现有BE MDB进行版本升级。这有点古怪但有效。
I use it all the time.
我用它所有的时间。
#7
You need to understand that a shared mdb file for the data is not a robust solution. Microsoft would suggest that SQL Server or some other server based database would be a far better solution and would allow you to use the same access front end. The migration wizard would help you make the changeover if you wanted to go that way.
您需要了解数据的共享mdb文件不是一个强大的解决方案。 Microsoft建议SQL Server或其他基于服务器的数据库将是一个更好的解决方案,并允许您使用相同的访问前端。如果您想这样做,迁移向导将帮助您进行转换。
As another uses pointed out, corruption will occur. It is simply a question of how often, not if.
另一种用途指出,腐败将会发生。这只是一个经常性的问题,而不是。
To understand the performance issues you need to understand that to the server the mdb file with the data in it is simply that, a file. Since no code runs on the server, the server does not understand transactions, record locking etc. It simply knows that there is a file that a bunch of people are trying to read and write simultaniously.
要了解性能问题,您需要了解服务器,其中包含数据的mdb文件就是一个文件。由于服务器上没有代码运行,服务器无法理解事务,记录锁定等。它只是知道有一群人正在尝试同时读写。
With a database system such as SQL Server, Oracle, DB2. MySQL etc. the database program runs on the server and looks to the server like a single program accessing the database file. It is the database program (running on the server) that handles record locking, transactions, concurrency, logging, data backup/recovery and all the other nice things one wants from a database.
使用SQL Server,Oracle,DB2等数据库系统。 MySQL等数据库程序在服务器上运行,并像访问数据库文件的单个程序一样查看服务器。它是数据库程序(在服务器上运行),它处理记录锁定,事务,并发,日志记录,数据备份/恢复以及数据库中所需的所有其他好处。
Since a database program designed to run on the server is designed to do that and only that, it can do it far better and more efficently that a program like Access reading an writing a shared file (mdb).
由于设计为在服务器上运行的数据库程序旨在实现这一目的,因此它可以更好,更有效地执行像Access这样的程序读写共享文件(mdb)。
#8
There are two rules for developing against live data
针对实时数据开发有两个规则
The first rule is . . . never develop against live data. Not ever.
第一条规则是。 。 。从不针对实时数据制定。永远不会。
The second rule is . . .never develop against live data. Not ever.
第二个规则是。 。 。根据实时数据制定。永远不会。
You can programatically change the bindings for linked tables, so you can write a macro to change your links when you're deploying a new version.
您可以以编程方式更改链接表的绑定,以便在部署新版本时编写宏来更改链接。
The application is slow because it's MS Access, and it doesn't like many concurrent users (where many is any number > 1).
应用程序很慢,因为它是MS Access,并且它不像许多并发用户(其中许多是任何数字> 1)。
#1
If all your users are sharing the front end, that's THE WRONG CONFIGURATION.
如果所有用户都在共享前端,那就是错误的配置。
Each user should have an individual copy of the front end. Sharing a front end is guaranteed to lead to frequent corruption of the shared front end, as well as odd corruptions of forms and modules in the front end.
每个用户都应该拥有前端的单独副本。保证共享前端会导致共享前端频繁损坏,以及前端表单和模块的奇怪损坏。
It's not clear to me how you could be developing in the same copy of the front end that the end users are using, since starting with A2000, that is prohibited (because of the "monolithic save model," where the entire VBA project is stored in a single BLOB field in a single record in one of the system tables).
我不清楚如何在最终用户使用的前端的同一副本中进行开发,因为从A2000开始,这是禁止的(因为“整体存储模型”,整个VBA项目存储在其中)在一个系统表中的单个记录中的单个BLOB字段中)。
I really don't think the problems are caused by using the production data (though it's likely not a good idea to develop against production data, as others have said). I think they are caused by poor coding practices and lack of maintainance of your front end code.
我真的不认为这些问题是由使用生产数据引起的(尽管根据其他人的说法开发生产数据可能不是一个好主意)。我认为它们是由糟糕的编码实践和前端代码缺乏维护造成的。
-
turn off COMPILE ON DEMAND in the VBE options.
在VBE选项中关闭COMPILE ON DEMAND。
-
make sure you require OPTION EXPLICIT.
确保你需要OPTION EXPLICIT。
-
compile your code frequently, after every few lines of code -- to make this easy, add the COMPILE button to your VBE toolbar (while I'm at it, I also add the CALL STACK button).
在每几行代码之后经常编译代码 - 为了简化这一过程,将COMPILE按钮添加到VBE工具栏(当我在它时,我还添加了CALL STACK按钮)。
-
periodically make a backup of your front end and decompile and recompile the code. This is accomplished by launching Access with the /decompile switch, opening your front end, closing Access, opening your front end with Access (with the SHIFT key held down to bypass the startup code), then compacting the decompiled front end (with the SHIFT key held down), then compiling the whole project and compacting one last time. You should do this before any major code release.
定期备份前端并反编译并重新编译代码。这是通过使用/ decompile开关启动Access,打开前端,关闭Access,使用Access打开前端(按住SHIFT键以绕过启动代码),然后压缩反编译的前端(使用SHIFT)来实现的。按键),然后编译整个项目并最后一次压缩。您应该在任何主要代码发布之前执行此操作。
A few other thoughts:
其他一些想法:
-
you don't say if it's a Windows server. Linux servers accessed over SAMBA have exhibited problems in the past (though some people swear by them and say they're vastly faster than Windows servers), and historically Novell servers have needed to have settings tweaked to enable Jet files to be reliably edited. There are also some settings (like OPLOCKS) that can be adjusted on a Windows server to make things work better.
你不说它是不是Windows服务器。通过SAMBA访问的Linux服务器过去曾出现过问题(尽管有些人发誓说它们比Windows服务器快得多),而且历史上Novell服务器需要调整设置以使Jet文件可靠地编辑。还有一些设置(如OPLOCKS)可以在Windows服务器上进行调整,以使工作更好。
-
store your Jet MDBs in shares with short paths. \Server\Data\MyProject\MyReallyLongFolderName\Access\Databases\ is going to be much slower reading data than \Server\Databases. This really makes a huge difference.
将Jet MDB存储在具有短路径的共享中。 \ Server \ Data \ MyProject \ MyReallyLongFolderName \ Access \ Databases \读取数据要比\ Server \ Databases慢得多。这确实产生了巨大的差异。
-
linked tables store metadata that can become outdated. There are two easy steps and one drastic one to be taken to fix it. First, compact the back end, and then compact the front end. That's the easy one. If that doesn't help, completely delete the links and recreate them from scratch.
链接表存储可能过时的元数据。有两个简单的步骤,一个急剧的步骤来解决它。首先,压紧后端,然后压紧前端。这很简单。如果这没有帮助,请完全删除链接并从头开始重新创建它们。
-
you might also consider distributing an MDE to your end users instead of an MDB, as it cannot uncompile (which an MDB can).
您也可以考虑将MDE分发给最终用户而不是MDB,因为它无法解压缩(MDB可以)。
-
see Tony Toews's Performance FAQ for other generalized performance information.
有关其他广义性能信息,请参阅Tony Toews的性能常见问题解答。
#2
1) Relink Access tables from code http://www.mvps.org/access/tables/tbl0009.htm
1)从代码http://www.mvps.org/access/tables/tbl0009.htm重新链接访问表
Once I'm ready to publish a new MDE to the users I relink the tables, make the MDE and copy the MDE to the server.
一旦我准备好向用户发布新的MDE,我重新链接表,制作MDE并将MDE复制到服务器。
2) I specifically created the free Auto FE Updater utility so that I could make changes to the FE MDE as often as I wanted and be quite confident that the next time someone went to run the app that it would pull in the latest version. For more info on the errors or the Auto FE Updater utility see the free Auto FE Updater utility at http://www.granite.ab.ca/access/autofe.htm at my website to keep the FE on each PC up to date.
2)我专门创建了免费的自动FE更新程序实用程序,以便我可以根据需要随时更改FE MDE,并且相信下次有人去运行应用程序它会引入最新版本。有关错误或Auto FE Updater实用程序的详细信息,请参阅我的网站http://www.granite.ab.ca/access/autofe.htm上的免费Auto FE Updater实用程序,以使每台PC上的FE保持最新。
3) Now when working on site at a clients I make the updates to the table structure after hours when everyone is out of the system. See HOW TO: Detect User Idle Time or Inactivity in Access 2000 (Q210297) http://support.microsoft.com/?kbid=210297 ACC: How to Detect User Idle Time or Inactivity (Q128814) http://support.microsoft.com/?kbid=128814
3)现在,当客户在现场工作时,我会在几个小时后对每个人都不在系统中时对表结构进行更新。请参阅如何:在Access 2000中检测用户空闲时间或不活动(Q210297)http://support.microsoft.com/?kbid=210297 ACC:如何检测用户空闲时间或不活动(Q128814)http://support.microsoft的.com /?kbid = 128814
However we found that the code which runs on the timer event must be disabled for the programmers. Otherwise weird things start happening when you're editing code.
但是我们发现必须为程序员禁用在timer事件上运行的代码。当你编辑代码时,会发生奇怪的事情。
Also print preview would sometimes not allow the users to run a menu item to export the report to Excel or others. So you had to right click on the Previewed report to get some type of internal focus back on the report so they could then export it. This was also helped by extending the timer to five minutes.
此外,打印预览有时不允许用户运行菜单项以将报告导出到Excel或其他人。因此,您必须右键单击“预览”报告才能获得某种类型的内部焦点,以便他们可以将其导出。将计时器延长到五分钟也有助于此。
The downside to extending the timer to five minutes was if a person stays in the same form and at the same control for considerable parts of the day, ie someone doing the same inquiries, the routine didn't realize that they had actually done something. I'll be putting in some logic sometime to reset this timer whenever they do something in the program.
将计时器延长到五分钟的缺点是,如果一个人在一天的大部分时间里保持相同的形式和相同的控制,即有人做同样的查询,例程没有意识到他们实际上已经做了一些事情。每当他们在程序中执行某些操作时,我会在某个时候输入一些逻辑来重置此计时器。
4) In reference to another person commenting about scripts and such to update the schema see Compare'Em http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm. While it has its quirks it does create the VBA code to update tables, fields, indexes and relationships.
4)参考另一个评论脚本的人,以及更新架构,请参阅Compare'Em http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm。虽然它有它的怪癖,它确实创建了VBA代码来更新表,字段,索引和关系。
#3
Use VBA to unlink and re-link your tables to the new target when switching from dev to prod. It's been to many years for me to remember the syntax--I just know the function was simple to write.
从dev切换到prod时,使用VBA取消链接并将表重新链接到新目标。多年来我一直记住语法 - 我只知道函数编写起来很简单。
Or use MS-Access to talk to MS-Access through ODBC, or some other data connection that lives outside of the client mdb.
或者使用MS-Access通过ODBC或客户端mdb之外的其他数据连接与MS-Access通信。
As with all file base databases, you will eventually run into problems with peak usage or when you go over a small magical number somewhere between 2 and 30.
与所有文件库数据库一样,您最终会遇到峰值使用问题,或者当您在2到30之间查看一个小的神奇数字时。
Also, Access tends to corrupt frequently, so backup, compact and repair need to be done on an frequent basis. 3rd party tools used to exist to automate this task.
此外,Access往往会经常损坏,因此需要经常进行备份,紧凑和修复。过去存在的第三方工具可以自动执行此任务。
As far as performance goes, the data is being processed client side, so you might want to use something like netmeter to watch how much data is going over the wire. The same principle about indexing and avoiding table scans apply to file base dbs as well.
就性能而言,数据正在客户端进行处理,因此您可能希望使用netmeter这样的内容来监视线路上有多少数据。关于索引和避免表扫描的相同原则也适用于文件库dbs。
#4
Many good suggestions from other people. Here's my 2 millicents worth. My backend data is on server accessed through a Drive mapping. In my case, the Y drive. Production users get the mapping through a login script using active directory. Then the following scenarios are easily done by batch file:
其他人的许多好建议。这是我2百万的价值。我的后端数据在通过Drive映射访问的服务器上。在我的情况下,Y驱动器。生产用户使用活动目录通过登录脚本获取映射。然后,批处理文件可以轻松完成以下方案:
- Develop against local computer by doing a subst command in a batch file
- run reports against last nights data by pointing Y to the backup server (read only)
- run reports against end of month data by pointing to the right directory
- test against specialized scenarios by keeping a special directory
通过在批处理文件中执行subst命令来针对本地计算机进行开发
通过将Y指向备份服务器来运行针对昨晚数据的报告(只读)
通过指向正确的目录来运行针对月末数据的报告
通过保留特殊目录来针对特定方案进行测试
In my environment (average 5 simultaneous users, 1000's of rows, not 10,000's.) corruption has occurred, but it's rare and manageable. Only once in the last several years have we resorted to the previous days backup. We use SQL Server for our higher volume stuff, but it's not as convenient to develop against, probably because we don't have a SQL admin on site.
在我的环境中(平均5个并发用户,1000个行,而不是10,000个。)已经发生了腐败,但它很少见且易于管理。在过去的几年中,我们只使用了前几天的备份。我们使用SQL Server来实现更高容量的东西,但是开发起来并不方便,可能是因为我们没有现场的SQL管理员。
#5
You might also find some of the answers to this question (how to extract schemas from access) to be useful as well. Once you've extracted a schema using one of the techniques that were suggested you gain a whole range of new options like the ability to use source control on the schemas, as well as being able to easily build "clean" testing environments.
您可能还会发现此问题的一些答案(如何从访问中提取模式)也很有用。一旦使用建议的技术之一提取了模式,就可以获得一系列新选项,例如在模式上使用源代码控制的能力,以及能够轻松构建“干净”的测试环境。
Edit to respond to comment: There's no easy way to source control an Access database in it's native format, but schema files are just text files like any other. Hence, you can check them in and out of the source control software of your choice for easy version control/rollbacks.
编辑以回复评论:没有简单的方法来源控制Access数据库的原生格式,但架构文件只是像任何其他文本文件一样。因此,您可以在您选择的源控制软件中进行检查,以便轻松进行版本控制/回滚。
Or course, it relies on you having a series of scripts set up to re-build your database from the schema. Once you do, it's normally fairly trivial to create an option/alternative version that rebuilds it in a different location, allowing you to build test environments from any previous committed version of the schema. I hope that clarifies a bit!
或者,它依赖于您设置一系列脚本以从架构重建数据库。一旦这样做,创建一个在不同位置重建它的选项/替代版本通常是相当简单的,允许您从任何以前提交的模式版本构建测试环境。我希望澄清一下!
#6
If you want to update the back end MDB schema automatically when you release a new FE to the clients then see Compare'Em http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm will happily generate the VBA code need to recreate an MDB. Or the code to create the differences between two MDBs so you can do a version upgrade of the already existing BE MDB. It's a bit quirky but works.
如果要在向客户端发布新FE时自动更新后端MDB架构,请参阅Compare'Em http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm将很乐意生成VBA代码需要重新创建MDB。或者用于创建两个MDB之间差异的代码,以便您可以对现有BE MDB进行版本升级。这有点古怪但有效。
I use it all the time.
我用它所有的时间。
#7
You need to understand that a shared mdb file for the data is not a robust solution. Microsoft would suggest that SQL Server or some other server based database would be a far better solution and would allow you to use the same access front end. The migration wizard would help you make the changeover if you wanted to go that way.
您需要了解数据的共享mdb文件不是一个强大的解决方案。 Microsoft建议SQL Server或其他基于服务器的数据库将是一个更好的解决方案,并允许您使用相同的访问前端。如果您想这样做,迁移向导将帮助您进行转换。
As another uses pointed out, corruption will occur. It is simply a question of how often, not if.
另一种用途指出,腐败将会发生。这只是一个经常性的问题,而不是。
To understand the performance issues you need to understand that to the server the mdb file with the data in it is simply that, a file. Since no code runs on the server, the server does not understand transactions, record locking etc. It simply knows that there is a file that a bunch of people are trying to read and write simultaniously.
要了解性能问题,您需要了解服务器,其中包含数据的mdb文件就是一个文件。由于服务器上没有代码运行,服务器无法理解事务,记录锁定等。它只是知道有一群人正在尝试同时读写。
With a database system such as SQL Server, Oracle, DB2. MySQL etc. the database program runs on the server and looks to the server like a single program accessing the database file. It is the database program (running on the server) that handles record locking, transactions, concurrency, logging, data backup/recovery and all the other nice things one wants from a database.
使用SQL Server,Oracle,DB2等数据库系统。 MySQL等数据库程序在服务器上运行,并像访问数据库文件的单个程序一样查看服务器。它是数据库程序(在服务器上运行),它处理记录锁定,事务,并发,日志记录,数据备份/恢复以及数据库中所需的所有其他好处。
Since a database program designed to run on the server is designed to do that and only that, it can do it far better and more efficently that a program like Access reading an writing a shared file (mdb).
由于设计为在服务器上运行的数据库程序旨在实现这一目的,因此它可以更好,更有效地执行像Access这样的程序读写共享文件(mdb)。
#8
There are two rules for developing against live data
针对实时数据开发有两个规则
The first rule is . . . never develop against live data. Not ever.
第一条规则是。 。 。从不针对实时数据制定。永远不会。
The second rule is . . .never develop against live data. Not ever.
第二个规则是。 。 。根据实时数据制定。永远不会。
You can programatically change the bindings for linked tables, so you can write a macro to change your links when you're deploying a new version.
您可以以编程方式更改链接表的绑定,以便在部署新版本时编写宏来更改链接。
The application is slow because it's MS Access, and it doesn't like many concurrent users (where many is any number > 1).
应用程序很慢,因为它是MS Access,并且它不像许多并发用户(其中许多是任何数字> 1)。