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

Asked 14/10, 2014 at 9:2 Answered 13/10, 2016 at 14:47

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

Cooperage answered 14/10, 2014 at 9:2 Comment(9)

I have been using ADODB or DAO for years and never encountered any deprecation related errors...Driver={SQL Server}? – Lacefield 14/10, 2014 at 9:4

When I am not using linked tables, I am using DAO all the time and it works perfectly. It is the "native way" in Access, and I never heard it was deprecated (expect in 2007 for some time ?). When using DAO, I don't even see wether it's odbc direct or jet. Who cares anyway ? – Rotl 14/10, 2014 at 9:8

@vba4all: Me neither, but that's not surprising: Deprecation doesn't mean "it doesn't work anymore", deprecation means "Don't use this for new development, it might stop working anytime". We are currently reworking the data access layer of our VBA applications, so I'm curious about the currently recommended way to access SQL Server data. – Cooperage 14/10, 2014 at 9:9

@iDevlop: I care. :-) If you don't see whether it's ODBC direct or JET, you are probably using JET, which means that you have to use JET SQL syntax and cannot use any native T-SQL statements (common table expressions, etc.). – Cooperage 14/10, 2014 at 9:10

@Heinzi: right, BUT then you can easily create passthru queries, either purely in VBA or in saved queries. The performance is equivalent to other clients, tested with quite large tables on Oracle and Sql Server. In that case you're using the server's syntax. – Rotl 14/10, 2014 at 9:31

If there is a driver that does not support the T-SQL or other syntax you want you call stored procedures on the server and that should in theory work ... – Lacefield 14/10, 2014 at 9:33

@iDevlop: True, passthrough queries would be an option, although I'd rather consider this a workaround (creating temporary objects in the front-end database) than a solution. – Cooperage 14/10, 2014 at 9:42

@vba4all: ...and write a lot of boilerplate code to manage and update those stored procedures during updates of my front-end application. I'd rather keep all the code (VBA and SQL) in one place, if possible. – Cooperage 14/10, 2014 at 9:44

not anymore... blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/… – Winifield 11/9, 2018 at 18:2

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:

SQL Server Native Client 11.0 is the version that ships with SQL Server 2014 (ref: here).
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.

Poulos answered 14/10, 2014 at 10:18 Comment(3)

Actually, your first option uses MSDASQL, the OLEDB->ODBC mapper, which is the default ADO provider used if no Provider=... clause is present in the connection string. It might work; but, as stated in the question, this is explicitly "not supported" by Microsoft. +1 for mentioning ACE DAO, though! – Cooperage 14/10, 2014 at 11:22

@Cooperage Point taken re: MSDASQL. In fact, I just checked and both "Microsoft ActiveX Data Objects 2.8 Library" and "Microsoft ActiveX Data Objects 6.0 Library" report 'MSDASQL.1` as their .Provider for a Connection object. It certainly would be ironic if SQL Server Native Client broke MSDASQL and a whole bunch of VBA code for SQL Server had to be migrated from ADO to ACE DAO! (I sure hope that doesn't happen....) – Poulos 14/10, 2014 at 12:30

I would recommend changing Driver={SQL Server Native Client 11.0} to Driver={ODBC Driver 17 for SQL Server} to use Microsoft ODBC Driver for SQL Server based on Driver history for Microsoft SQL Server saying "It is not recommended to use [SQL Server Native Client] for new development." and the MSODBCSQL announcement. – Neurophysiology 15/1, 2019 at 17:9

The correct and future way is to use the ACE object model. You are 100% correct that native oleDB is being dropped from SQL server. It ALSO very important to note that the “general” developer community started dropping ADO when .net came out (the ado.net provider is a VERY different beast and one not reliant on oleDB, but the sqlprovider).

So because of this, significant trends are occurring in our industry.

We are moving away from oleDB. This is in general a Windows-only technology. With the rise of iPads, smartphones, Android etc, then you don’t have such platform specific providers and they don't have oleDB. You thus have to revert back TOWARDS using Open Database Connectivity standards (ODBC). Oracle, Microsoft, MySQL have all stated this is the future road and choice.

