Cómo conectar una base de datos Microsoft Access con una hoja de cálculo Microsoft Excel directamente (dinámicamente). Realizaremos la vinculación de tablas y crearemos algunas consultas e informes en Access con tablas vinculadas a hojas de Excel. Os explicamos cómo vincular tablas en Access 97, Access 2003 y Access 2007.
- Algunas definiciones iniciales (Access, Excel).
- Escenario de trabajo, fichero Excel.
- Creación de la base de datos Access y vinculación de Excel para generar informes.
- Generación de consultas e informes en Access.
Algunas definiciones iniciales (Access, Excel)
Definición Microsoft Access
Microsoft Access es un programa Sistema Gestor de Base de Datos Relacional (SGBDR del inglés Relational DataBase Management System o RDBMS) creado por Microsoft para uso personal de pequeñas organizaciones. Es un componente de la suite Microsoft Office.
Es un software de gran difusión entre pequeñas empresas (PYMES) cuyas bases de datos no requieren de excesiva potencia, ya que se integra perfectamente con el resto de aplicaciones de Microsoft y permite crear pequeñas aplicaciones con unos pocos conocimientos del Programa. Microsoft Access permite crear formularios para insertar y modificar datos fácilmente. También tiene un entorno gráfico para ver las relaciones entre las diferentes tablas de la base de datos.
Tiene un sistema de seguridad de cifrado bastante primitivo y puede ser la respuesta a proyectos de programación de pequeños y medianos tamaños.
Para bases de datos de gran volumen (de datos o de usuarios) es recomendable usar otros SGBDR como MySQL, Microsoft SQL Server, Oracle Database, IBM DB2, Firebird, PostgreSQL, etc. Pues Microsoft Access no está diseñada para soportar un gran volumen de datos o un gran número de usuarios conectados concurrentemente. Tampoco está diseñada para funcionar a través de Internet.
Entre sus mayores inconvenientes figuran que no es multiplataforma, sólo está disponible para sistemas operativos de Microsoft. Su uso es inadecuado para grandes proyectos de software que requieren tiempos de respuesta críticos.
Microsoft Access es «similar» a otros motores de base de datos SGBDR de escritorio como SQLite, Paradox (de Borland), DBASE, FoxPro
A continuación os mostramos las extensiones típicas de los archivos que componen una base de datos Access:
- .mdb: base de datos de Access (versión 2003 y anteriores).
- .mde: base de datos de Access protegida («compilada») (versión 2003 y anteriores).
- .mdz: extensión de plantillas de Access.
- .accdb: base de datos de Access (versión 2007).
- .accde: base de datos de Access protegida («compilada») (versión 2007).
- .ldb, .laccdb: son usados para determinar qué registros están bloqueados y por quien, en una base de datos
compartida. Es un archivo temporal, se destruye automáticamente cuando no hay usuarios conectados a la base de datos Access. - .accdc: es un archivo comprimido que contiene un archivo de Access (.accdb o .accde) y un certificado digital.
- .mam: macro de Access.
- .maq: consulta de Access.
- .mar: informe de Access.
- .mat: tabla de Access.
- .maf: formulario de Access.
- .adp: proyecto de Access.
- .adn: plantilla de proyecto de Access.
Definición Microsoft Excel
Microsoft Office Excel es una aplicación desarrollada por Microsoft para crear y manejar hojas de cálculo. Es utilizado, normalmente, en tareas financieras y contables.
La última versión de esta hoja de cálculo, la 2007, incluye las siguientes características:
- Permite trabajar con grandes cantidades de datos, admite hojas de cálculo que pueden tener un máximo de 1 millón de filas y 16.000 columnas. Además de una cuadrícula mayor, Office Excel 2007 es compatible con plataformas de multiprocesadores para obtener cálculos más rápidos de hojas de cálculo llenas de fórmulas.
- Permite crear gráficos de aspecto profesional con mayor rapidez y comodidad gracias a las herramientas de generación de gráficos de la interfaz de usuario de Office Fluent. Incluye efectos 3D, sombreado suave y transparencia. El motor de gráficos de Office Excel 2007 es compatible con Microsoft Office Word 2007 y Microsoft Office PowerPoint 2007.
- Permite crear, aplicar formatos, expandir, filtrar y hacer referencias a tablas dentro de fórmulas, ya que Office Excel 2007 ha mejorado la compatibilidad con las tablas.
- El formato XML de Microsoft Office Excel ofrece una reducción significativa del tamaño de los archivos, mientras que su arquitectura mejora la recuperación de los datos de los archivos dañados. Este nuevo formato aporta un ahorro muy importante en los requisitos de almacenamiento y ancho de banda, a la vez que reduce la carga de trabajo del personal de TI.
- Office Excel 2007 incluye funciones de cubo para crear informes personalizados a partir de una base de datos OLAP. La conexión a orígenes de datos también es más sencilla mediante la Biblioteca de conexiones de datos.
A continuación os mostramos las extensiones típicas de los archivos que componen una hoja de cálculo Excel:
- .xls: hoja de cálculo Excel (versión 2003 y anteriores).
- .xlsx: hoja de cálculo Excel (versión 2007).
Escenario de trabajo, fichero Excel
En primer lugar dispondremos de una hoja de cálculo Excel, que será la que contenga los datos principales de la aplicación. En realidad esto no es lo habitual, pero nos ceñiremos al título del artículo. Lo normal es que Access sea la base de datos principal de la aplicación. Pero para este caso supondremos un escenario de trabajo donde la hoja de cálculo Excel es la base de datos principal de la aplicación.
Así pues dispondremos de un fichero .xls de Excel, en nuestro caso con el siguiente contenido:
| APELLIDOS | NOMBRE | CURSO1 | CURSO2 | CURSO3 |
| Lozano Gomariz | Juan Pablo | Introducción a la informática | Desarrollo de proyectos | Desarrollo de aplicaciones |
| Perea Cañales | Fernando Alonso | Desarrollo de aplicaciones | Introducción a la informática | |
| AjpdSoft | AjpdSoft | Desarrollo de proyectos | Desarrollo de aplicaciones | Ofimática |
| Nadal Gutiérrez | Rafael | Introducción a la informática | Introducción a la informática | |
| Cánovas Lozano | Federer | Ofimática | ||
| Peñalver Vicente | Juan | Desarrollo de aplicaciones | Introducción a la informática | Ofimática |
Este fichero Excel irá siendo actualizado por los usuarios, de forma que será la base de datos principal. En nuestro caso, el nombre del fichero es Cursos.xls:
Creación de la base de datos Access y vinculación de Excel para generar informes
Ahora crearemos una base de datos Access (.mdb ó .accdb) y vincularemos Access con Excel de forma dinámica. Explicaremos cómo hacerlo para las versiones 97, 2000 y 2007, aunque el proceso es similar para todas las versiones.
Creación base de datos y vinculación con Excel en Access 97
Para crear la base de datos abriremos Access, desde «Inicio» – «Programas» – «Microsoft Office» – «Microsoft Access», en la primera ventana seleccionaremos «Base de datos en blanco» y pulsaremos «Aceptar»:
Indicaremos el nombre de la base de datos y la carpeta donde la guardaremos, por ejemplo «AjpdSoft Cursos»:
En la pestaña «Tablas» pulsaremos el botón «Nuevo»:
Puesto que queremos añadir una vinculación a una tabla externa seleccionaremos «Vincular tabla» y pulsaremos «Aceptar»:
En «Tipo de archivo» indicaremos «Microsoft Excel», Access permite vincular tablas de otros tipos como DBASE, FoxPro, Paradox, ODBC (con este método podremos vincular tablas MySQL, Microsoft SQL Server, Oracle Database, IBM DB2, Firebird, PostgreSQL, SQLite, etc.). En nuestro caso seleccionaremos «Microsoft Excel» y buscaremos el fichero de Excel a vincular, en nuestro caso «Cursos.xls» y pulsaremos en «Vincular»:
Si tenemos varias hojas en el libro de Excel, el Asistente para vinculación de hojas de cálculo, mostrará una ventana como la siguiente para seleccionar la hoja que vincularemos a Access:
Si la primera fila de la hoja de Excel contiene los títulos de las columnas marcaremos «Primera fila contiene títulos de columnas», pulsaremos «Siguiente»:
Indicaremos el nombre que tendrá la tabla vinculada en Access, en nuestro caso «Cursos». Pulsaremos en «Terminar»:
Si todo es correcto nos mostrará un mensaje como el siguiente, indicando que la vinculación se ha realizado correctamente:
Con el texto: «Finalizada la vinculación de la tabla ‘Cursos’ al archivo …».
A partir de este momento tendremos en Access acceso directo a la hoja de cálculo Excel vinculada, haciendo doble clic sobre «Cursos» o seleccionando esta tabla y pulsando en «Abrir» veremos su contenido:
Dicho contenido es el que hay en el fichero de Excel, de hecho, si realizamos cualquier cambio en esta tabla vinculada se reflejará directamente en el fichero de Excel, de ahí que sea una vinculación dinámica. Hay que tener en cuenta que mientras la tabla esté abierta en Access no será accesible desde Excel:
Si intentamos abrir el fichero de Excel con el propio Excel nos mostrará un aviso como este:
Con el texto: «Imposible obtener acceso a «Cursos.xls».»
Creación base de datos y vinculación con Excel en Access 2003
Para el caso de Access 2003, el proceso es similar, lo abriremos, pulsaremos el botón «Nuevo» y en la parte derecha pulsaremos en «Base de datos en blanco…»:
Indicaremos la carpeta y el nombre de la base de datos Access 2003, en nuestro caso «AjpdSoft Cursos» y pulsaremos «Crear»:
En «Objetos» – «Tablas», pulsaremos con el botón derecho y seleccionaremos «Vincular tablas…»:
En «Tipo de archivo» seleccionaremos «Microsoft Excel», buscaremos el fichero de Excel que queramos vincular y lo seleccionaremos:
En nuestro caso marcaremos «Primera fila contiene títulos de columnas»:
Indicaremos el nombre que tendrá la tabla de Access:
Si la vinculación es correcta mostrará este mensaje:
Haciendo doble clic sobre la tabla «Cursos» vinculada o seleccionándola y pulsando en «Abrir»:
Veremos el contenido de la hoja de cálculo Excel, como hemos dicho para el caso de Access 97, cualquier cambio que se realice en esta tabla será guardado directamente en el fichero de Excel:
Creación base de datos y vinculación con Excel en Access 2007
Abriremos Access 2007, pulsaremos en «Base de datos en blanco»:
En la parte derecha de la ventana indicaremos el «Nombre de archivo» y la carpeta de destino, en nuestro caso «AjpdSoft Cursos.accdb». Pulsaremos en «Crear»:
Pulsaremos en el grupo «Datos externos» y seleccionaremos «Excel»:
Pulsaremos en «Examinar» para seleccionar el fichero de Excel que queremos vincular a Access 2007:
Seleccionaremos el fichero de Excel a vincular:
Marcaremos «Vincular al origen de datos creando una tabla vinculada» y pulsaremos «Aceptar»:
Si la el libro de Excel tiene varias hojas, seleccionaremos la que queramos vincular:
Si la primera fila contiene los títulos de columna marcaremos «Primera fila contiene encabezados de columna»:
Indicaremos el nombre para la tabla de Access vinculada:
Si todo es correcto mostrará este aviso:
Desde Access 2007 podremos consultar la tabla vinculada a Excel:
Podremos ver los datos en tiempo real, pero no podremos modificarlos:
Generación de consultas e informes en Access
Como se ha podido observar en el proceso anterior (creación y vinculación de tablas), la metodología es similar para todas las versiones de Access. A continuación explicaremos cómo realizar consultas e informes en Access para mostrar determinados datos de la tabla de Excel vinculada, que al igual que en el caso anterior, el proceso es similar para todas las versiones de Access.
Generación de consultas e informes en Access 2007
Desde el grupo «Crear», en «Otros» pulsaremos en «Asistente para consultas»:
Seleccionaremos «Asistente para consultas sencillas»:
En la parte izquierda aparecerán los campos disponibles para la consulta, pulsando el botón «>» pasaremos el campo disponible a campo seleccionado (aparecerá en la consulta). En la parte superior indicaremos la tabla o consulta origen de datos para esta consulta, en nuestro caso la tabla «Cursos» creada anteriormente como tabla vinculada de fichero Excel:
Indicaremos el título que tendrá la consulta, si queremos ver la vista de diseño marcaremos «Modificar el diseño de consulta», si queremos ver directamente el resultado de la ejecución de la consulta marcaremos «Abrir la consulta para ver información»:
En nuestro caso vemos el diseño de la consulta:
Desde el diseño podremos modificar la consulta agregando o quitando columnas, agregando filtros (criterios), agregando nuevas tablas, etc. Por ejemplo, para mostrar sólo los alumnos cuyos apellidos lleven la palabra «aj», en «Criterios» de esta columna añadiremos:
Como «*aj*»
Ejecutando la consulta (pulsando en «Ejecutar») podremos ver el resultado:
También podremos ver la consulta SQL real que genera Access, pulsando en «Ver» – «Vista SQL»:
Desde esta vista también podremos modificar la consulta, aunque necesitaremos tener conocimientos de SQL:
La consulta SQL que Access ha generado de forma automática:
SELECT Cursos.[APELLIDOS], Cursos.[NOMBRE],
Cursos.[CURSO1], Cursos.[CURSO2], Cursos.[CURSO3]
FROM Cursos
WHERE (((Cursos.[APELLIDOS]) Like «*aj*»));
Hagamos ahora una consulta un poco más compleja. Por ejemplo, para el caso del fichero de Excel, hagamos una consulta que muestre el número de alumnos que se han matriculado en el CURSO1:
SELECT Cursos.CURSO1, Count(Cursos.CURSO1) AS Alumnos_Curso
FROM Cursos
GROUP BY Cursos.CURSO1;
La vista en modo gráfico de esta consulta será:
Compliquemos un poco más la consulta, hagamos que muestre el número de alumnos que se han matriculado en cada curso, para ello utilizaremos la cláusula «UNION»:
SELECT Cursos.CURSO1, Count(Cursos.CURSO1) AS Alumnos_Curso
FROM Cursos
GROUP BY Cursos.CURSO1
UNION
SELECT Cursos.CURSO2, Count(Cursos.CURSO2) AS Alumnos_Curso
FROM Cursos
GROUP BY Cursos.CURSO2
UNION
SELECT Cursos.CURSO3, Count(Cursos.CURSO3) AS Alumnos_Curso
FROM Cursos
GROUP BY Cursos.CURSO3;
Lógicamente, esta no es la mejor forma (o la más óptima) de guardar los datos de los cursos, lo lógico es que hubiera tres tablas, una para los alumnos, otra para los cursos y una tercera para los cursos a los que se han inscrito los alumnos. Pero puesto que utilizamos Excel como origen de datos, nos ajustamos a su estructura:
Esta consulta SQL no es del todo «correcta», pues repite el nombre de los cursos y no los suma. Para corregirla guardaremos esta consulta con el nombre «ctCursosAlumnos»:
Ahora crearemos una nueva consulta, como origen de datos seleccionaremos la consulta creada anteriormente «ctCursosAlumno»:
Marcaremos «Resumen» pues queremos agrupar por el campo «Curso»:
Marcaremos «Suma» para que sume las agrupaciones:
Pulsaremos «Siguiente»:
Indicaremos el nombre de la consulta, por ejemplo «Alumnos por curso»:
Y el asistente de Access habrá creado la consulta de forma automática. En realidad lo único que ha hecho es agrupar por la columna CURSO (con nombre CURSO1) y en «Total» del campo «Alumnos_Curso» ha añadido «Suma»:
La consulta SQL que ha generado:
SELECT DISTINCTROW ctCursosAlumno.CURSO1,
Sum(ctCursosAlumno.Alumnos_Curso) AS [Suma De Alumnos_Curso]
FROM ctCursosAlumno
GROUP BY ctCursosAlumno.CURSO1;
Y la consulta en ejecución:
Una vez realizada la consulta, para realizar el informe, el proceso es bastante sencillo, en primer lugar seleccionaremos la consulta creada «Alumnos por curso», a continuación, en «Crear» – «Informes», pulsaremos en «Informe»:
El asistente seleccionará automáticamente como origen de datos la consulta seleccionada y generará el informe con los campos de la consulta, podremos ver la vista de diseño desde «Vistas» – «Vista Diseño», para modificar y adaptar el informe a nuestras necesidades:
Para ver cómo quedará en la impresión pulsaremos en «Vistas» – «Vista preliminar»:
