如何以非废弃的方式从VBA访问SQL Server？

Question

如何以非废弃的方式从VBA访问SQL Server？

sql-servervbaexcelms-access

15

看起来直接从VBA项目访问SQL Server数据库的所有方式都已被弃用：

通过ODBCDirect使用DAO：自Access 2007开始不再支持。
通过JET使用DAO：你不是认真的吧？无论如何，这已经被Microsoft视为过时。
通过SQLOLEDB提供程序使用ADO：已弃用。
通过SQL Server本机OLEDB提供程序使用ADO：将在SQL Sever 2012之后不再受支持。
通过Microsoft ODBC提供程序的OLE DB提供程序使用ADO：不支持："SQL Server Native Client在Microsoft OLE DB提供程序(即MSDASQL)中不受支持。"

我错过了什么？有什么官方的、得到Microsoft认可的方法可以从VBA访问SQL Server数据库(毕竟VBA并没有被弃用，而且仍然是Office 2013附带的官方开发语言)？

- Heinzi

2

我使用ADODB或DAO已经多年了，从未遇到任何*弃用(deprecation)*相关的错误... Driver={SQL Server}？ - user2140173

1

当我不使用链接表时，我一直在使用DAO，并且它完美地工作。这是Access中的“本地方式”，我从未听说过它已被弃用（除了2007年有一段时间？）。使用DAO时，我甚至看不到它是odbc direct还是jet。反正谁在乎呢？ - iDevlop

@vba4all：我也是这样，但这并不奇怪：弃用并不意味着“它不再起作用”，而是意味着“不要在新开发中使用它，因为它随时可能停止工作”。我们目前正在重新设计VBA应用程序的数据访问层，因此我很想知道目前推荐的访问SQL Server数据的方法。 - Heinzi

1

@Heinzi：没错，但是你可以轻松地创建透传查询，无论是纯粹在VBA中还是在保存的查询中。性能与其他客户端相当，在Oracle和Sql Server上测试过相当大的表。在这种情况下，您正在使用服务器的语法。 - iDevlop

@vba4all：...并编写大量样板代码来管理和更新这些存储过程，以便在前端应用程序更新期间使用。如果可能的话，我宁愿将所有代码（VBA和SQL）放在一个地方。 - Heinzi

显示剩余4条评论

3个回答

2

正确的方法并且也是未来的方法是使用ACE对象模型。您的想法是正确的，本机OLEDB正在从SQL Server中被删除。值得注意的是，在.NET推出时，“一般”的开发人员社区开始放弃ADO（ado.net提供程序是一个非常不同的实体，不依赖于OLEDB，而是sqlprovider）。

因此，我们行业正在发生重大变化。

我们正在远离OLEDB。总的来说，这是一个仅限Windows的技术。随着iPad、智能手机、Android等的兴起，您就没有这样的特定平台提供商，它们也没有OLEDB。因此，您必须重新使用开放数据库连接性标准（ODBC）。Oracle、Microsoft、MySQL都表示这是未来的发展方向和选择。

虽然JET被认为是过时的，但ACE却不是。

自访问2007以来（现在已经完全3个版本），您不需要也不应该引用DAO。因此，在过去的3个Access版本中，您不需要、不想要或不使用DAO对象库的引用。

您现在应该使用新的内置ACE数据库引擎。这意味着您不需要单独引用DAO。

使用ACE引擎有几个优点：

您不再需要DAO引用了。

一次引用数据引擎即可处理之前的两个库引用。

现在有32位和64位版本可用（因此，.net应用程序等可以使用此数据引擎的x64位版本）。JET只有32位。

ACE提供程序继续接收更新和增强。不能为JET或实际上也没有多少ADO做到这一点。

ACE现在支持存储过程和表触发器。它还支持基于Web服务的SharePoint列表。

Access/ACE也进行了更改，以便与SQL Azure配合使用。

要使用Access与SQL Server，您只需使用ACE和链接表即可。请注意，远离ADO的趋势始于约13年前，当时.NET出现了。

因此，现在的标准方法和建议是ACE + ODBC。

因此，您没有错过任何内容。混乱很大程度上源于文章指出JET已被弃用，但是然后遗漏了一个非常重要的细节，即Access在过去3个版本中不再使用JET，而是使用一个名为ACE的新库。

这意味着您的Access应用程序中不再需要DAO的引用。

您肯定正在使用兼容的DAO库，并且建议您仍然在记录集代码中加入DAO前缀（因此，在过去，如果您完成了此操作，或者在声明记录集时总是省略了DAO限定符，则旧的现有代码也将正常工作）。

对于诸如sql通过等的事情，您只需使用保存的通过查询，然后执行以下操作：

   CurrentDb.QueryDefs("MyPass").Execute

或者您可以使用一些T-SQL，您可以这样做：

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

或者使用参数在现场调用您选择的存储过程

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

以上代码看起来简洁明了，相比于使用发布的oleDB/ADO示例，上述代码示例要少得多，并且更加省事。

对于长期使用Access并围绕ODBC和SQL Server开发技能的用户来说，你不需要做任何事情，因为行业已经决定你一直在使用的方法是推荐的方法。

虽然ACE不支持JET-DIRECT，但我想不出任何情况会错过这种选择，因为使用上面的透传查询定义示例代替JET Direct。

- Albert D. Kallal

+1，提出了很好的观点。我暂时会把问题保持开放，以防有人能够提供一个纯-VBA解决方案（例如从Excel和Access中使用，而不添加“中间ACCDB”）。 - Heinzi

你说得很对，从Access来看这很“容易”。但是从Excel来看，我仍然会考虑使用ADO。虽然oleDB在SQL Server的v-next版本中被弃用了，但你仍然可以使用ODBC提供程序来使用oleDB。因此，“一般情况下”你仍然可以编写和使用ADO代码，但是未来ADO将使用ODBC提供程序而不是oleDB提供程序（因此你不太关心）。所以作为一个“仅限于Access”的问题，这是一个简单的答案。你的介绍和在这种情况下使用Excel增加了你的挑战。我非常同意你可以继续在Excel中使用ADO。 - Albert D. Kallal

但是遗憾的是，新的ACE引擎仍然作为“DAO”库存在。 - Klaus

0

在VBA中初始化adodb.connection时，我们进行了替换

          .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 & "; "

这个使用了.Provider = "MSDASQL.1"，但你不必添加它。

- Hein van der Kallen

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gord Thompson · Accepted Answer

我错过了什么？

普通的ODBC。在除Access外的Office应用程序的VBA项目中，通过ADO使用ODBC是最直接的方法：

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项目，我们仍然可以使用ODBC连接的数据表和通过ACE DAO传递查询方式。

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

注意：

SQL Server 2014附带的版本是SQL Server Native Client 11.0（参考：此处）。
引用的已过时数据访问技术列表中说：“DAO 3.6是这项技术的最终版本。它不会在64位Windows操作系统上提供。”。这是指Jet DAO（“Microsoft DAO 3.6 Object Library”）。如果安装了64位版本的Access数据库引擎，则确实可以将ACE DAO（“Microsoft Office 14.0 Access database engine Object Library”）提供给64位应用程序。