Cannot use pass through queries in Excel

Question

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

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.

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
MS Excel 2013
Microsoft DAO 3.6 Object library

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

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

Thanks, Ryan

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

Forgot to mention, in case it helps, the error message I get is "Wrong number of arguments used with function in query expression 'isnull(d.Name,'???')'", which is the result of Excel/Access using a different syntax for this function. — Ryan Poth
– Ryan Poth, Commented Mar 10, 2016 at 1:45
SQL is a language than no single entity owns and various RDMS maintain different dialects. So, MS Access SQL will differ from SQL Server SQL as does MySQL/Postgre/DB2/SQLite SQL. So there is no true native SQL. — Parfait
– Parfait, Commented Mar 10, 2016 at 3:25
ISNULL() is used in Access and MSSQL but in different implementations. For Access, this function with no arguments returns true/false but not SQL Server. Are you connecting to SQL Server or an .mdb/.accdb database? If former, your query is valid. If latter, your error will emerge. — Parfait
– Parfait, Commented Mar 10, 2016 at 3:26
@Parfait: I am connecting to SQL Server, so I believe my query should work. In short, I would like my code to be validated as per SQL Server syntax. — Ryan Poth
– Ryan Poth, Commented Mar 10, 2016 at 3:37

Parfait · Accepted Answer · 2016-03-10 04:58:11Z

2

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 Connection

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

ADO Connection

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

answered Mar 10, 2016 at 4:58

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ryan Poth Over a year ago

Parfait, thanks for the two options. As I'm using DAO I went for the first one, and it worked perfectly. I had read several forum posts that you needed to use a QueryDef in order to process a pass through query, which is clearly incorrect, but the primary reason for me scratching my head over this for a week or so. Thanks again Parfait.

Parfait Over a year ago

Glad to help. I too learned something as I only strictly use DAO in MS Access and never did in Excel until now! Good question.

Collectives™ on Stack Overflow

Cannot use pass through queries in Excel

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related