☰

Excel VBA：SQL 查询处理错误

tag-icon • tag-icon microsoft-excel vba sql
Excel VBA：SQL 查询处理错误

我有可以在 Excel 2013 中使用的 SQL，可以从 Access 数据库获取信息，并且它可以直接在连接中使用 SQL，但现在我想从 VBA 运行它，这样我就可以使用 SQL 中的一些变量。

我找到了这个链接，对其进行了修改，它适用于一些比较简单的 SQL 语句，但对其他语句不起作用。

使用 VBA 执行 SQL 查询并用数据填充工作表

我收到一个错误：

运行时错误 -2147217883 (80040e25) 必须先释放所有行句柄，然后才能获取新的行句柄。

我搜索了很多有关此错误的信息，并尝试了不同的方法，但显然不明白。我知道 SQL 很长/很复杂，但它可以直接在 Access 和 Excel 中使用。

这是当前代码：

Sub Get_SQLData()
'modified from from https://superuser.com/questions/813077/execute-sql-query-with-vba-and-fill-sheet-with-data
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String

strFile = "C:\mydb.accdb"

strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFile

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon

'JAJ set my SQL RAW first
strSQL = "SELECT RAWDATA_Incidents.ID, RAWDATA_Incidents.[Incident Number], RAWDATA_Incidents.[Categorization Tier 1], RAWDATA_Incidents.[Categorization Tier 2], RAWDATA_Incidents.[Categorization Tier 3], RAWDATA_Incidents.Priority, RAWDATA_Incidents.Urgency, RAWDATA_Incidents.Impact, RAWDATA_Incidents.[Reported Date], RAWDATA_Incidents.[Service Type], RAWDATA_Incidents.[Closure Product Category Tier1], RAWDATA_Incidents.[Closure Product Category Tier2], RAWDATA_Incidents.[Closure Product Category Tier3], ClosureProductName.ClosureProductName, RAWDATA_Incidents.Status, RAWDATA_Incidents.[Closed Date], RAWDATA_Incidents.[Product Name], OpsCatTreeFaultMode.FaultMode, BusinessService.MMServiceID, ([RAWDATA_Incidents]![Closed Date]-[RAWDATA_Incidents]![Reported Date])*1440 AS Expr2, IIf([RAWDATA_Incidents]![Priority]='Critical' Or [RAWDATA_Incidents]![Priority]='High',788,394) AS Expr3, BusinessService.Name, BSDependsOnAC.MMServiceID, CI.CIName, AccessChannel.Name, BusinessService.ID " _
    & "FROM OpsCatTreeFaultMode INNER JOIN (RAWDATA_Incidents INNER JOIN (CI INNER JOIN ((ITSystemService INNER JOIN (BusinessService INNER JOIN ((AccessChannel INNER JOIN ACDependsOnITSS ON AccessChannel.ACID = ACDependsOnITSS.ACID.Value) INNER JOIN BSDependsOnAC ON AccessChannel.ACID = BSDependsOnAC.ACID.Value) ON BusinessService.ID = BSDependsOnAC.MMServiceID.Value) ON (ITSystemService.ITSSID = ACDependsOnITSS.ITSSID.Value) AND (ITSystemService.ITSSID = ACDependsOnITSS.ITSSID.Value)) INNER JOIN ClosureProductName ON ITSystemService.ITSSID = ClosureProductName.ITSS.Value) ON CI.CIID = ITSystemService.CIID) ON RAWDATA_Incidents.[Closure Product Name] = ClosureProductName.ClosureProductName) ON OpsCatTreeFaultMode.OpsCatTreeName = RAWDATA_Incidents.[Categorization Tier 3]"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn

rs.Open strSQL

Sheet1.Range("A1").CopyFromRecordset rs     ' fails here  - gives handles error.

rs.Close
cn.Close
Set cn = Nothing

End Sub

任何帮助，将不胜感激。

答案1

我认为你可能有一些冗余：

Sub Get_SQLData()

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String

strFile = "C:\mydb.accdb"
strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFile
Set cn = CreateObject("ADODB.Connection")
cn.Open strCon  

'JAJ set my SQL RAW first 
strSQL = "..."

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL

Sheet1.Range("A1").CopyFromRecordset rs     ' fails here  - gives handles error.
rs.Close
cn.Close

Set cn = Nothing
End Sub

你的问题从这里开始：

rs.activeconnection = cn

你已经声明

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon

您使用 rs 指向已创建并打开的对象。请实例化新连接，而不是尝试克隆它。您在此处指向一个对象。

相关内容