将Excel链接到Access查询 [英] Linking Excel to Access Query

问题描述

我在Access（2007）中有一个查询（带有计算字段），数据会根据所选的日期和工作人员而变化。我需要根据此查询中的数据生成一系列图形，在我看来，Excel比Access更容易创建图形。


当我使用获取外部数据时Excel的功能和带有表和查询的对话框出现，我不需要列出我需要的查询。这是因为查询有计算字段还是其他原因？我可以找到基础表，但后来数据没有被过滤，当然，没有计算字段。


我相信可以使用vba建立连接，但我不知道如何要做到这一点。


如果有帮助的话，这是我查询的sql。

I have a query (with calculated fields) in Access (2007) and the data changes depending on the dates and staff person selected. I need to produce a series of graphs based on the data in this query and it seems to me that Excel is far easier to create graphs than Access.

When I use the Get External Data feature of Excel and the dialog box with the tables and queries appears, the query I need is not listed. Is this because the query has calculated fields or some other reason? I can find the underlying table but then the data is not filtered and of course, there are no calculated fields.

I believe connections can be made using vba but I have no idea of how to do this.

This is my sql for the query if that helps.

展开 | 选择 | Wrap | 行号

推荐答案

确定


我找到了一些代码可以完成这项工作但我得到的运行时错误3061太几个参数预期1条消息。


这是代码：

OK

I have found some code that may do the job but I am getting a Runtime Error 3061 Too few parameters Expected 1 message.

This is the code:

展开 | 选择 | Wrap | 行号

嗨。您需要指定涉及哪种记录集 - DAO或ADO。这两种类型都可以使用Excel CopyFromRecordset方法，但是当Access本身变得混乱时会出现参数错误。


将变量RS的DIM更改为

Hi. You will need to specify which kind of recordset is involved - DAO or ADO. Either type will work with the Excel CopyFromRecordset method, but the parameter error arises when Access itself becomes confused over which you are using.

Change the DIM for variable RS to

展开 | 选择 | Wrap | 行号

谢谢Stewart


我试过但仍然收到错误。我还尝试按照您的建议勾选Microsoft DAO 3.6对象库引用，但我收到一条消息，说它与现有的模块，项目或对象库冲突。


在引用下我有以下内容打勾：

Visual Basic for applications

Microsoft Acces 12对象库

OLE自动化

Microsoft Office 12 Access数据库引擎对象库

Microsoft Forms 2对象库

Thanks Stewart

I tried that but still get the error. I also tried to tick the Microsoft DAO 3.6 Object Library reference as you suggested but I get a message saying it conflicts with existing Module, project or object Library.

Under references I have the following ticked:
Visual Basic for applications
Microsoft Acces 12 Object Library
OLE automation
Microsoft Office 12 Access database engine object library
Microsoft Forms 2 Object library

这篇关于将Excel链接到Access查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！