Sql-server – How to access SQL Server from VBA in a non-deprecated way

excelms-accesssql servervba

It appears that all ways to directly access an SQL Server database from a VBA project have been deprecated:

  • DAO with ODBCDirect: Support has been dropped with Access 2007.
  • DAO via JET: You're not serious, right? Anyway, it's considered obsolete by Microsoft.
  • ADO with the SQLOLEDB provider: Deprecated.
  • ADO with the SQL Server Native OLEDB provider: Won't be supported after SQL Sever 2012.
  • ADO with the Microsoft OLE DB provider for ODBC: Not supported: "SQL Server Native Client is not supported from the Microsoft OLE DB provider for ODBC (MSDASQL)."

What did I miss? What is the official, Microsoft-approved way to access an SQL Server database from VBA (which is, after all, not deprecated and still the official development language included with Office 2013)?

Best Answer

What did I miss?

Plain old ODBC. In VBA projects for Office applications other than Access, ODBC via ADO is the most straightforward:

Sub AdoOdbcExample()
    Dim con As Object
    Set con = CreateObject("ADODB.Connection")
    con.Open _
            "Driver={SQL Server Native Client 11.0};" & _
            "Server=.\SQLEXPRESS;" & _
            "Database=myDb;" & _
            "Trusted_Connection=yes;"
    con.Execute "UPDATE Clients SET FirstName='Gord' WHERE ID=5;"
    con.Close
    Set con = Nothing
End Sub

For VBA projects in Access, we also have the option to use ODBC linked tables and pass-through queries via ACE DAO like we always have

Sub DaoOdbcExample()
    Dim cdb As DAO.Database, qdf As DAO.QueryDef
    Set cdb = CurrentDb
    Set qdf = cdb.CreateQueryDef("")
    qdf.Connect = "ODBC;" & _
            "Driver={SQL Server Native Client 11.0};" & _
            "Server=.\SQLEXPRESS;" & _
            "Database=myDb;" & _
            "Trusted_Connection=yes;"
    qdf.sql = "UPDATE Clients SET FirstName='Gord' WHERE ID=5;"
    qdf.ReturnsRecords = False
    qdf.Execute dbFailOnError
    Set qdf = Nothing
    Set cdb = Nothing
End Sub

Notes:

  1. SQL Server Native Client 11.0 is the version that ships with SQL Server 2014 (ref: here).

  2. The cited list of Obsolete Data Access Technologies says "DAO 3.6 is the final version of this technology. It will not be available on the 64-bit Windows operating system.". That refers to Jet DAO ("Microsoft DAO 3.6 Object Library"). ACE DAO ("Microsoft Office 14.0 Access database engine Object Library") is indeed available to 64-bit applications if the 64-bit version of the Access Database Engine is installed.

Related Topic