如何将参数发送到Microsoft Access查询，以便我可以将访问参数查询导入到Excel? [英] How to send parameters to microsoft access query so that I can import an access parameter query to excel?

问题描述

我需要将具有弹出输入参数的Microsoft Access查询导入Excel.我尝试了下面的代码，但它不起作用.我收到错误93，该错误告诉我未设置对象或对象变量.

I need to import a microsoft access query that has popup input parameters into excel. I tried the code below but it does not work. I receive error 93 that tells me that object or object variable is not set.

我希望能够在excel中引用两个包含当前月和上个月值的单元格，然后将这些值作为输入发送给访问查询，但是为了简化起见，我现在在VBA中输入了它们.

I would like to be able to reference two cells in excel that contain the values of the current and previous month and then send these values as inputs to the access query, but for now I entered them in VBA to keep it simple.

任何帮助将不胜感激！

谢谢！

Sub Acess_Connection()
     Dim dbs As DAO.Database
     Dim rst As DAO.Recordset
     Dim qdf As DAO.QueryDef
     Dim i As Long
     Dim wsh As Worksheet

     Set dbs = DBEngine.OpenDatabase("filepath")                         
     Set qdf = dbs.QueryDefs("parameter_query")

     qdf.Parameters("Date_PreviousMonth") = "31.12.2018"
     qdf.Parameters("Date_CurrentMonth") = "31.01.2019"

     Set rst = qdf.OpenRecordset("parameter_query")          
     Set wsh = Worksheets("Sheet1")

     For i = 0 To rst.Fields.Count - 1   
         wsh.Cells(1, i + 1).Value = rst.Fields(i).Name      
     Next

     wsh.Range("A1").Resize(ColumnSize:=rst.Fields.Count).Font.Bold = True
     wsh.Range("A2").CopyFromRecordset rst

     rst.Close
     Set rst = Nothing
     dbs.Close
     Set dbs = Nothing
 End Sub

推荐答案

我通过一个非常简单的查询通过VBA测试了设置查询参数，并且可以进行以下调整.

I tested setting query parameters via VBA with a very simple query and it works with following adjustments.

1. 查询对象必须在适当的字段下具有PARAMETERS子句和参数

1. query object must have PARAMETERS clause and parameters under appropriate field(s)

将#分隔符用于日期条件#12/31/2018#

use # delimiters for date criteria #12/31/2018#

Set rst = qdf行未使用查询名称作为参数，变量qdf提供了正确的名称以 Set rst = qdf.OpenRecordset()将使用默认记录集类型.

Set rst = qdf line does not use query name as argument, the variable qdf provides the name so correct to Set rst = qdf.OpenRecordset() which will use the default recordset type.

要进行早期绑定，请至少在VBA编辑器中选择Microsoft Office 14.0 Access Database Engine Object Library，以获取最新版本的Excel

for early binding, select Microsoft Office 14.0 Access Database Engine Object Library in VBA editor, at least for more recent versions of Excel

这篇关于如何将参数发送到Microsoft Access查询，以便我可以将访问参数查询导入到Excel?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！