如何在MS Access中设置开发环境 [英] How to set up a development environment in MS Access

问题描述

我创建了一个MS Access 2003应用程序，该应用程序设置为前端/后端拆分配置，用户组大约有5个人.前端.mdb位于网络文件服务器上，它包含所有查询，表单，报告和VBA代码，以及指向后端.mdb中所有表的链接以及一些指向ODBC数据源(如AS/400后端位于同一网络文件服务器上，并且其中只包含表数据.

这一直很好，直到我上线"并且我的少数用户开始提出增强功能请求，错误报告等.我一直在通过自己的前端副本进行开发/测试来推出新代码. .mdb在另一个网络文件夹(链接到同一个后端.mdb)中，然后将完成的文件发布到"come-and-get-it"文件夹中，提醒用户，然后他们复制/粘贴新文件前端文件到网络上自己的文件夹中.这样，每个用户都可以在处于停止点"时更新其前端，而不必立即将每个人都引导出去.

我发现当我现在进行开发时，有时Access变得非常慢.例如，当我开发表单并尝试单击属性框上的下拉菜单时，下拉箭头将被推入，但是要花几秒钟的时间才能显示选项列表.否则选择&在窗体上移动控件.或大量键盘滞后.

然后，在其他时候，完全没有延迟.

我想知道是否是因为我链接到与其他用户相同的后端.我确实做出了合理的努力来设置查询，表单，报表等，并且根据需要以最小的记录锁定(如果有的话)进行了最小化.但是我可能错过了一些东西，或者还有一些我需要解决的性能问题.

但是我想知道是否还有更好的方法来设置自己的开发后端.mdb，因此我可以在安全"数据上测试我的代码，而不是与其余数据相同的实时数据的用户.恐怕在最坏的时刻，我破坏一些数据只是时间问题.

很明显，我可以只使用一个独立的后端.mdb并使用链接表管理器每次在前端手动重新配置表链接.但我希望有比这更优雅的解决方案.

我想知道在此多用户拆分数据库配置中是否应该考虑其他性能问题.

我应该补充说，我仍然使用MS Access(不是MS-SQL或任何其他真实"后端)；有关更多详细信息，请参阅我对这篇文章的评论.

解决方案

如果您的所有用户都共享前端，那就是错误的配置.

每个用户都应该拥有一个单独的前端副本.保证共享前端会导致共享前端的频繁损坏，以及前端中表单和模块的奇怪损坏.

对于我来说，目前尚不清楚最终用户使用的前端副本如何开发，因为从A2000开始，这是被禁止的(因为整体保存模型"，即整个VBA)项目存储在系统表之一的单个记录中的单个BLOB字段中.

我真的不认为问题是由使用生产数据引起的(尽管像其他人所说的那样对生产数据进行开发可能不是一个好主意).我认为它们是由于不良的编码习惯和缺乏对前端代码的维护性所致.

1. 在VBE选项中关闭按需编译".

2. 确保您需要OPTION EXPLICIT.

3. 每隔几行代码就频繁地编译您的代码-为了简化此过程，请将COMPILE按钮添加到您的VBE工具栏(在我使用它的同时，我还添加了CALL STACK按钮). /p>

4. 定期对您的前端进行备份，然后反编译并重新编译代码.这是通过使用/decompile开关启动Access，打开前端，关闭Access，使用Access打开前端(按住SHIFT键以跳过启动代码)，然后压缩反编译的前端(使用SHIFT)来实现的.键)，然后编译整个项目并最后压缩一次.您应该在发布任何主要代码之前执行此操作.

其他一些想法:

1. 您没有说这是否是Windows服务器.过去，通过SAMBA访问的Linux服务器出现了问题(尽管有些人向他们发誓，说它们比Windows服务器快得多)，并且从历史上看，Novell服务器需要进行设置调整，以使Jet文件能够可靠地进行编辑.还有一些设置(例如OPLOCKS)可以在Windows服务器上进行调整，以使工作状况更好.

2. 将您的Jet MDB存储在具有短路径的共享中. \ Server \ Data \ MyProject \ MyReallyLongFolderName \ Access \ Databases \的读取数据的速度将比\ Server \ Databases慢得多.这确实有很大的不同.

3. 链接的表存储可能会过时的元数据.有两个简单的步骤，一个非常艰巨的步骤可以解决.首先，压紧后端，然后压紧前端.那很容易.如果这样做没有帮助，请完全删除链接，然后从头开始重新创建它们.

4. 您还可以考虑将MDE分发给最终用户而不是MDB，因为它不能反编译(MDB可以编译).

5. 有关其他广义性能信息，请参见 Tony Toews的性能常见问题解答. .

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.

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.

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.

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.

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.

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.

解决方案

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).

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.

1. turn off COMPILE ON DEMAND in the VBE options.

2. make sure you require OPTION EXPLICIT.

3. 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).

4. 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.

A few other thoughts:

1. 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.

2. 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.

3. 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.

4. you might also consider distributing an MDE to your end users instead of an MDB, as it cannot uncompile (which an MDB can).

5. see Tony Toews's Performance FAQ for other generalized performance information.

这篇关于如何在MS Access中设置开发环境的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！