如何以不被废弃的方式从VBA访问SQL Server？ [英] How to access SQL Server from VBA in a non-deprecated way?

问题描述

似乎已经不推荐使用从VBA项目直接访问SQL Server数据库的所有方法：

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

• 带有ODBCDirect的DAO：支持已经被Access 2007删除了。


• DAO通过JET：你不认真，对吧？无论如何，微软的被认为已过时。


• ADO与SQLOLEDB提供程序：已弃用。


• ADO与SQL Server Native OLEDB提供程序：在SQL Sever 2012之后不会被支持。


• ADO与Microsoft的OLE DB提供程序ODBC：不支持：Microsoft OLE DB提供程序不支持SQL Server Native Client（ODBC）（MSDASQL ）

• 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)."

我错过了什么？官方的微软批准的从VBA访问SQL Server数据库的方式（毕竟，不再使用不是，而是Office 2013附带的官方开发语言）？

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

推荐答案

我错过了什么？

What did I miss?

纯旧的ODBC。在Access中的Office应用程序的VBA项目中，通过ADO的ODBC是最简单的：

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

对于Access中的VBA项目，我们还可以选择通过ACE DAO使用ODBC链接表和传递查询，就像我们一直有

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

注意：

1. SQL Server Native Client 11.0是SQL Server 2014附带的版本（参考：此处）。

引用的过时数据访问技术说：DAO 3.6是这项技术的最终版本，64位Windows操作系统将不可用。这是指 Jet DAO（Microsoft DAO 3.6 Object Library）。 ACE DAO（Microsoft Office 14.0 Access数据库引擎对象库）确实可用于64位应用程序，如果安装了64位版本的Access数据库引擎。

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.

这篇关于如何以不被废弃的方式从VBA访问SQL Server？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！