访问滞后于SQL Server [英] Access Lagging behind SQL Server

问题描述

我正在尝试将表中保存的查询作为T-SQL导出为ex​​cel。

想法是让管理员编写查询，这将由其他人员导出。 />

视图和表确实正确创建，因为我可以在ssms中看到它们，但是我得到运行时错误7874 - 找不到对象。


我唯一能想到的是访问落后于sql server，数据库窗口没有刷新。


这是在SQL Server 2008上的Access 2007上运行的ADP文件标准服务器。

I am trying to export a query held within a table as T-SQL out to excel.
The idea is to have admins write the Queries, which will be exported by other personnel.

The View and Table do create correctly as I can see them within ssms, however I get a runtime error 7874 - cannot find object.

The only thing i can think of is that access is lagging behind sql server, and the database window is not being refreshed.

This is an ADP file running on Access 2007 on a SQL Server 2008 Standard Server.

展开 | 选择 | Wrap | 行号

推荐答案

1. 这可能是一个愚蠢的问题，但我必须问一下，你有一个参考设置到Microsoft Excel对象库吗？
2. 您是否尝试设置断点然后单步执行代码确切地看到错误的生成位置？

Hiya，


不是一个愚蠢的问题： ）


我引用了excel库。


我设置了一个断点，错误即将出现（偶尔）在docmd .transferspreadsheet line。


如果我再次运行脚本（错误之后），就会生成电子表格。


干杯谢谢


Leon

Hiya,

not such thing as a dumb question :)

I have the excel library referenced.

I have set a breakpoint, and the error is coming up (occasionally) at the docmd.transferspreadsheet line.

If i run the script again (after the error), the spreadsheet is produced.

Cheers and Thanks

Leon

嗨。我确定这与Access无关;导致问题的是Excel自动化。原因是您在许多位置将隐式引用到当前活动工作表对象（第56行到第81行）。这在Excel中运行代码时有效，因为当前活动的工作表或工作簿用于隐式引用。当从Excel外部运行引用Excel对象的代码时，它无法可靠地运行。


无论何时想要设置或引用范围，都必须明确地引用自动化服务器对象或选择。在第53行中有一个WITH，它允许你在不引用xlApp的情况下执行此操作，但是在引用选择，行等时（第56行以后）不使用该对象的属性。


例如，第56到61行可以替换如下：

Hi. I''m sure this is nothing to do with Access as such; it is the Excel automation that is causing the problem. Reason is that you are referring implicitly in many locations to the current active worksheet object (lines 56 through 81). This works when running code in Excel, as the currently-active worksheet or workbook is taken for the implicit references. It does not work reliably when running code referencing the Excel object from outside of Excel itself.

You MUST refer explicitly to the automation server object whenever you want to set or refer to a range or selection. You have a WITH in line 53 which will allow you to do this without referring to xlApp all the time, but you then do not use the properties of that object when referring to selections, rows etc (lines 56 onwards).

For example, lines 56 to 61 can be replaced as follows:

展开 | 选择 | 换行 | 行号

这篇关于访问滞后于SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！