使用链接表访问连续表单-如何避免为表单中的每一行击中数据库服务器? [英] Access Continuous Form with Linked Table - How to Avoid Hitting Database Server for Every Row in Form?

问题描述

我正在通过SQL Server迁移助手(SSMA)将数据从Access数据库迁移到SQL Server. Access应用程序将继续与将本地表转换为链接表一起使用.

I'm migrating the data from an Access database to SQL Server via the SQL Server Migration Assistant (SSMA). The Access application will continue to be used with the local tables converted to linked tables.

一个连续的表格在加载时会挂起15-30秒.它显示大约2000条记录.当我查看SQL Server Profiler来查看其工作时，它正在为表单中的每个记录单独调用后端数据库.因此，打开表单的延迟是由对数据库的2000多次单独调用引起的.

One continuous form hangs for 15 - 30 seconds when it's loading. It displays approximately 2000 records. When I looked in SQL Server Profiler to see what it was doing, it was making a separate call to the backend database for each record in the form. So the delay when the form opens is caused by the 2000-odd separate calls to the database.

这是非常低效的.是否有任何方法可以使Access一次调用后端数据库并立即检索所有记录?

This is amazingly inefficient. Is there any way to get Access to make a single call to the backend database and retrieve all the records at once?

我不知道这是否相关，但是表单的记录源是SQL Server后端数据库中的一个视图，该视图是通过Access链接表链接的(因此，希望Access只是将其视为表) ，而不是视图).在SQL Server的视图中，我需要一个Not Of触发器，在Access中的链接表上需要一个唯一索引，以允许通过表单更新记录.

I don't know if this is relevant but the Record Source for the form is a view in the SQL Server backend database, which is linked to via an Access linked table (so, hopefully, Access just sees it as a table, not a view). I needed an Instead Of trigger on the view in SQL Server, and a unique index on the linked table in Access, to allow the records to be updated via the form.

推荐答案

原来的问题是两个聚合字段.一个字段的控制源为= Count(ID)，另一字段的控制源为= Sum(Total_Qty).

Turned out the problem was two aggregate fields. One field's Control Source was =Count(ID) and the other field's Control Source was =Sum(Total_Qty).

清除这两个字段的控制源可以使表单快速打开.如Gord Thompson所述，SQL Server Profiler显示它调用sp_execute一次检索7个批处理，每批10行.比一次进行2000次调用来一次检索一行要快得多.

Clearing the control sources of those two fields allowed the form to open quickly. SQL Server Profiler shows it calling sp_execute, as Gord Thompson described, to retrieve seven batches of 10 rows at a time. Much quicker than making 2000 calls to retrieve one row at a time.

这篇关于使用链接表访问连续表单-如何避免为表单中的每一行击中数据库服务器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！