MS Access 查询设计因与 SQL Server 的连接而挂起
Microsoft Access 是一种访问 MS SQL Server 后端数据库中数据的灵活方法，但我在访问（可以这么说）大型数据表时总是遇到问题，尤其是在 Access 中尝试在结果和设计模式之间切换时。
Access 为我提供了许多漂亮的东西，其中最重要的是交叉表，但是与服务器的挂起连接让我有点疯狂！
是否有任何 MS Access 专家知道如何优化 ODBC 连接，以便当我只想调整和构建查询时，它不会执行看似全表扫描的操作？
Microsoft Access is a slick way to access data in a MS SQL Server backend database, but I've always had problems accessing (so to speak) large tables of data, especially when trying to toggle between results and design mode in Access.
Access gives me a number of nifty things, not the least of which is Crosstabs, but this hung connection to the server drives me a little crazy!
Does any MS Access gurus know how to optimize the ODBC connection so it isn't doing what appears to be full table scans when I just want to tweak and build my queries?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
ODBC 驱动程序会将尽可能多的工作传递给 SQL Server，但是一旦您使用 VBA 函数（如 Nz）或非 SQL Server 语法（如 PIVOT），则 ODBC 驱动程序必须拉回更多数据和索引以完成 SQL Server 上的工作。客户端。
根据其他答案，要么在 SQL Server 中构建视图并链接到视图，要么使用 Access 数据项目。
注意：在 SQL Server 中，无法以 Access 本身执行此操作的方式处理列数未知的 PIVOT 查询 - 因此，如果您在 Access 中针对 SQL Server 数据运行数据透视，则可能会拉回整个表。 数据透视查询必须使用动态 SQL 技术或对所有列进行硬编码的预保存视图在 SQL Server 中构建。 查看此链接以了解执行此操作的一种方法：
http://www.sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/" rel="nofollow noreferrer"> sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/
The ODBC driver will pass as much work as possible to SQL Server but as soon as you use a vba function like Nz or non-SQL Server syntax like PIVOT then the ODBC driver must pull back more data and indexes to get the work done on the client side.
As per other answer either build your views in SQL Server and link to the views or else use an Access Data Project.
NB: PIVOT queries with unknown number of columns cannot be handled in SQL Server in the same way that Access will do this natively - so if you run a pivot in Access against SQL Server data you will likely pull the whole table back. Pivot queries must be built in SQL Server using dynamic SQL techniques or else pre-saved views that have all the columns hard coded. Check out this link for one way to do this:
http://www.sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/
正如其他人所说，提高大型表性能的唯一方法是让 SQL Server 数据库引擎为您完成这项工作。 执行此操作的一种方法（尚未提及）是使用传递查询，这将使您能够将所有代码保留在 MS Access 中，而无需在 SQL Server 上创建对象：
http://support.microsoft.com/kb/303968
您必须编写 SQL Server T-SQL 而不是 Access方言; 但是，SQL 2005（在兼容模式 90 下运行时）确实支持 PIVOT 命令。
As others have said, the only way to improve performance on large tables is to have the SQL Server database engine do the work for you. A method of doing this which hasn't been mentioned is to use a pass-through query, which will enable you to keep all your code in MS Access, without having to create objects on the SQL Server:
http://support.microsoft.com/kb/303968
You will have to write SQL Server T-SQL rather than the Access dialect; however, SQL 2005 (when running in compatibility mode 90) does support a PIVOT command.
我的类似问题是选择链接表/ODBC 连接后 ORACLE ODBC 连接挂起。 任务管理器说 10 分钟后没有响应。 然后该连接会对 ORACLE 执行 ping 操作以获取所有可用表。 我已经在 ORACLE ODBC 管理器上打开了日志记录，因此它必须将所有这些内容写入日志，这可能会导致结果延迟几个小时。 一小时后日志为 60 MB，当我关闭它时，一切都很好！
要关闭它，请转到 Oracle 安装/网络管理/MS ODBC 管理员/跟踪选项卡并将其关闭！
关于 ODBC 的一个很好的资源在这里：http://eis.bris.ac.uk /~ccmjs/odbc_section.html
My similar problem was that the ORACLE ODBC connection hung after selecting the Link table/ODBC connection. Task manager said not responding after 10's of minutes. The connection then pings ORACLE for all available tables. I had turned on logging on the ORACLE ODBC Administrator, so it had to write all these things to the log, slowing any results by perhaps hours. The log was 60 MB one hour later, when I turned it off, then everything was fine!
To turn it off go to the Oracle installation/Network Administration/MS ODBC Adminstrator/Tracing tab and turn it OFF!
A good resource on ODBC is here: http://eis.bris.ac.uk/~ccmjs/odbc_section.html
不幸的是，Access 无法将大量工作推送到服务器，并且在针对 SQL Server 中的多个表或视图设计查询时，它会执行大量表扫描。
您可以使用 SSMS 在 SQL Server 中构建和调整查询（视图），并将视图存储在 SQL Server 中以大幅提升性能，并且仍然使用 Access 作为前端。
Unfortunately Access is not able to push a lot of that work to the server, and yes, it will do huge table scans when designing queries against multiple tables or views in SQL Server.
You can build and tweak queries (views) in SQL Server using SSMS and store the views in SQL Server for a massive performance boost and still use Access for your front end.