无法在Excel中使用传递查询 [英] Cannot use pass through queries in Excel

问题描述

我已经搜寻了多个论坛几天了，但仍然陷于困境.希望有人可以在这里阐明一些观点.

I've scoured multiple forums for days now and still stuck. Hoping somebody can shed some light here.

我越来越对MS Office和本机SQL之间的SQL语法差异感到沮丧，并且我被认为使用直通查询将允许我使用本机SQL.我已经尝试过各种论坛的多个建议来创建直通查询，但查询中仍然会遇到Office(语法)错误.

I am increasingly frustrated by SQL syntax differences between MS Office and native SQL, and I've been led to believe that using pass through queries will allow me to use native SQL. I've tried multiple suggestions from various forums to create a pass through query, but I am still faced with Office (syntax) errors in my queries.

下面是我的代码的一个简单示例，由于ISNULL语法，Excel/VBA不喜欢我的代码.请注意，不是ISNULL本身就是问题，我知道如何解决.这仅是示例.问题在于它应该在本机SQL中运行(并且在SQL Server Management Studio中也可以运行).

Below is a simple example of my code, which Excel/VBA does not like, due to the ISNULL syntax. Please note, it isn't ISNULL itself that is the problem, I know how to work around that. This is just by way of example. The problem is that it should work in native SQL (and it does in SQL Server Management Studio).

出于完整性考虑，我正在使用:

For completeness, I am using:

• SQL Server 2014

• SQL Server 2014

MS Excel 2013

MS Excel 2013

Microsoft DAO 3.6对象库

Microsoft DAO 3.6 Object library

我怀疑连接字符串或DAO对象库可能是罪魁祸首，但我尝试了多个其他操作，但结果相同.

I suspect the connection string or the DAO object library may be the culprit, but I've tried multiple others with the same result.

下面是完整的示例代码(在OpenRecordSet上失败).我将永远感激能够提供的任何帮助.

The complete sample (failing on OpenRecordSet) code follows. I would be eternally grateful for any help that can be offered.

谢谢，瑞安

Option Explicit

Sub TestQuerySQL()
Dim sqlConnect As String, dsnName As String, dbName As String, sqlString As String, db As Database, qd As QueryDef, rs As Recordset

   dsnName = "MyDSN"
   dbName = "MyDatabaseName"

   sqlConnect = "ODBC;DSN=" & dsnName & ";Trusted_Connection=yes;"
   sqlString = "Select isnull(d.Name, '???') as DealerName from Dealer d"

   Set db = OpenDatabase(dbName, dbDriverNoPrompt, True, sqlConnect)

   On Error Resume Next
   Set qd = db.CreateQueryDef("", sqlString)

   If Err.Number <> 0 Then
      MsgBox "CreateQueryDef failed. SQL=>" & sqlString & "< " & Err.Number & " Err=>" & Err.Description & "<", vbCritical
   Else
      qd.ReturnsRecords = True

      Set rs = qd.OpenRecordset(dbOpenSnapshot, dbReadOnly)

      If Err.Number <> 0 Then
         MsgBox "OpenRecordset Failed. SQL=>" & sqlString & "< Err=>" & Err.Description & "<", vbCritical
      Else
         MsgBox "Success"
         'do someting with the results
      End If
   End If
End Sub

推荐答案

指定

Specify the dbSQLPassthrough option in the recordset line. Without this designation, the JET/ACE DAO Engine uses its own SQL dialect and hence interprets ISNULL() as the logical function and not SQL Server's ISNULL() as the value function. Below directly opens the recordset without using querydef:

DAO连接

Set db = OpenDatabase(dbName, dbDriverNoPrompt, True, sqlConnect)
Set rs = db.OpenRecordset(sqlString, dbOpenDynaset, dbSQLPassThrough)

ADO连接

或者，使用ADO连接，在该连接中可以读取任何外部SQL引擎的方言:

Alternatively, use an ADO connection where any external SQL engine's dialect can be read:

Dim conn As New ADODB.Connection, rst As New ADODB.Recordset
Dim sqlConnect As String, sqlString As String

' REFERENCE THE MICROSOFT ACTIVEX DATA OBJECTS XX.X LIBRARAY '
sqlConnect = "ODBC;DSN=" & dsnName & ";Trusted_Connection=yes;"
sqlString = "Select isnull(d.Name, '???') as DealerName from Dealer d"

conn.Open sqlConnect
rst.Open sqlString, conn

这篇关于无法在Excel中使用传递查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！