Как получить доступ к SQL Server из VBA без устаревших способов?

Ответ 1

Что я пропустил?

Обычный старый ODBC. В проектах VBA для приложений Office, отличных от Access, ODBC через ADO является наиболее простым:

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

Для проектов VBA в Access у нас также есть возможность использовать связанные таблицы 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 Native Client 11.0 - это версия, которая поставляется с SQL Server 2014 (ссылка: здесь).

• Приведенный список Устаревших технологий доступа к данным говорит: "DAO 3.6 является окончательной версией этой технологии. Он не будет доступен на 64-битной Windows.". Это относится к Jet DAO ( "Библиотека объектов Microsoft DAO 3.6" ). ACE DAO ( "Библиотека объектов баз данных Microsoft Office 14.0 Access" ) действительно доступна для 64-разрядных приложений, если установлена ​​64-разрядная версия компонента Database Database Engine.

Ответ 2

Правильный и будущий способ - использовать объектную модель ACE. Вы на 100% правильны, что родной oleDB удаляется с SQL-сервера. Также очень важно отметить, что "общее" сообщество разработчиков начало сбросить ADO, когда вышел .net(поставщик ado.net - ОЧЕНЬ другой зверь и один не зависит от oleDB, а от sqlprovider).

Поэтому из-за этого в нашей отрасли происходят значительные тенденции.

Мы отходим от oleDB. Это вообще технология только окон. С ростом iPads, смартфонов, Android и т.д., Тогда у вас нет таких специфичных для платформы поставщиков, и у них нет oleDB. Таким образом, вы должны вернуться назад TOWARDS, используя стандарты Open Database Connectivity (ODBC). Oracle, Microsoft, MySQL заявили, что это будущий путь и выбор.

В то время как JET считается устаревшим, ACE не является.

SINCE Access 2007 (теперь это полностью 3 версии), вы НЕ И НЕ ДОЛЖНЫ иметь ссылку на DAO. Таким образом, для последних 3 версий Access вам не нужна и не нужна ссылка на библиотеку объектов DAO.

Теперь вы должны использовать новый встроенный механизм базы данных ACE. Это означает, что вам НЕ нужна отдельная ссылка на DAO.

Для двигателя ACE есть несколько преимуществ:

Вам больше не нужна ссылка DAO.

Как только ссылка на движок данных позаботится о предыдущих двух ссылках на библиотеку.

Существует доступная версия для x32 и x64 (поэтому приложения .net и т.д. могут использовать x64-разрядную версию этого механизма данных). JET был только x32.

Поставщик ACE продолжает получать обновления и улучшения. То же самое нельзя сказать о JET или на самом деле много для ADO.

ACE теперь поддерживает процедуры хранения и триггеры таблиц. Он также поддерживает списки SharePoint, которые основаны на веб-сервисах.

Также были внесены изменения в Access/ACE для работы с SQL Azure.

Для использования Access с SQL-сервером вы просто используете ACE и связанные таблицы. Как отмечалось, тренд от ADO MUCH начался около 13 лет назад, когда .net появился на сцене.

Таким образом, стандартным подходом и рекомендацией является ACE + odbc.

Итак, вы ничего здесь не пропустили. Сбивание с толку связано со статьей, в которой состояние JET обесценивается, но THEN не учитывает ОЧЕНЬ важную деталь, что Access для THE LAST 3 теперь НЕ использует JET, но использует новую библиотеку под названием ACE.

ЗНАЧИТЕЛЬНО, что вам больше не нужна и не нужна ссылка на DAO в ваших приложениях доступа.

Вы, конечно же, используете совместимую библиотеку DAO, и она по-прежнему даже рекомендует вам прикрепить ваш код reocrdset с помощью DAO (так что старый существующий код будет работать отлично, если, если вы сделали это в прошлом, или вы всегда оставляли DAO квалификатор при объявлении наборов записей.

А для таких вещей, как sql pass, вы просто можете использовать сохраненный проход, хотя запрос, и выполните следующее:

   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

Разве это не так красиво и чисто? Как отмечалось выше, приведенные выше примеры кода, как правило, являются FAR меньше кода и хлопот, а затем, используя опубликованные примеры oleDB/ADO.

Долгое время пользователи Access, которые развивали свои навыки в ODBC и sql-сервере, вам не нужно ничего делать, поскольку отрасль решила, что вы делаете все это время, это рекомендуемый подход.

В то время как JET-DIRECT не поддерживается в ACE, я не могу придумать ни одного случая, когда этот выбор пропущен, используя примеры pass-but querydef, как указано выше, вместо JET direct.

Ответ 3

При инициализации adodb.connection в vba мы заменили

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

с

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

Это использует .Provider = "MSDASQL.1", но вам не нужно это добавлять.