Just to clarify it's not actually my database and I didn't choose access, I'm just helping a company out by developing some of their already implemented access database.
只是为了澄清它实际上不是我的数据库而我没有选择访问权限，我只是通过开发一些已经实现的访问数据库来帮助公司。
Anyway, on some of their forms the forms open and run extremely slowly for no apparent reason. There is one form which takes a long long time to open on everyone's computer but runs fine when it's there and there's another form that runs fine on most people's computers and is practically unusable on a few.
无论如何，在他们的一些形式上，表格打开并且运行速度极慢，没有明显的原因。有一种形式需要很长时间才能在每个人的计算机上打开，但是当它存在时运行正常并且有另一种形式在大多数人的计算机上运行正常并且几乎无法使用。
The forms have a few subforms on them and no VBA script running in the background that might cause an endless loop and I am stumped for ideas
表单上有几个子表单，后台没有运行VBA脚本，可能会导致无限循环，我很难接受想法
I have turned auto namecorrect off and it came up on one of them saying "The recordset cannot be edited" because of some grouping on one of the text boxes but even when I worked my way around that it still ran just as slow
我已经关闭了自动名称纠正，其中一个人说“记录集无法编辑”，因为在其中一个文本框上进行了一些分组，但即使我一直在处理它仍然运行速度很慢
3 个解决方案
#1
6
Tony Toews's Access Performance FAQ is the best place to start, in my opinion.
在我看来，Tony Toews的Access Performance FAQ是最好的起点。
That said, the problems you describe sound like they fall into two classes:
也就是说，你描述的问题听起来像是分为两类：
A. slow opening forms.
A.开放形式缓慢。
B. slow performing forms.
B.表现缓慢。
There are two common causes for A:
A有两个常见原因：
-
creation of the LDB file/contention for it with existing users. This problem is usually resolved with some form of the solution from Tony's LDB Locking article. You can tell if this is the cause of the problem if opening the first form is slow, and opening successive forms are not slow if you leave the initial one open. FWIW, I don't use that method, but use a persistent database variable that accomplishes the same thing (not the most recent time I've posted the code on SO, but perhaps the one with the best context is here: MS Access: Is there a significant overhead when using CurrentDB as opposed to DBEngine(0)(0)?).
与现有用户一起创建LDB文件/争用。这个问题通常可以通过Tony的LDB Locking文章的某种形式的解决方案来解决。如果打开第一个表单很慢，您可以判断这是否是问题的原因，如果您将初始表单保持打开状态，则打开后续表单并不会很慢。 FWIW，我不使用那个方法，但是使用一个持久的数据库变量来完成同样的事情（不是最近我在SO上发布代码的时候，但也许是具有最佳上下文的那个在这里：MS Access：使用CurrentDB而不是DBEngine（0）（0）会有很大的开销吗？）。
-
out-of-date metadata in linked tables. This can happen if, for instance, you work on the front end on your test server, move it to the production environment and update the connect strings to point to the production back end. Updating the connect string doesn't refresh all the metadata stored in the linked table definitions, and there's no way to actually fully refresh them. So, you have to delete and recreate the linked tables in the production environment. The symptom of this one is that in the test environment forms open immediately or in only a second or two, and in the production environment take a minute or more to open. After opening, they generally work just fine. FWIW, I haven't really seen this problem except in the earliest days of Access 2000 when it was a significant and terrible problem that almost cost me a job (my first A2000 project).
链接表中的过时元数据。例如，如果您在测试服务器的前端工作，将其移动到生产环境并更新连接字符串以指向生产后端，则会发生这种情况。更新连接字符串不会刷新链接表定义中存储的所有元数据，也无法实际完全刷新它们。因此，您必须在生产环境中删除并重新创建链接表。这种情况的症状是在测试环境中立即打开或仅在一两秒内打开，并且在生产环境中需要一分钟或更长时间才能打开。打开后，他们通常工作得很好。 FWIW，我没有真正看到这个问题，除了在Access 2000的早期，这是一个重大而可怕的问题，几乎让我失业（我的第一个A2000项目）。
Slow-performing forms is more complicated to fix, but the cause is usually pretty uncomplicated: the forms are loading too much data at once. Forms with lots of subforms (usually on a tab control) and lots of large combo boxes are the usual culprit. The solution is to not load the subforms/combo boxes until they are actually displayed. In a tab control that means loading the subform for each tab in the tab control's OnChange event. For combo boxes, you'd load them when they are displayed, or if they have too many records in them (over 1000, I'd say), don't load a rowsource until after the user has typed 1 or 2 characters (using the OnChange event of the combo box).
表现缓慢的表单修复起来比较复杂，但原因通常很简单：表单一次加载太多数据。具有大量子表单（通常在选项卡控件上）和许多大型组合框的表单通常是罪魁祸首。解决方案是在实际显示子窗体/组合框之前不加载它们。在选项卡控件中，这意味着在选项卡控件的OnChange事件中为每个选项卡加载子窗体。对于组合框，你在显示它们时加载它们，或者如果它们中有太多记录（超过1000，我会说），在用户键入1或2个字符之前不要加载行源（使用组合框的OnChange事件）。
The problem with that is that you're trading one major slowdown (loading all that stuff when the form is first opened) for a number of much smaller slowdowns (loading each subform/rowsource as its needed). It's a trade-off and you have to decide where you want your pain.
问题在于，您正在进行一次主要的减速（在首次打开表单时加载所有内容）进行一些小得多的减速（根据需要加载每个子表单/行源）。这是一个权衡，你必须决定你想要痛苦的地方。
There are lots of other things to do, and one thing to examine early on in troubleshooting performance problems is the Recordsource of each main form. Left joins can get really expensive performance-wise and it's a good idea to eliminate any of them that aren't absolutely required. I just recently vastly speeded up a form that had a left join to the parent table from the child table. It was impossible for the child to exist without a parentID in the PK field in the child table, so that left join was completely unnecessary. Removing it really speeded up navigation from record to record.
还有很多其他事情要做，在故障排除性能问题的早期检查的一件事是每个主要表单的Recordsource。左连接可以在性能方面变得非常昂贵，并且最好消除任何非绝对需要的连接。我最近刚刚加速了一个从子表左边连接到父表的表单。在子表的PK字段中，子节点不可能存在父ID，因此完全不需要左连接。删除它确实加快了从记录到记录的导航。
#2
0
Identify the recordsource from the form properties (table/query/sql statement) and run this directly on the affected machine/s. That will help to narrow down the problem as either form or recordsource. You may for example find that one of the forms is referencing a particularly slow query (large tables, multiple joins, dlookups etc) in which case you would need to focus on optimising this.
从表单属性（table / query / sql语句）中识别记录源，并直接在受影响的计算机上运行它。这将有助于将问题缩小为形式或记录源。例如，您可能会发现其中一个表单引用了一个特别慢的查询（大表，多个连接，dlookup等），在这种情况下，您需要专注于优化它。
#3
0
You might like to read: http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx
您可能希望阅读：http：//office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx
#1
6
Tony Toews's Access Performance FAQ is the best place to start, in my opinion.
在我看来，Tony Toews的Access Performance FAQ是最好的起点。
That said, the problems you describe sound like they fall into two classes:
也就是说，你描述的问题听起来像是分为两类：
A. slow opening forms.
A.开放形式缓慢。
B. slow performing forms.
B.表现缓慢。
There are two common causes for A:
A有两个常见原因：
-
creation of the LDB file/contention for it with existing users. This problem is usually resolved with some form of the solution from Tony's LDB Locking article. You can tell if this is the cause of the problem if opening the first form is slow, and opening successive forms are not slow if you leave the initial one open. FWIW, I don't use that method, but use a persistent database variable that accomplishes the same thing (not the most recent time I've posted the code on SO, but perhaps the one with the best context is here: MS Access: Is there a significant overhead when using CurrentDB as opposed to DBEngine(0)(0)?).
与现有用户一起创建LDB文件/争用。这个问题通常可以通过Tony的LDB Locking文章的某种形式的解决方案来解决。如果打开第一个表单很慢，您可以判断这是否是问题的原因，如果您将初始表单保持打开状态，则打开后续表单并不会很慢。 FWIW，我不使用那个方法，但是使用一个持久的数据库变量来完成同样的事情（不是最近我在SO上发布代码的时候，但也许是具有最佳上下文的那个在这里：MS Access：使用CurrentDB而不是DBEngine（0）（0）会有很大的开销吗？）。
-
out-of-date metadata in linked tables. This can happen if, for instance, you work on the front end on your test server, move it to the production environment and update the connect strings to point to the production back end. Updating the connect string doesn't refresh all the metadata stored in the linked table definitions, and there's no way to actually fully refresh them. So, you have to delete and recreate the linked tables in the production environment. The symptom of this one is that in the test environment forms open immediately or in only a second or two, and in the production environment take a minute or more to open. After opening, they generally work just fine. FWIW, I haven't really seen this problem except in the earliest days of Access 2000 when it was a significant and terrible problem that almost cost me a job (my first A2000 project).
链接表中的过时元数据。例如，如果您在测试服务器的前端工作，将其移动到生产环境并更新连接字符串以指向生产后端，则会发生这种情况。更新连接字符串不会刷新链接表定义中存储的所有元数据，也无法实际完全刷新它们。因此，您必须在生产环境中删除并重新创建链接表。这种情况的症状是在测试环境中立即打开或仅在一两秒内打开，并且在生产环境中需要一分钟或更长时间才能打开。打开后，他们通常工作得很好。 FWIW，我没有真正看到这个问题，除了在Access 2000的早期，这是一个重大而可怕的问题，几乎让我失业（我的第一个A2000项目）。
Slow-performing forms is more complicated to fix, but the cause is usually pretty uncomplicated: the forms are loading too much data at once. Forms with lots of subforms (usually on a tab control) and lots of large combo boxes are the usual culprit. The solution is to not load the subforms/combo boxes until they are actually displayed. In a tab control that means loading the subform for each tab in the tab control's OnChange event. For combo boxes, you'd load them when they are displayed, or if they have too many records in them (over 1000, I'd say), don't load a rowsource until after the user has typed 1 or 2 characters (using the OnChange event of the combo box).
表现缓慢的表单修复起来比较复杂，但原因通常很简单：表单一次加载太多数据。具有大量子表单（通常在选项卡控件上）和许多大型组合框的表单通常是罪魁祸首。解决方案是在实际显示子窗体/组合框之前不加载它们。在选项卡控件中，这意味着在选项卡控件的OnChange事件中为每个选项卡加载子窗体。对于组合框，你在显示它们时加载它们，或者如果它们中有太多记录（超过1000，我会说），在用户键入1或2个字符之前不要加载行源（使用组合框的OnChange事件）。
The problem with that is that you're trading one major slowdown (loading all that stuff when the form is first opened) for a number of much smaller slowdowns (loading each subform/rowsource as its needed). It's a trade-off and you have to decide where you want your pain.
问题在于，您正在进行一次主要的减速（在首次打开表单时加载所有内容）进行一些小得多的减速（根据需要加载每个子表单/行源）。这是一个权衡，你必须决定你想要痛苦的地方。
There are lots of other things to do, and one thing to examine early on in troubleshooting performance problems is the Recordsource of each main form. Left joins can get really expensive performance-wise and it's a good idea to eliminate any of them that aren't absolutely required. I just recently vastly speeded up a form that had a left join to the parent table from the child table. It was impossible for the child to exist without a parentID in the PK field in the child table, so that left join was completely unnecessary. Removing it really speeded up navigation from record to record.
还有很多其他事情要做，在故障排除性能问题的早期检查的一件事是每个主要表单的Recordsource。左连接可以在性能方面变得非常昂贵，并且最好消除任何非绝对需要的连接。我最近刚刚加速了一个从子表左边连接到父表的表单。在子表的PK字段中，子节点不可能存在父ID，因此完全不需要左连接。删除它确实加快了从记录到记录的导航。
#2
0
Identify the recordsource from the form properties (table/query/sql statement) and run this directly on the affected machine/s. That will help to narrow down the problem as either form or recordsource. You may for example find that one of the forms is referencing a particularly slow query (large tables, multiple joins, dlookups etc) in which case you would need to focus on optimising this.
从表单属性（table / query / sql语句）中识别记录源，并直接在受影响的计算机上运行它。这将有助于将问题缩小为形式或记录源。例如，您可能会发现其中一个表单引用了一个特别慢的查询（大表，多个连接，dlookup等），在这种情况下，您需要专注于优化它。
#3
0
You might like to read: http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx
您可能希望阅读：http：//office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx