将VBA变量传递到访问查询(Excel VBA) [英] Pass VBA Variable into Access Query(Excel VBA)

问题描述

我是新手，尝试从vba构造查询.我试图弄清楚如何在VBA语法内传递变量.介意告诉我我在哪里傻吗?

Im new at trying to construct queries out of vba. I am trying to figure out how to pass a variable inside the VBA syntax. Mind showing me where im dumb?

我在下面尝试了此操作，但是弹出了一个自动化错误.我已经从aroudn的播放中注意到，当您语法错误时会出现自动化错误，所以希望它有点小?

I tried this below but there's an automation error that pops up. Ive noticed from playing aroudn that automation errors come up when youve just got syntax wrong, so hopefully its something small?

非常感谢您的帮助

Sub GetDataFromAccess()
   Dim cmd As New ADODB.Command, rs As ADODB.Recordset
   Dim recordNum As Integer

   recordNum = 7

   cmd.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ashleysaurus\Desktop" & "\" & "xyzmanu3.accdb"
   cmd.CommandType = adCmdText

   cmd.CommandText = "SELECT * FROM Invoice WHERE OrderNumber <" & "'" & recordNum & "'" & "ORDER BY OrderNumber ASC"

   Set rs = cmd.Execute
   Sheet1.Range("A2").CopyFromRecordset rs

   rs.Close
   cmd.ActiveConnection.Close

   Debug.Print "Done!"
End Sub

推荐答案

假定OrderNumber是一个数字，请不要使用引号. 另外，请确保在排序依据"之前有一个空格:

Assuming OrderNumber is a number, do not use quotes. Also make sure you have a space before Order By:

cmd.CommandText = "SELECT * FROM Invoice WHERE OrderNumber <" & recordNum & " ORDER BY OrderNumber ASC"

这篇关于将VBA变量传递到访问查询(Excel VBA)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！