AuditNet®
End User Database Access Using Excel
by Mike Blakely
The Challenge
One of the challenges facing IT auditors is the need to provide database query support for others within Internal Auditing. This support can include helping auditors extract or obtain data from corporate, departmental and local databases. Often, one or both of the following approaches are taken:
1. Conduct training sessions on how to query databases, including
training in SQL and the techniques to use SQL.
2. A specialist obtains the data directly for the auditor.
Although the first approach sounds good, not only is it often resource intensive to develop and deliver the training, but all too often, the material learned in the training session is quickly forgotten, especially if not used on a regular basis.
The second approach is also resource intensive for the specialist, who is often already overloaded. And all too often, the first database query leads to the second, and so forth.
A Possible Solution
This article proposes an alternative which has been proven efficient and effective in certain environments. Often the specialist is well aware of the databases available as well as what are likely the most commonly required queries. One solution is to encapsulate this knowledge in a format that any auditor will feel comfortable with and be able to use and re-use without any help and on their own time frame. Some auditors can even use this as a training opportunity to develop and test their own queries. For this purpose, an Excel workbook, macros, connection strings and example data and SQL has been contributed to the public domain by the author. This material may be downloaded, and is available as a “setup.exe” file to facilitate installation.
How it works, in brief
The specialist (e.g. IT Auditor) takes tested queries and edits the query literals using parameters. These are then saved as a text file with a descriptive name, e.g. “List invoices between two dates and within two amounts.sql”. The specialist also researches and builds the connection parameters that are required by Microsoft ActiveX Data Objects (ADO). (For examples of connection parameters there is a good resource at http://connectionstrings.com.) These files are then stored on a local server accessible by the auditors.
The auditor simply opens their own copy of the workbook provided,
browses to the file containing the connection string, then possibly
enters an id and password if required. They then browse to the file
containing the SQL, double click the file name and type in the
parameters required in a familiar Excel form environment. They then
specify the Excel worksheet where the output result set should be
stored. When they click the button “Run Query”, the database query runs
and the result set is written to the Excel sheet specified. An example
screen shot of a very basic query is shown at the end of the article.
What is Required
There are only three things required: (1) ActiveX Data Objects must be installed on the desktop/laptop (generally already there if using XP or later), (2) Excel must be installed, and (3) the files containing the connection strings and SQL must be constructed. Notice that there must be some in-house technical knowledge of SQL.
What types of databases can be accessed?
Any database accessible under ADO. This includes almost all of the
popular databases such as DB2, MS-Access, MS-SQL Server, Oracle, MySQL,
etc. Note that drivers may need to be installed on the desktop/laptop.
Cost / Restrictions
Cost is the time to establish the SQL, install the drivers and software etc. The Excel code is public domain and can be modified for special local requirements if required. Generally, ADO does not impose many restrictions on use.
Where Can I find Out More?
There is a seven minute training demo available on the subject at http://ezrstats.com/CP/ADO.php (requires broadband access). The data files, Excel workbook and help file can be downloaded as a “setup.exe” file from http://ezrstats.com/DBQuery/EZDBSetup.exe.
About the author
Mike Blakley is an IT auditor for the State of North Carolina Health
and Human Services. He maintains a blog on audit software at
http://blog.ezrstats.com and can
reached via e-mail at
Mike.Blakley@ezrstats.com.
For the article with the examples click here!
Example Excel query form shown from within Excel.
The Query which is being run:
Results of Query (Stored on worksheet named Res)