Mostramos paso a paso cómo añadir un módulo, macro y formulario en una base de datos Microsoft Access, que permita actualizar (modificar o cambiar) el DSN (cadena de conexión ODBC o DNS) de las tablas vinculadas. Para ello usaremos VBA (Visual Basic for Application). Además, mostraremos un método para actualizar todas las tablas vinculadas de una base de datos Access. Usaremos Access 97, auunque el proceso es similar en Access 2000, Access 2003 e incluso en las nuevas versiones de Access que siguen manteniendo VBA.
- Base de datos Microsoft Access con tablas vinculadas, qué es una tabla vinculada en Access.
- Crear formulario en Access para modificar la vinculación DSN automática de tablas.
- Crear módulo VBA para actualizar el DSN de las tablas vinculadas de una base de datos Access.
- Crear macro para ejecutar código VBA para actualizar el DSN de las tablas vinculadas de una base de datos Access.
- Aplicar la acción correspondiente al botón de comando Readjuntar Tablas en el formulario.
- Probar formulario para readjuntar (modificar vínculo DSN ODBC) tablas en Access.
- Comprobar cadena de conexión DSN ODBC de las tablas vinculadas en Access.
- Descarga gratuita de la base de datos Access en formato Access 97 con el formulario, la macro y el módulo VBA.
Base de datos Microsoft Access con tablas vinculadas, qué es una tabla vinculada en Access
La base de datos Microsoft Access (en este tutorial usaremos la versión 97, pero es perfectamente válido para el resto de versiones 2000, 2003, 2008, etc.) permite utilizar tablas de la propia base de datos y tablas de motores de base de datos externos. En una base de datos Microsoft Access se puede trabajar con tablas de otras bases de datos usando ODBC, a estas tablas externas se les llama tablas vinculadas.
A continuación mostramos un tutorial donde explicamos paso a paso cómo crear un origen de datos ODBC y cómo vincular una tabla de MySQL en una base de datos Access:
Lo que explicaremos en este tutorial es cómo cambiar el DSN de una tabla ya vinculada en Access. Este procedimiento es muy útil cuando tenemos varias tablas vinculadas en una base de datos Access y necesitamos cambiar usuario y contraseña o IP o nombre de servidor del motor de base de datos de las tablas vinculadas. Este procedimiento, una vez cambiado el ODBC en el equipo, nos permitirá actualizar el DSN correspondiente en todas las tablas vinculadas de forma automática.
Un ejemplo de base de datos Access con tablas vinculadas, en el ejemplo, las tablas CLIENTES y FACTURAS corresponden a una base de datos Oracle 11g, han sido vinculadas usando el ODBC correspondiente (como indicamos en el tutorial anterior):
Crear formulario en Access para modificar la vinculación DSN automática de tablas
Abriremos nuestra base de datos Microsoft Access, en la pestaña «Tablas» tendremos las tablas tanto de la propia base de datos como las tablas vinculadas. En nuestra base de datos de ejemplo tenemos dos tablas vinculadas de un origen de datos ODBC correspondientes a un servidor de base de datos Oracle: CLIENTES y FACTURAS:
A continuación explicamos paso a paso cómo añadir la utilidad de actualizar el vínculo DSN de todas las tablas vinculadas de esta base de datos de forma automática. Para ello pulsaremos en la pestaña «Formularios» y pulsaremos en «Nuevo»:
Seleccionaremos «Vista Diseño» y pulsaremos «Aceptar»:
Añadiremos los siguientes componentes al formulario:
- Un Cuadro de texto para introducir el DSN, para ello pulsaremos en el botón de «Herramientas» y añadiremos el «Cuadro de texto»:
Estableceremos la propiedad «Nombre» del cuadro de texto a «txtDSN». Para ello pulsaremos con el botón derecho del ratón sobre el Cuadro de texto y seleccionaremos «Propiedades»:
En»Nombre» escribiremos «txtDSN»:
Nota: es muy importante establecer nombres exactos que posteriormente recordemos a los componentes que indicamos que han de añadirse al formulario. Estos nombres serán usados más adelante en el código VBA por lo que deben coincidir.
- Un Cuadro de lista que usaremos para mostrar el progreso de las tablas que se van vinculando desde el código VBA, en la propiedad «Nombre» introduciremos «lsLista»:
- Añadiremos otro Cuadro de texto al final en el que mostraremos la tabla a la que se le está actualizando el vínculo DSN, lo llamaremos «txtResultado»:
- Añadiremos un par de Botones de comando, uno para ejecutar el proceso y otro para cerrar el formulario. Más adelante asignaremos la acción correspondiente al botón «Readjuntar tablas» (será la ejecución de una Macro). Al botón de cerrar el formulario podremos asignarle la acción de forma automática con el asistente, añadiendo un «Botón de comando» desde la barra de herramientas nos mostrará un asistente, en «Categoría» elegiremos «Operaciones con formularios» y en «Acciones» elegiremos «Cerrar formulario». Así el asistente añadirá el código VBA oportuno para cerrar el formulario:
El código VBA del botón Cerrar formulario:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Private Sub Comando11_Click() On Error GoTo Err_Comando11_Click DoCmd.Close Exit_Comando11_Click: Exit Sub Err_Comando11_Click: MsgBox Err.Description Resume Exit_Comando11_Click End Sub |
Una vez añadidos todos los componentes al formulario lo cerraremos y pulsaremos «Sí» en el cuadro de diálogo de aviso:
Introduciremos un nombre para el formulario, por ejemplo «formReadjuntarTablas» y pulsaremos «Aceptar»:
Más adelante, como ya hemos dicho, volveremos a la edición del formulario para establecer la acción que realizará el botón «Readjuntar tablas». Ahora mismo no podemos pues aún no hemos creado la macro para asociarla a dicha acción.
Podemos descargar de forma gratuita la base de datos Access de ejemplo de este tutorial:
Crear módulo VBA para actualizar el DSN de las tablas vinculadas de una base de datos Access
Tras crear el formulario y sus componentes y asignarles un nombre (necesario para el código VBA que generaremos ahora), crearemos un nuevo Módulo con código VBA, en este módulo crearemos las funciones necesarias para obtener el número de tablas vinculadas (excluyendo otro tipo de tablas) de nuestra base de datos Access, modificar el vínculo DSN ODBC de cada tabla y mostrar el resultado en el formulario.
Para crear un nuevo módulo en Access, pulsaremos en la pestaña «Módulos» y en el botón «Nuevo»:
Pegaremos el siguiente código VBA en el nuevo módulo:
El código fuente completo del módulo VBA para actualizar el vínculo DSN de todas la tablas vinculadas de una base de datos Access:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 |
Option Compare Database Option Explicit 'Recorremos todas las tablas de la base de datos 'para modificar el DSN (ODBC) Public Function cambiarDSNTodasTablas() As String Dim baseDatos As Database Dim tabla As TableDef Dim tablaNombre As String Dim tablaAtributos As String Dim i As Integer Dim resultado As String Dim resultadoAcumulado As String Dim numeroTablas As Integer Dim cadenaConexionDSN As String Dim formProgreso As Form On Error GoTo cError Set formProgreso = Forms("formActualizarTablasVinculadas") cadenaConexionDSN = formProgreso.txtDSN If cadenaConexionDSN = "" Then MsgBox "Debe especificar la cadena de conexión DSN, con el formato: " & _ vbCrLf & vbCrLf & "ODBC;DSN=XXX;UID=XXX;PWD=XXX;DBQ=XXX" DoCmd.OpenForm ("formReadjuntarTablas") Else resultado = "" resultadoAcumulado = "" Set baseDatos = CurrentDb() numeroTablas = baseDatos.TableDefs.Count If MsgBox("Se van a readjuntar [" & CStr(obtenerNumeroTablasVinculadas) & _ "] tablas ¿desea continuar?", vbYesNo + vbQuestion, "Readjuntar tablas") = vbYes Then 'Recorremos todas las tablas de la base de datos Access For i = 0 To numeroTablas - 1 Set tabla = baseDatos.TableDefs(i) tablaNombre = tabla.Name tablaAtributos = (tabla.Attributes And Not dbSystemObject And dbAttachedODBC) 'Si no es de sistema y es vinculada ODBC actualizamos el DSN If tablaAtributos = True Then 'Intentamos cambiar el origen de datos ODBC DSN de la tabla resultado = cambiarDSNTabla(tablaNombre, cadenaConexionDSN) 'Mostramos el resultado del cambio de DSN de cada tabla 'en el ListBox (Cuadro de lista) del formulario If resultadoAcumulado = "" Then formProgreso.lsLista.RowSource = resultado resultadoAcumulado = resultado Else formProgreso.lsLista.RowSource = _ formProgreso.lsLista.RowSource & ";" & resultado resultadoAcumulado = resultadoAcumulado & vbCrLf & resultado formProgreso.lsLista.Requery formProgreso.Repaint End If formProgreso.txtResultado = resultado 'formProgreso.txtResultado.Text = resultado 'formProgreso.txtResultado.Requery End If Next i formProgreso.txtResultado = "Proceso finalizado" MsgBox "Proceso finalizado. Puede consultar el resultado de la vinculación " & _ "de cada tabla en la lista.", vbOKOnly + vbInformation End If cambiarDSNTodasTablas = resultadoAcumulado 'MsgBox "Proceso finalizado: " & vbCrLf & vbCrLf & resultadoAcumulado End If cSalir: Exit Function cError: MsgBox "Error al cambiar DSN de tablas vinculadas en Access: " & _ Err.Description, vbOKOnly + vbExclamation End Function 'Modifica el DSN (cadena de conexión ODBC) de una 'tabla vinculada de una base de datos Access Public Function cambiarDSNTabla(nombreTabla As String, _ cadenaConexionDSN As String) As String Dim baseDatos As Database Dim tabla As TableDef On Error GoTo cError Set baseDatos = Application.CurrentDb Set tabla = baseDatos.TableDefs(nombreTabla) tabla.Connect = cadenaConexionDSN tabla.RefreshLink cambiarDSNTabla = "Actualizado DSN tabla -> [" & nombreTabla + "]" Salir: Exit Function cError: cambiarDSNTabla = "ERROR actualizar DSN tabla -> [" & _ nombreTabla & "] Error: " & Err.Description End Function 'Devuelve el número de tablas ODBC vinculadas 'en una base de datos Access, empleando el atributo dbAttachedODBC 'y excluyendo las de sistema con dbSystemObject 'Los posibles atributos: ' dbAttachExclusive Tabla vinculada para uso exclusivo ' dbAttachSavePWD Tabla vinculada, ID y contraseña se guardará en información de conexión ' dbSystemObject Tabla de sistema (protegida) ' dbHiddenObject Tabla oculta ' dbAttachedTable Tabla vinculada no ODBC (Microsoft Access o Paradox) ' dbAttachedODBC Tabla vinculada por ODBC (SQL Server, MySQL, Oracle) Public Function obtenerNumeroTablasVinculadas() As Integer Dim baseDatos As Database Dim tabla As TableDef Dim tablaNombre As String Dim tablaAtributos As String Dim i As Integer Dim numTablas As Integer On Error GoTo cError Set baseDatos = CurrentDb() For i = 0 To baseDatos.TableDefs.Count - 1 Set tabla = baseDatos.TableDefs(i) tablaAtributos = (tabla.Attributes And _ Not dbSystemObject And dbAttachedODBC) If tablaAtributos = True Then numTablas = numTablas + 1 End If Next i obtenerNumeroTablasVinculadas = numTablas cSalir: Exit Function cError: MsgBox "Error al obtener número de tablas vinculadas: " & _ Err.Description, vbOKOnly + vbExclamation End Function |
Una vez pegado el código cerraremos el módulo:
Guardaremos los cambios:
E introduciremos un nombre para el módulo, por ejemplo «utilidades»:
Podemos descargar de forma gratuita la base de datos Access de ejemplo de este tutorial:
Crear macro para ejecutar código VBA para actualizar el DSN de las tablas vinculadas de una base de datos Access
Tras crear el formulario y el módulo VBA añadiremos ahora una macro que nos permitirá ejecutar la función cambiarDSNTodasTablas() del código del módulo VBA anterior. Dicha función será la que realice todo el proceso de actualización del vínculo de todas las tablas de nuestra base de datos Access.
Para crear una nueva macro pulsaremos en la pestaña «Macro» y en el botón «Nuevo»:
En «Acción» elegiremos EjcutarCódigo y en «Nombre de función» introduciremos cambiarDSNTodasTablas()
Cerraremos la nueva macro y guardaremos los cambios:
Introduciremos un nombre para la macro, por ejemplo ReadjuntarTablas:
Podemos descargar de forma gratuita la base de datos Access de ejemplo de este tutorial:
Aplicar la acción correspondiente al botón de comando Readjuntar Tablas en el formulario
Una vez creado el formulario, añadido el módulo VBA y la macro correspondiente volveremos a la edición de nuestro formulario, desde la pestaña «Formularios», seleccionando el formulario y pulsando en «Diseño». Desde el diseño del formulario pulsaremos con el botón derecho del ratón sobre el botón de comando «Readjuntar tablas» y en el menú emergente seleccionaremos «Propiedades»:
En la propiedad/evento «Al hacer clic» introduciremos el nombre de la macro creada anteriormente «ReadjuntarTablas»:
De esta forma el botón ejecutará la macro y la macro, a su vez, ejecutará la función cambiarDSNTodasTablas() del módulo VBA.
Podemos descargar de forma gratuita la base de datos Access de ejemplo de este tutorial:
Probar formulario para readjuntar (modificar vínculo DSN ODBC) tablas en Access
Desde la pestaña «Formularios» de nuestra base de datos Access, seleccionaremos el formulario creado y pulsaremos en Abrir:
Introduciremos en DSN la cadena de conexión a ODBC con el formato, usuario y contraseña correctos, por ejemplo:
ODBC;DSN=FACTURACION;UID=usufacturas;PWD=contraseña;DBQ=BDFACTURAS
donde:
- DSN=FACTURACION: será el nombre del origen de datos ODBC.
- UID=usufacturas: será el usuario de acceso a la base de datos ODBC.
- PWD=contraseña: será la contraseña del usuario anterior.
- DBQ=BDFACTURAS: será el nombre de la base de datos ODBC.
Una vez introducido el DSN correcto pulsaremos en «Readjuntar tablas»:
Desde el código VBA recorre todas la tablas de la base de datos Access y cuenta las que son tablas vinculadas, luego muestra un mensaje al usuario indicando el número de tablas vinculadas y si desea continuar con el proceso de actualización del DSN ODBC. Para continuar pulsamos en «Sí»:
El código VBA se ejecutará e irá actualizando el DSN ODBC de cada tabla vinculada de nuestra base de datos Access y mostrando en el formulario el resultado de cada vinculación. Una vez finalizado el proceso nos mostrará un mensaje indicándolo:
Como vemos el código fuente VBA puede ser muy útil para automatizar tareas que de lo contrario tendríamos que llevar a cabo manualmente.
Podemos descargar de forma gratuita la base de datos Access de ejemplo de este tutorial:
Comprobar cadena de conexión DSN ODBC de las tablas vinculadas en Access
Microsoft Access nos permite consultar los objetos del sistema y sus valores. Entre estos objetos se encuentran las tablas vinculadas y el string de conexión DSN correspondiente. Para consultarlo deberemos mostrar las tablas ocultas y de sistema, para ello, en nuestra base de datos Access, accederemos al menú «Herramientas» – «Opciones»:
En la pestaña «Ver» marcaremos «Objetos ocultos» y «Objetos de sistema», pulsaremos «Aceptar»:
En la pestaña «Tablas» de nuestra base de datos Access nos aparecerán objetos ocultos, entre ellos MSysObjects, lo seleccionaremos y pulsaremos «Abrir»:
En esta tabla de Access protegida (no se puede modificar directamente) podremos comprobar y consultar las tablas vinculadas actualizadas anteriormente y sus cadenas de conexión DSN en el campo Connect:
Descarga gratuita de la base de datos Access en formato Access 97 con el formulario, la macro y el módulo VBA
A continuación mostramos enlace a la descarga gratuita de una base de datos Microsoft Access que incluye el formulario, la macro y el módulo VBA anteriores:
