La base de datos de Access es un sistema de administración de bases de datos relacionales que guarda de manera efectiva una gran cantidad de datos de manera organizada. Donde Excel es una herramienta poderosa para convertir datos en información significativa. Sin embargo, Excel no puede almacenar demasiados datos. Pero cuando usamos Excel y Access juntos, el poder de estas herramientas aumenta exponencialmente. Entonces, aprendamos cómo conectar la base de datos de Access como fuente de datos a Excel a través de VBA.
Conexión de la base de datos de Access como fuente de datos Excel
1: Agregar referencia al objeto de datos AcitveX
Usaremos ADO para conectarnos y acceder a la base de datos. Entonces, primero debemos agregar la referencia al objeto ADO.
Agregue un módulo a su proyecto VBA y haga clic en las herramientas. Aquí haga clic en las referencias.
Ahora busque la biblioteca de objetos de datos Microsoft ActiveX. Comprueba la última versión que tienes. Tengo 6.1. Haga clic en el botón Aceptar y listo. Ahora estamos listos para crear un enlace a la base de datos de Access.
2. Escriba un código VBA para establecer una conexión a la base de datos de Access
Para conectar Excel a una base de datos de Access, necesita tener una base de datos de Access. El nombre de mi base de datos es "Prueba Database.accdb ". Se guarda en "C: \ Usuarios \ Manish Singh \ Desktop" localización. Estas dos variables son importantes. Deberá cambiarlos según sus necesidades. El código de descanso se puede mantener como está.
Copie el código a continuación para crear su módulo Excel VBA y realice los cambios según sus requisitos. He explicado cada línea del código a continuación:
Sub ADO_Connection () 'Creación de objetos de conexión y juego de registros Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String 'Declarar el nombre completo de la base de datos. Cámbielo con la ubicación y el nombre de su base de datos. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" 'Este es el proveedor de conexión. Recuerde esto para su entrevista. PRVD = "Microsoft.ace.OLEDB.12.0;" 'Esta es la cadena de conexión que necesitará al abrir la conexión. connString = "Proveedor =" & PRVD & "Fuente de datos =" & DBPATH 'abriendo la conexión conn.Open connString 'la consulta que quiero ejecutar en la base de datos. query = "SELECT * from customerT;" 'ejecutando la consulta en la conexión abierta. Obtendrá todos los datos en el rec objeto. rec.Open query, conn 'limpiar el contenido de las células Cells.ClearContents 'obteniendo datos del conjunto de registros, si los hubiera, e imprimiéndolos en la columna A de la hoja de Excel. If (rec.RecordCount 0) Entonces hazlo mientras no rec.EOF Range ("A" & Cells (Rows.Count, 1) .End (xlUp) .Row) .Offset (1, 0) .Value2 = _ rec.Fields (1) .Value rec.MoveNext Loop End If 'cerrando las conexiones rec.Close conn.Close End Sub
Copie el código anterior o descargue el archivo a continuación y realice los cambios en el archivo para que se adapte a sus necesidades.
Descargar archivo: VBA Database LearningCuando ejecute este código VBA, Excel establecerá una conexión a la base de datos. Posteriormente, ejecutará la consulta diseñada. Borrará cualquier contenido antiguo de la hoja y llenará la columna A con los valores del Campo 1 (segundo campo) de la base de datos.
¿Cómo funciona esta conexión de base de datos de acceso VBA?
Dim conn como nueva conexión, rec como nuevo conjunto de registros
En la línea anterior, no solo declaramos las variables Connection y recordset, sino que las inicializamos directamente usando la palabra clave New.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Estas dos líneas son concursantes. El DBPATH cambiará solo con su base de datos. PRVD está conectando el proveedor OLE DB.
conn.Open connString
Esta línea abre la conexión a la base de datos. Abrir es la función del objeto de conexión que toma varios argumentos. El primer argumento necesario es ConnectingString. Esta cadena contiene el proveedor OLE DB (aquí PRVD) y la fuente de datos (aquí DBPATH). También puede tomar admin y contraseña como argumentos opcionales para bases de datos protegidas.
La sintaxis de Connection.Open es:
conexión.open[ConnectionString como cadena], [UserID como cadena], [Contraseña como cadena], [Opciones como Long = -1])
Como no tengo ningún ID ni contraseña en mi base de datos, solo uso ConnectionString. El formato de ConnectionString es "Provider =proveedor_que desea utilizar; Fuente de datos =nombre completo de la base de datos". Creamos y guardamos esta cadena enconnString variable.
query = "SELECT * from customerT;"
Esta es la consulta que quiero ejecutar en la base de datos. Puede tener las consultas que desee.
rec.Open query, conn
Esta declaración ejecuta la consulta definida en la conexión definida. Aquí estamos usando el método Open del objeto recordset. Toda la salida se guarda en el objeto de conjunto de registrosrec. Puede recuperar, manipular o eliminar valores del objeto de conjunto de registros.
Cells.ClearContents
Esta línea borra el contenido de la hoja. En otras palabras, elimina todo de las celdas de la hoja.
If (rec.RecordCount 0) Entonces hazlo mientras no rec.EOF Range ("A" & Cells (Rows.Count, 1) .End (xlUp) .Row) .Offset (1, 0) .Value2 = _ rec.Fields (1) .Value rec.MoveNext Loop End If
El conjunto de líneas anterior comprueba si el conjunto de registros está vacío o no. Si el conjunto de registros no está vacío (significa que la consulta devolvió algunos registros), el ciclo comienza y comienza a imprimir cada valor del campo 1 (segundo campo, primer nombre en este caso) en la última celda no utilizada en la columna.
(Esto se usa solo para explicar. Es posible que no tenga estas líneas. Si solo desea abrir una conexión a la base de datos, entonces el código VBA por encima de estas líneas es suficiente).
Hemos utilizado rec.EOF para ejecutar el bucle hasta el final del conjunto de registros. Rec.MoveNext se utiliza para avanzar al siguiente conjunto de registros. rec.Fields (1) se usa para obtener valores del campo 1 (que es el segundo ya que la indexación de su campo comienza desde 0. En mi base de datos, el segundo campo es el Nombre del cliente).
rec.Close conn.Close
Finalmente, cuando todo el trabajo que queríamos de la grabación y conexión está hecho, los cerramos.
Puede tener estas líneas en una subrutina separada si desea abrir y cerrar conexiones específicas por separado.
Así que sí, chicos, así es como se establece una conexión a la base de datos ACCESS usando ADO. También existen otros métodos, pero esta es la forma más fácil de conectarse a una fuente de datos de acceso a través de VBA. Lo he explicado con el mayor detalle posible. Hágame saber si esto fue útil en la sección de comentarios a continuación.
Artículos relacionados:
Use un libro de trabajo cerrado como base de datos (DAO) usando VBA en Microsoft Excel | Para usar un libro cerrado como base de datos con conexión DAO, use este fragmento de VBA en Excel.
Use un libro cerrado como base de datos (ADO) usando VBA en Microsoft Excel | Para usar un libro cerrado como base de datos con conexión ADO, use este fragmento de VBA en Excel.
Introducción a Excel VBA UserForms | Para insertar datos en la base de datos, utilizamos formularios. Los UserForms de Excel son útiles para obtener información del usuario. Así es como debe comenzar con los formularios de usuario de VBA.
Cambie el valor / contenido de varios controles de UserForm usando VBA en Excel | Para cambiar el contenido de los controles del formulario de usuario, utilice este sencillo fragmento de VBA.
Evite que un formulario de usuario se cierre cuando el usuario hace clic en el botón x utilizando VBA en Excel | Para evitar que el formulario de usuario se cierre cuando el usuario hace clic en el botón x del formulario, usamos el evento UserForm_QueryClose.
Articulos populares:
50 accesos directos de Excel para aumentar su productividad | Acelera tu tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
La función BUSCARV en Excel | Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
CONTAR.SI en Excel 2016 | Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar un valor específico. La función Countif es esencial para preparar su tablero.
Cómo usar la función SUMIF en Excel | Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.