MS Access as an Audit Tool part 2!

An auditor can use MS Access as an audit tool through the following ways:

1. Connecting to another database through an ODBC driver.
2. Converting data extracted by the I.T. unit into an MS Access tables.

In this post, I will explain to the reader how to connect to another data source like Oracle, MySql and others through an ODBC driver.

Knowledge on how to do this is vital if your Audit organization does not have the budget to buy expensive audit tools like ACL or IDEA that would serve as their CAATs software. If you could train your auditors who have an inclination with the workings of database softwares, you could very well create audit tools that are as powerful as ACL or IDEA and easily customizable according to your needs.

ODBC stands for Open Database Connectivity

Open Database Connectivity (ODBC) is Microsoft's strategic interface for accessing data in a heterogeneous environment of relational and non- relational database management systems. Based on the Call Level Interface specification of the SQL Access Group, ODBC provides an open, vendor- neutral way of accessing data stored in a variety of proprietary personal computer, minicomputer, and mainframe databases. ODBC alleviates the need for independent software vendors and corporate developers to learn multiple application programming interfaces. ODBC now provides a universal data access interface. With ODBC, application developers can allow an application to concurrently access, view, and modify data from multiple, diverse databases. - Microsoft Help and Support, Article ID: 110093 - Last Review: March 29, 2007 - Revision: 1.5
ODBC--Open Database Connectivity Overview.

In order to connect to another data source or database, you need an ODBC client and an ODBC driver. The client is the Microsoft Access since it is an ODBC enabled front-end and the one which the user sees on the screen. While your ODBC driver is the one that handles the communication between your client or front end and another database. Microsoft Access has a couple of ODBC drivers already built in its system. However, if MS Access is not pre-loaded with the ODBC driver you require, you may download it from the internet where most ODBC drivers are readily available for download, you may do so and install it in your system.

For example if your back end database is Oracle 10G, download the ODBC driver for the Oracle 10G database from the Oracle website and install the driver in your operating system. Be sure to download the version applicable to your operating system.

--- See next blog post for an example of connecting to an Oracle 10G database from MS Access ---