Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to access SQL Server from VBA in a non-deprecated way?

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)?

like image 505
Heinzi Avatar asked Oct 14 '14 09:10

Heinzi


People also ask

Can Visual Basic connect to SQL Server?

SQL Server and Visual Basic . NET must be installed and running on the same computer. In addition, the user must be able to use Windows Integrated Security to connect to SQL Server.

What is Adodb connection in VBA?

The ADO Connection Object is used to create an open connection to a data source. Through this connection, you can access and manipulate a database. If you want to access a database multiple times, you should establish a connection using the Connection object.

How do I open SQL Server in Visual Studio?

In Visual Studio Code, press Ctrl+Shift+P (or F1) to open the Command Palette. Select MS SQL:Connect and choose Enter.


1 Answers

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.

like image 109
Gord Thompson Avatar answered Oct 23 '22 23:10

Gord Thompson