Patrón de codificación VBA: Crear cadena de conexión ADO DB

Este artículo examina la implementación de funciones para crear y editar cadenas de conexión ADO DB en Excel VBA.

---

1. Concepto y necesidad de ADO

1.1. Conceptos de objetos de datos ActiveX (ADO)

ADO es una biblioteca que puede acceder a varios almacenes de datos y datos CRUD (Crear, Leer, Actualizar, Eliminar).

El sitio de documentos de Microsoft describe ADO de la siguiente manera.

ActiveX Data Objects (ADO) es una interfaz de alto nivel y fácil de usar para OLE DB. OLE DB es una interfaz de bajo nivel y alto rendimiento para una variedad de almacenes de datos. Tanto ADO como OLE DB pueden trabajar con datos relacionales (tabulares) y no relacionales (jerárquicos o de flujo).

ADO proporciona una capa de abstracción entre su cliente o aplicación de nivel medio y las interfaces OLE DB de bajo nivel. ADO usa un pequeño conjunto de objetos de automatización para proporcionar una interfaz simple y eficiente para OLE DB. Esta interfaz convierte a ADO en una buena opción para los desarrolladores de lenguajes de alto nivel, como Visual Basic y VBScript, que desean acceder a los datos sin tener que aprender las complejidades de COM y OLE DB.

fuente: https://docs.microsoft.com/en-us/sql/ado/guide/ado-introduction?view=sql-server-ver15 

1.2. Necesidad de objetos de datos activos (ADO)

Publicación anterior Clase de Excel VBA (1): Resumen de Excel VBA_1.3. ¿Qué se puede hacer con Excel VBA? Entre los contenidos introducidos en , hubo contenidos relacionados con la base de datos.

Entre ellos, se requiere ADO al buscar, guardar, cambiar o eliminar datos relacionados con la base de datos. Hay varias bibliotecas relacionadas con bases de datos (ADO, OLE DB, ODBC, etc.) que se pueden usar en Excel VBA, pero ADO es la de uso más general y conveniente.

2. Crear cadena de conexión ADO DB

2.1. Añadir referencia

Este código necesita agregar referencias a dos bibliotecas.

▼ Adición de referencia de biblioteca de Microsoft ActiveX Data Objects 2.8

“Microsoft ActiveX Data Objects 6.1 Library” es una versión más reciente, pero generalmente se usa 2.8 porque no hay una diferencia significativa.

▼ Adición de referencia de biblioteca de tipos de Microsoft OLE DB Service Component 1.0

Agregue una referencia para usar la clase MSDASC.DataLinks, que le permite crear o editar cadenas de conexión.

2.2. Implementación de la función de creación de cadenas de conexión ADO DB

Publicación anterior Clase de Excel VBA (10): Herramientas que se están desarrollando y utilizando con Excel VBA_5.7. Descargador de datos de ExcelHabía un botón para crear una cadena de conexión de la siguiente manera.

El código de procesamiento del evento de clic del botón "Cadena de conexión" en la pantalla anterior es el siguiente.

Private Sub btnBuildConnectionString_Click()
On Error GoTo 0

    Dim cn As ADODB.Connection, MSDASCObj As MSDASC.DataLinks, oCurrentRange As Range
    Dim eOrgXlEnableCancelKey As XlEnableCancelKey

    Set oCurrentRange = ActiveCell

    Set MSDASCObj = New MSDASC.DataLinks

    eOrgXlEnableCancelKey = Application.EnableCancelKey
    Application.EnableCancelKey = xlDisabled
    Set cn = New ADODB.Connection
    cn.ConnectionString = oCurrentRange
    If MSDASCObj.PromptEdit(cn) = True Then
        oCurrentRange = cn.ConnectionString
    End If

    Set cn = Nothing
    Set MSDASCObj = Nothing

    Application.EnableCancelKey = eOrgXlEnableCancelKey
End Sub

Este código implementa la función de crear o editar la cadena de conexión mediante las clases MSDASC.DataLinks y ADODB.Connection.

• Línea 7: Asigne la celda de ubicación actual (ActiveCell) en la hoja a oCurrentRange. Dado que puede ingresar múltiples cadenas de conexión hacia abajo desde la celda C16, primero seleccione la ubicación actual y luego ejecute la función.
• Líneas 13-14: Cree un objeto ADODB.Connection (cn) y asigne su propiedad ConnectionString al valor oCurrentRange.
• Línea 15: Ejecute pasando el objeto ADODB.Connection (cn) como parámetro a la función PromptEdit del objeto MSDASC.DataLinks. Cuando se ejecuta esta función, la ventana "Propiedades de conexión de datos" aparece de la siguiente manera. (La lista de proveedores puede ser diferente según el entorno del usuario porque se muestra la lista de proveedores instalados. La pestaña "Conexión" a continuación es la pantalla que se muestra cuando se selecciona "Microsoft OLE DB Provider for ODBC Drivers".)

Como referencia, cuando se selecciona "Microsoft OLE DB Provider for Oracle" como proveedor, la pestaña "Conexión" cambia de la siguiente manera.

• Línea 16: si el valor de retorno de la función PromptEdit es True, es decir, si se crea/cambia ConnectionString (cadena de conexión) haciendo clic en el botón "Aceptar" en la ventana "Propiedades de conexión de datos", el valor se registra en oCurrentRange.

La aplicación de este método tiene las siguientes ventajas:

• Posibilidad de error reducida
  • Si crea un DB ConnectionString escribiendo, puede cometer un error tipográfico o especificar un proveedor que no está instalado y provocar un error.
  • DB ConnectionString se puede crear sin errores aplicando este método.
• Reutilización mejorada
  • Si DB ConnectionString se administra como una cadena en el código fuente (codificado), el código fuente debe cambiarse cada vez que desee ejecutar la función en un entorno diferente. De esta forma, sólo los usuarios que sepan editar un determinado código fuente podrán reutilizarlo.
  • Si DB ConnectionString está visible en la interfaz de usuario (hoja de Excel, etc.) en lugar del código fuente, y se proporciona la función para que se pueda editar, cualquiera puede reutilizarla, mejorando la reutilización.
• Mejoras de conveniencia (no es necesario crear ODBC DSN)
  • Cuando se utiliza ODBC, se requiere información de conexión preestablecida y su nombre (DSN, nombre de fuente de datos).
  • La aplicación de este método es conveniente porque no es necesario crear un DSN de ODBC.

La desventaja es que si incluye una contraseña al crear ConnectionString, la contraseña puede quedar expuesta en la interfaz de usuario. Debe tener cuidado si no desea revelar su contraseña.