Utilizamos cookies propias y de terceros. Al navegar entendemos que aceptas el uso de cookies. +Info.
Política de cookies
Proyecto AjpdSoft

· Inicio
· Buscar
· Contactar
· Cookies
· Descargas
· Foros
· Historia
· Nosotros
· Temas
· Top 10
· Trucos
· Tutoriales
· Usuario
· Wiki

Nick


Contraseña


Nuevo usuario


English

Access: Consultas e informes desde Microsoft Access a Excel
Bases de Datos


Mostramos en este artículo 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)

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:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Escenario de trabajo, fichero Excel

 

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":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

Indicaremos el nombre de la base de datos y la carpeta donde la guardaremos, por ejemplo "AjpdSoft Cursos":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

En la pestaña "Tablas" pulsaremos el botón "Nuevo":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

Puesto que queremos añadir una viculación a una tabla externa seleccionaremos "Vincular tabla" y pulsaremos "Aceptar":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

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":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

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:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

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":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

Indicaremos el nombre que tendrá la tabla vinculada en Access, en nuestro caso "Cursos". Pulsaremos en "Terminar":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

Si todo es correcto nos mostrará un mensaje como el siguiente, indicando que la vinculación se ha realizado correctamente:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

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:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

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:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

Si intentamos abrir el fichero de Excel con el propio Excel nos mostrará un aviso como este:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 97

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...":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2003

Indicaremos la carpeta y el nombre de la base de datos Access 2003, en nuestro caso "AjpdSoft Cursos" y pulsaremos "Crear":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2003

En "Objetos" - "Tablas", pulsaremos con el botón derecho y seleccionaremos "Vincular tablas...":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2003

En "Tipo de archivo" seleccionaremos "Microsoft Excel", buscaremos el fichero de Excel que queramos vincular y lo seleccionaremos:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2003

En nuestro caso marcaremos "Primera fila contiene títuls de columnas":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2003

Indicaremos el nombre que tendrá la tabla de Access:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2003

Si la vinculación es correcta mostrará este mensaje:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2003

Haciendo doble clic sobre la tabla "Cursos" vinculada o seleccionándola y pulsando en "Abrir":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2003

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:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2003

 

Creación base de datos y vinculación con Excel en Access 2007

Abriremos Access 2007, pulsaremos en "Base de datos en blanco":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

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":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

Pulsaremos en el grupo "Datos externos" y seleccionaremos "Excel":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

Pulsaremos en "Examinar" para seleccionar el fichero de Excel que queremos vincular a Access 2007:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

Seleccionaremos el fichero de Excel a vincular:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

Marcaremos "Vincular al origen de datos creando una tabla vinculada" y pulsaremos "Aceptar":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

Si la el libro de Excel tiene varias hojas, seleccionaremos la que queramos vincular:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

Si la primera fila contiene los títulos de columna marcaremso "Primera fila contiene encabezados de columna":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

Indicaremos el nombre para la tabla de Access vinculada:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

Si todo es correcto mostrará este aviso:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

Desde Access 2007 podremos consultar la tabla vinculada a Excel:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

Podremos ver los datos en tiempo real, pero no podremos modificarlos:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Creación base de datos y vinculación con Excel en Access 2007

 

 

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":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

Seleccionaremos "Asistente para consultas sencillas":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

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:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

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":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

En nuestro caso vemos el diseño de la consulta:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

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*"

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

Ejecutando la consulta (pulsando en "Ejecutar") podremos ver el resultado:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

También podremos ver la consulta SQL real que genera Access, pulsando en "Ver" - "Vista SQL":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

Desde esta vista también podremos modificar la consulta, aunque necesitaremos tener conocimientos de SQL:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

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á:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

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:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

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":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

Ahora crearemos una nueva consulta, como origen de datos seleccionaremos la consulta creada anteriormente "ctCursosAlumno":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

Marcaremos "Resumen" pues queremos agrupar por el campo "Curso":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

Marcaremos "Suma" para que sume las agrupaciones:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

Pulsaremos "Siguiente":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

Indicaremos el nombre de la consulta, por ejemplo "Alumnos por curso":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

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":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

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:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

Una vez realizada la consulta, para realizar el informe, el proceso es bastante sencillo, en primer lugar seleccioanremos la consulta creada "Alumnos por curso", a continuación, en "Crear" - "Informes", pulsaremos en "Informe":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

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:

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

Para ver cómo quedará en la impresión pulsaremos en "Vistas" - "Vista preliminar":

AjpdSoft Consultas e informes desde Microsoft Access a Excel - Generación de consultas e informes en Access 2007

 

Artículos relacionados

Créditos

Artículo realizado íntegramente por Alonsojpd miembro fundador del proyecto AjpdSoft.


Anuncios


Enviado el Jueves, 27 agosto a las 14:01:25 por ajpdsoft
Este sitio web NO CONTIENE malware, todos los programas con código fuente aquí. Autor: Alonso Javier Pérez Díaz Google+ Síguenos en Google+