Using DAO to connect to SQL server in Excel

[CedarTree]

Hello - I have a working model to grab data into Access using DAO.recordset from a database hosted on SQL server. Connection string works great. I even have a generic function to grab the recordset as long as I pass the sql statement to the function (see below please). How do I do the same thing (not ADO) in Excel? Thanks!

Function fnServerRecordset(pSQL As String) As DAO.Recordset

    Dim qdf As DAO.QueryDef
   
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = gsConnectionString ' = global connection string that works well
    qdf.ReturnsRecords = True
    qdf.sql = pSQL
    Set fnServerRecordset = qdf.OpenRecordset
    qdf.Close
    Set qdf = Nothing
   
End Function

 

[CedarTree]

Hi-- any suggestions here please? BTW, I'm able to connect from Excel to an Access DB that has the sql server tables linked using DAO. But I'm trying to skip the middle-man of Access. THANKS!

 

[Minty]

I don't think Excel has a querydef object in the same way as Access does. I couldn't see it in the excel object browser.
So I think this method is a no go.

You might be able to get something similar with power query?

 

[GPGeorge]

Hi-- any suggestions here please? BTW, I'm able to connect from Excel to an Access DB that has the sql server tables linked using DAO. But I'm trying to skip the middle-man of Access. THANKS!

I tried using Excel by adding the appropriate DAO and Access references to the VBA in Excel. It seemed like it might be a long slog to get it to work even then. I think alternatives like Power Query or ADO would make more sense. If it takes twice as much work to get something done, then there needs to be twice the pay-off to justify it. This is probably a case where making DAO work isn't going to return twice the benefit.

 

[Isaac]

For straight excel-to-SQL server, I'm currently doing that at work, and strongly suggest using ADO instead of DAO. Even if the only reason I gave was that there's more precedent for that in the online community and more documentation, that'd be enough. Further, it actually works..

 

[CedarTree]

Okay -- off to ADO I go... Thanks!