¿Cómo conectar Excel a base de datos Access con VBA
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 «Test Database.accdb». Se guarda en la ubicación «C: \ Users \ Manish Singh \ Desktop». 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 cambios según sus requisitos. He explicado cada línea del código a continuación:
Sub ADO_Connection()
'Creating objects of Connection and Recordset
Dim conn As New Connection, rec As New Recordset
Dim DBPATH, PRVD, connString, query As String
'Declaring fully qualified name of database. Change it with your database's location and name.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb"
'This is the connection provider. Remember this for your interview.
PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
conn.Open connString
'the query I want to run on the database.
query = "SELECT * from customerT;"
'running the query on the open connection. It will get all the data in the rec object.
rec.Open query, conn
'clearing the content of the cells
Cells.ClearContents
'getting data from the recordset if any and printing it in column A of excel sheet.
If (rec.RecordCount <> 0) Then
Do While Not rec.EOF
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
rec.Fields(1).Value
rec.MoveNext
Loop
End If
'closing the connections
rec.Close
conn.Close
End Sub
Copie el código anterior o descargue el archivo a continuación y realice cambios en el archivo para satisfacer sus necesidades.
`link: /wp-content-uploads-2020-02-VBA-Database-Learning.xls [__ Descargar archivo: VBA Database Learning]
Cuando 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 a VBA?
Dim conn As New Connection, rec As New Recordset
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 y necesario argumento 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:
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])
Como no tengo ningún ID ni contraseña en mi base de datos, solo uso ConnectionString. El formato de ConnectionString es «Proveedor = proveedor que desea usar; _ Fuente de datos = nombre completamente calificado de la base de datos«. Creamos y guardamos esta cadena inconnString 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 conjunto de registros objectrec. 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) Then
Do While Not 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 // celdas-rangos-filas-y-columnas-en vba / 3-best-way-to-find-last-non-blank-row-and-column-using-vba.html [ú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 sobre 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 rec y la 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:
`link: / import-and-export-in-vba-use-a-closed-workbook-as-a-database-dao-using-vba-in-microsoft-excel [Use a closed workbook as a database (DAO) usando VBA en Microsoft Excel] `| Para usar un libro de trabajo cerrado como una base de datos con conexión DAO, use este fragmento de VBA en Excel.
`link: / import-and-export-in-vba-use-a-closed-workbook-as-a-database-ado-using-vba-in-microsoft-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.
link: / applications-word-outlook-in-vba-Getting-started-with-excel-vba-userforms [Comenzando con Excel VBA UserForms] | * Para insertar datos en la base de datos, usamos formularios. Los formularios de usuario de Excel son útiles para obtener información del usuario. Así es como debe comenzar con los formularios de usuario de VBA.
===
`link: / user-forms-input-boxes-in-vba-change-the-value-content-of-multiple-user-form-controls-using-vba-in-microsoft-excel [Cambiar el valor / contenido de varios UserForm-controles usando VBA en Excel] `| * Para cambiar el contenido de los controles de formulario de usuario, use este sencillo fragmento de VBA.
`link: / user-forms-input-boxes-in-vba-prevent-a-userform-from-close-when-the-user-click-the-x-button-using-vba-in-microsoft-excel [ Impedir 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.
Artículos populares:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-incrementa-su-productividad [50 accesos directos de Excel para aumentar su productividad] | Acelera tu tarea. Estos 50 atajos le permitirán trabajar aún más rápido en Excel.
link: / fórmulas-y-funciones-introducción-de-vlookup-function [La función VLOOKUP 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.
enlace: / tips-countif-in-microsoft-excel [COUNTIF 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.
link: / excel-formula-and-function-excel-sumif-function [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.