sql-server - usar - que significa compatibilidad de base de datos

¿Cómo acceder a SQL Server desde VBA de una forma no obsoleta?

Question

Parece que todas las formas de acceder directamente a una base de datos de SQL Server desde un proyecto de VBA han quedado en desuso:

• DAO con ODBCDirect: la compatibilidad se ha eliminado con Access 2007.
• DAO vía JET: No habla en serio, ¿verdad? De todos modos, Microsoft lo considera obsoleto .
• ADO con el proveedor SQLOLEDB: Obsoleto .
• ADO con el proveedor OLEDB nativo de SQL Server: no será compatible después de SQL Sever 2012 .
• ADO con el proveedor Microsoft OLE DB para ODBC: No compatible : "SQL Server Native Client no es compatible con el proveedor Microsoft OLE DB para ODBC (MSDASQL)".

¿Qué me perdí? ¿Cuál es la forma oficial, aprobada por Microsoft, de acceder a una base de datos de SQL Server desde VBA (que, después de todo, no está desaprobada y sigue siendo el lenguaje de desarrollo oficial incluido con Office 2013)?

Answer 1

¿Qué me perdí?

Normal viejo ODBC. En los proyectos de VBA para aplicaciones de Office que no sean Access, ODBC a través de ADO es el más sencillo:

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

Para los proyectos de VBA en Access, también tenemos la opción de utilizar tablas vinculadas ODBC y consultas de paso a través de ACE DAO, como siempre lo hemos hecho

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

Notas:

1. SQL Server Native Client 11.0 es la versión que se envía con SQL Server 2014 (ref: aquí ).

2. La lista citada de Tecnologías obsoletas de acceso a datos dice que "DAO 3.6 es la versión final de esta tecnología. No estará disponible en el sistema operativo Windows de 64 bits". Eso se refiere a Jet DAO ("Microsoft DAO 3.6 Object Library"). ACE DAO ("Biblioteca de objetos del motor de base de datos de Microsoft Office Access 14.0") está realmente disponible para las aplicaciones de 64 bits si está instalada la versión de 64 bits del Motor de base de datos de Access.

Answer 2

Al inicializar un adodb.connection en vba reemplazamos

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

con

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

Eso usa .Provider = "MSDASQL.1" pero no tiene que agregar eso.

Answer 3

La forma correcta y futura es usar el modelo de objetos ACE. Estás 100% correcto de que oleDB nativo se está eliminando del servidor SQL. También es muy importante tener en cuenta que la comunidad de desarrolladores "generales" comenzó a eliminar ADO cuando salió .net (el proveedor de ado.net es una bestia MUY diferente y no depende de oleDB, sino del proveedor de sql).

Debido a esto, se están produciendo tendencias importantes en nuestra industria.

Nos alejamos de oleDB. Esta es, en general, una tecnología solo de Windows. Con el aumento de iPads, teléfonos inteligentes, Android, etc., entonces no tienes proveedores específicos de plataforma y no tienen oleDB. Por lo tanto, debe volver a TOWARDS utilizando los estándares de Conectividad abierta de bases de datos (ODBC). Oracle, Microsoft, MySQL han declarado que este es el futuro camino y elección.

Si bien JET se considera obsoleto, ACE no lo es.

DESDE Access 2007 (que ahora es completamente 3 versiones), NO DEBE y NO DEBE tener una referencia a DAO. Por lo tanto, para las últimas 3 versiones de Access no necesita ni desea ni utiliza una referencia a la biblioteca de objetos DAO.

Ahora debería usar el nuevo motor de base de datos ACE integrado. Esto significa que NO necesita una referencia por separado a DAO.

Hay varias ventajas para el motor ACE:

Ya no necesitas una referencia DAO.

Una vez que la referencia al motor de datos se ocupa de las dos referencias bibliográficas anteriores.

Hay una edición tanto de x32 como de x64 disponible (así que las aplicaciones .net, etc. pueden usar una edición de bit x64 de este motor de datos). JET solo era de 32 bits.

El proveedor de ACE continúa recibiendo actualizaciones y mejoras. No se puede decir lo mismo de JET o, de hecho, de ADO tampoco.

ACE ahora es compatible con procedimientos de tienda y desencadenadores de tabla. También tiene soporte para listas de SharePoint basadas en servicios web.

También se realizaron cambios en Access / ACE para trabajar con SQL Azure.

Para usar Access con SQL server, simplemente usa ACE y tablas vinculadas. Como se señaló, la tendencia a alejarse de ADO MUCH comenzó hace unos 13 años cuando .net entró en escena.

Por lo tanto, el enfoque estándar y la recomendación ahora es ACE + odbc.

Entonces no te perdiste nada aquí. Los troncos confusos provienen en gran parte del artículo que declara que JET está depreciado, pero ENTONCES omite el detalle MUY importante de que Access para LAS ÚLTIMAS 3 versiones ahora NO usa JET, pero usa una nueva biblioteca llamada ACE.

Es SIGNIFICATIVO que ya no necesite ni desee una referencia a DAO en sus aplicaciones de acceso.

Ciertamente está usando una biblioteca DAO compatible, e incluso recomienda que prefija el código de su servidor con DAO (de modo que el código existente anterior funcionará bien si lo hizo en el pasado, o siempre dejó fuera el calificador DAO al declarar conjuntos de registros.

Y para cosas como el paso sql, puede usar una consulta de paso guardado y hacer esto:

   CurrentDb.QueryDefs("MyPass").Execute

o qué tal algunos t-sql, puedes hacer esto:

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

o llame un procedimiento de tienda de su elección "sobre la marcha" con un parámetro

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

¿No es lo anterior tan agradable y limpio? Como se señaló, los ejemplos de código anteriores tienden a ser MENOS código y molestia, y luego se utilizan ejemplos de oleDB / ADO publicados.

Para los usuarios de Access de larga data que desarrollaron sus habilidades en torno a ODBC y al servidor sql, no tiene que hacer nada, ya que la industria decidió que lo que ha estado haciendo siempre es el enfoque recomendado.

Si bien JET-DIRECT no es compatible con ACE, no puedo pensar en ningún caso cuando se omite esta opción utilizando los ejemplos querydef pass-though como se indicó anteriormente en lugar de JET direct.