Combinando Microsoft Access y Oracle

En este artículo vamos a ver cómo implementar una sencilla aplicación que trabaje sobre una base de datos Oracle para actualizar datos y generar reportes, usando una herramienta amigable y disponible en la mayoría de los ordenadores como lo es Microsoft Access.

La idea es, en 5 pasos crear una interfase gráfica con poco esfuerzo y en tiempo récord, que facilite la vida de muchos usuarios no IT, como por ejemplo la del Gerente del Area de Recursos Humanos que necesita ingresar y consultar datos en su base de datos corporativa. No hay necesidad de comprar un caro producto comercial para esa sencilla tarea.

Qué software necesitamos para desarrollar y ejecutar nuestra aplicación?

- Microsoft Access, sino nuestro archivo .mdb no será reconocido por Windows.
- Un cliente Oracle, para que podamos conectarnos con la base de datos de destino.

(Este software debe estar presente ya sea en la máquina que usemos para desarrollar como en la máquina destino donde va a correr el programa. No está en el alcance de este artículo explicar cómo instalar estos dos productos)

Los 5 pasos para crear la aplicación

1) Crear una conexión ODBC
2) Vincular en Access las tablas de Oracle
3) Crear las relaciones existentes
4) Crear formularios
5) Crear un reporte

Nuestra aplicación va a conectarse y trabajar con el usuario Oracle SCOTT. Uso Windows Vista, Access 2003 y un cliente Oracle 10g.

Disculpas por las pantallas de Access en portugués y Windows en inglés, pero es todo lo que tengo en la vuelta :)

Paso 1. Crear una conexión ODBC

- En Windows, vamos al menú Start, Control Panel, Administrative Tools y seleccionamos Data Sources (ODBC)

- En la lengueta User DNS, presionar sobre el botón Add. Una lista de drivers disponibles aparecerá en un diálogo. Tenemos dos posiblidades para seleccionar, una es elegir el driver de Microsoft para Oracle (Microsoft ODBC for Oracle), y la otra es elegir el driver de Oracle que fue instalado con nuestro cliente (en mi caso Oracle em OraClient10g). Recomiendo usar el driver nativo de Oracle, ya que está optimizado y se corresponde exactamente con la versión de Oracle que acabamos de instalar.

Nota: En caso que se nos presente un mensaje de error con el título SQORAS32 y el mensaje An unsupported operation was attemped, se trata de un bug conocido cuya solución está en Metalink nota NR.5699495. Será necesario descargar un patch y reemplazar algunos archivos en el cliente Oracle. Es aplicable a los clientes 10.2.0.3.0 a 10.2.0.3.7 y las instrucciones vienen con el propio patch.

- En el diálogo siguiente, ingresar los datos de la conexión:

Data Source Name: Un nombre cualquiera describiendo nuestra conexión
Description: Opcional, puede ser nada
TNS Service Name: El mismo nombre de servicio en nuestro tnsnames.ora
User ID: Opcional, puede ser nada

- Pulsar Test Connection para ver si nuestra conexión funciona. Aparecerá un diálogo solicitando usuario y password. En mi caso colocaré scott/tiger. El mensaje Connection successfull deberá aparecer para poder continuar. De lo contrario, revisar el tnsnames.ora que está siendo usado, para ver si tiene la entrada correcta.



Luego de seleccionar OK en el diálogo principal, nuestra conexión ODBC está lista para ser usada.

Paso 2. Vincular en Access las tablas de Oracle

- Ingresar a Access y comenzar un nuevo proyecto en blanco.

- En el menú Insertar, elegir Tabla y Vinculación de tabla.

Aparecerá un diálogo como los de seleccionar archivos, pero tenemos primero que elegir en el combo Tipos de archivos, la opción Bases de datos (ODBC).





Luego se muestra el diálogo Select Data Source. Ir a la lengueta Machine Data Source y seleccionar la conexión ODBC creada en el paso anterior.

Somos solicitados para informar el usuario y password de la base de datos Oracle (scott/tiger).





Observar que se nos presentan todas las tablas y vistas a las que el usuario scott tiene acceso. Buscar aquellas que comienzan con el prefijo SCOTT y marcarlas: BONUS, DEPT, EMP y SALGRADE. Seleccionar OK.

- Access solicitará indicar para algunas tablas las columnas identificadoras (clave primaria). Eso dependerá si la clave está definida en la tabla original o no. En este caso vamos a ignorar el aviso simplemente seleccionando OK.

Listo! Las tablas ya están vinculadas en nuestro proyecto. Notar que el nombre de cada tabla asignado automáticamente por Access es [ESQUEMA]_[TABLA].

Paso 3. Crear las relaciones

Cuando se vinculan las tablas del esquema Oracle en Access, no se crean los relacionamientos entre ellas. Es necesario hacerlo manualmente, si queremos mantener el esquema de integridad de datos.

- En el menú Herramientas, ingresar a Relaciones.

- En el diálogo Mostrar Tabla, en la lengueta Tablas, seleccionar (presionando Control) las tablas SCOTT_EMP y SCOTT_DEPT, y pulsar Agregar. Las tablas se mostrarán en la ventana de relacionamientos, y podremos cerrar el diálogo Mostrar Tabla.

- Si la tabla SCOTT_EMP no muestra todas las columnas, agrandarla desde el vértice inferior derecho para permitir ver la columna DEPT.

