sql-server - 无法在 Excel 中使用传递查询

Question

我已经搜索了好几天的多个论坛，但仍然卡住了。希望有人可以在这里有所启发。

我对 MS Office 和原生 SQL 之间的 SQL 语法差异越来越感到沮丧，并且我被引导相信使用传递查询将允许我使用原生 SQL。我尝试了来自各个论坛的多个建议来创建直通查询，但我的查询中仍然遇到 Office(语法)错误。

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

为了完整起见，我正在使用:

SQL Server 2014

微软 Excel 2013

Microsoft DAO 3.6 对象库

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

完整的示例(在 OpenRecordSet 上失败)代码如下。我将永远感激可以提供的任何帮助。

谢谢，
瑞安

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

Answer 1

指定 dbSQLPassthrough记录集行中的选项。如果没有此指定，JET/ACE DAO 引擎将使用其自己的 SQL 方言并因此解释 ISNULL()作为逻辑函数而不是 SQL Server 的 ISNULL()作为值(value)函数。下面不使用querydef直接打开记录集:

DAO 连接

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

ADO 连接

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

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