While JET is considered obsolete, ACE is not.

SINCE Access 2007 (that is now fully 3 versions), you DO NOT and SHOULD NOT have a reference to DAO. So for the last 3 versions of Access you don’t need nor want or use a reference to the DAO object library.

You now should use the new built in ACE database engine. This means you do NOT need a separate reference to DAO.

There are several advantages to the ACE engine:

You don’t need a DAO reference anymore.

Once reference to the data engine takes care of previous two library references.

There is both an x32 and x64 bit edition available (so .net applications etc. can use an x64 bit edition of this data engine). JET was x32 bit only.

The ACE provider continues to receive updates and enhancements. The same cannot be said for JET or in fact much for ADO either.

ACE now supports store procedures and table triggers. It also has support for SharePoint lists which is web services based.

Also changes were made to Access/ACE to work with SQL Azure.

For using Access with SQL server, you simply use ACE and linked tables. As noted the trend away from ADO MUCH started about 13 years ago when .net came on the scene.

Thus the standard approach and recommend now is ACE + odbc.

So you not missed anything here. The confusion stems much from article that state JET is deprecated but THEN leaves out the VERY important detail that Access for THE LAST 3 versions now does NOT use JET, but uses a new library called ACE.

It is SIGNIFICANT that you don’t need nor want a reference to DAO in your Access applications anymore.

You are certainly using a compatible DAO library, and it still even recommend that you prefix your reocrdset code with DAO (so older existing code will work just fine when if you done this in the past, or you always left out the DAO qualifier when declaring recordsets.

And for things like sql passthrough, you simple can use a saved passthrough query, and do this:

   CurrentDb.QueryDefs("MyPass").Execute

or how about some t-sql, you can do this:

With CurrentDb.QueryDefs("MyPass")
  .SQL = "ALTER TABLE Contacts ADD MiddleName nvarchar(50) NULL"
  .Execute
End If

or call a store procedure of your choice "on the fly" with a parameter

With CurrentDb.QueryDefs("MyPass")
  .SQL = "Exec MyStoreProc " & strMyParm1
  .Execute
End If

Are not the above so nice and clean? As noted the above code examples tend to be FAR less code and hassle then using oleDB/ADO examples posted.

For the long time users of Access that developed their skills around ODBC and sql server, you don’t have to do anything as the industry much decided what you been doing all along is the recommend approach.

While JET-DIRECT is not supported in ACE, I cannot think of any case when this choice is missed by using the passthrough querydef examples as per above in place of JET direct.

Ivonneivor answered 14/10, 2014 at 21:41 Comment(3)

+1, good points raised. I will leave the question open for now, though, just in case someone can come up with a pure-VBA solution (that could be used, for example, from Excel as well as Access without adding an "intermediate ACCDB"). – Cooperage 15/10, 2014 at 7:28

You have a good point that this is "easy" from Access. From Excel, I would still consider using ADO. While oleDB is being dropped from v-next of SQL server, you can still use oleDB with the ODBC provider. So this means in "general" you could still write and use ADO code, but ADO will thus be using the ODBC provider as opposed to the oleDB provider in the future (and thus you don't care too much). So as a "access only" question, this is a easy answer. Your introduction and having Excel in this context does increase your challenge. I would much concede that you can continue to use ADO in Excel. – Ivonneivor 16/10, 2014 at 16:26

But sadly the new ACE engine is still living as the "DAO" library. – Unbeliever 18/1, 2017 at 20:37

When initializing an adodb.connection in vba we replaced

          .Provider = "sqloledb"
          .Properties("Data Source").Value = sServer
          .Properties("Initial Catalog").Value = sDB
          .Properties("Integrated Security").Value = "SSPI"

with

           .ConnectionString = _
               "DRIVER={ODBC Driver 11 for SQL Server}; " & _
               "SERVER=" & sServer & "; " & _
               "Trusted_Connection=Yes; " & _
               "DATABASE=" & sDB & "; "

That uses .Provider = "MSDASQL.1" but you don't have to add that.

Homesteader answered 13/10, 2016 at 14:47 Comment(0)

Recommended topics

Hot tags