- Hacer click (izquierdo) sobre la columna DEPT de SCOTT_EMP, y sin soltar, arrastrar la columna hacia la tabla SCOTT_EMP, soltándola encima de la columna DEPTNO (clave primaria de SCOTT_DEPT). Un nuevo diálogo se abrirá con la definición de la relación.



- Podemos elegir las propiedades de esta relación, en nuestro caso vamos a dejar todo por defecto, que corresponde a un INNER JOIN entre ambas tablas. Luego de seleccionar Crear, la relación entre las tablas será visible gráficamente en pantalla.

- Cerrar la ventana de relacionamientos y elegir Sí para guardar los cambios.


Paso 4. Crear formularios

Tenemos las tablas vinculadas en Access con sus relaciones, y de aquí para adelante depende exclusivamente de nuestros conocimientos en Access. Da lo mismo que sean tablas propias de Access o de Oracle con las que estemos trabajando. Podemos inclusive usar este mismo esquema para importar datos desde una tabla Oracle a una local.

Crearemos un formulario para cada una de las tablas importadas, para facilitar el mantenimiento de los datos a través de la aplicación. Explicaré cómo hacer los formularios de DEPT y EMP; para el resto es el mismo procedimiento.

- En la ventana principal de Access, ir a Formularios, y elegir la opción Crear formulario usando el asistente.

- Seleccionar la tabla SCOTT_DEPT en el combo de tablas, y seleccionar todos los campos disponibles (DEPTNO, DNAME, LOC) con el botón >>.

- Presionar Avanzar, elegir el layout 'Columna', el estilo 'Standard', y escribir el título 'Departamentos'. Seleccionar Concluir.

Ya está listo nuestro primer formulario donde daremos mantenimiento a los departamentos. Por defecto, todas las operaciones sobre los datos están permitidas. Si se desea, se pueden alterar las propiedades del formulario activando la caja Propiedades con Alt-Enter.


Ahora vamos al formulario de empleados.

- En la ventana Formularios, elegir la opción Crear formulario usando el asistente.

- Seleccionar la tabla SCOTT_EMP en el combo de tablas, y seleccionar todos los campos disponibles con el botón >>.

- Elegir las mismas opciones que en la tabla anterior, excepto por el título 'Empleados'.

Nuestro formulario de empleados está listo, pero vamos a introducir una pequeña mejora: que el campo DEPTNO en lugar de un texto numérico sea un combo list con los nombres de los departamentos, para evitar tener que recordar los números.

- Seleccionar en el menú Exibir, el modo Diseño.

- Marcar el campo DEPTNO en el formulario y borrarlo (pulsando la tecla DELETE)

- En la caja de herramientas (lateral izquierda) seleccionar la herramienta Caja de Combinación.



- Crear el combo en el lugar que eliminamos el campo numérico DEPTNO. Se abrirá automáticamente el asistente para caja de combinación.

- Seleccionar la primera opción para que el combo busque los datos a partir de una consulta y Avanzar.

- Elegir la tabla SCOTT_DEPT y Avanzar.

- Seleccionar los campos DEPTNO y DNAME y Avanzar

- Definir el orden de ordenamiento por DNAME (creciente) y Avanzar.

- Dejar seleccionado el checkbox Ocultar llave primaria y Avanzar.

- Dejar la opción Recordar valor para uso posterior y Avanzar.

- Si se desea modificar el rótulo, puede alterarse para 'DEPTO'.

- Concluir la operación.

Terminamos nuestro formulario para dar mantenimiento a los empleados.
Ahora nuestro campo de departamentos se actualiza dinámicamente a partir de la tabla SCOTT_DEPT.




Repetir el proceso de creación de formularios para el resto de las tablas, y estaremos aptos para insertar, borrar y alterar registros completamente a través de nuestra interface gráfica.

Paso 5. Crear un reporte

Crearemos un reporte para mostrar un ejemplo más de funcionalidad.

- En la ventana principal de Access, ir a Consultas, y elegir la opción Crear consulta en modo diseño.

- Cerrar la ventana Mostrar tabla y en el menú Exibir seleccionar SQL.

- Escribir la query:

SELECT e.empno, e.ename, e.job, e.sal, d.dname, s.grade
FROM scott_emp e, scott_dept d, scott_salgrade s
WHERE e.deptno=d.deptno
AND e.sal BETWEEN s.losal AND s.hisal

- Cerrar la ventana query y elegir Sí para grabar los cambios, asignando un nombre cualquiera para la consulta, por ejemplo 'Salarios de empleados por departamento'.

- En la ventana principal de Access, ir a Reportes, y elegir la opción Crear reporte usando el asistente.

- En el combo Tablas/Consultas elegir la consulta creada anteriormente, junto con todas las columnas disponibles en la consulta, y avanzar.




- En el nivel de agrupamiento, elegir DNAME (el departamento) y avanzar.





- Elegir el ordenamiento por orden de ENAME creciente.

- Dejar las opciones de diseño por defecto y avanzar hasta visualizar el reporte.

Nuestro reporte está listo luego de 10 minutos con cero esfuerzo de programación, simplemente usando una sencilla consulta SQL.







Combinamos la facilidad de uso y versatilidad de una herramienta como Access con el potencial de una base de datos Oracle. Las posibilidades dependen sólo de nuestra voluntad, pudiendo integrar planillas de Excel, gráficos, multimedia y mejorar la lógica de nuestra aplicacion con VBA (Visual Basic for Applications).