Query Assistant - Connect to Databases with ADO and Execute Queries

EDIT: I posted a message concerning this article below.

• Download source - 81.9 KB

Introduction

The Query Assistant is a full application designed to make connecting to a database, executing queries, and exporting any results easier. The program does this by shielding the user from the query's code and providing a clean and consistent interface regardless of the database itself. If for example, the user previously had to access a database by making a connection through a terminal emulator, navigating through various menus, manually edit some large query, and then after executing it, copy paste the raw results into a spreadsheet/e-mail, this can likely be made easier by using the Query Assistant.

Although the user cannot view or edit a query's code from inside the program's interface, it is possible to allow controlled input. This is done by inserting so called "(query) parameter definitions" into a query's code where user input is required. These are displayed as input boxes in the program's interface. The length and allowed characters are restricted by an input mask inside the parameter definition. The Query Assistant can export a query's results to a text file or Microsoft Excel spreadsheet and also attach these to an e-mail using Microsoft Outlook. Database connections and export options only have to be configured once. After that, simply start the program, specify any input, execute a query, and export any results if required.

A screenshot of the program with an open query, Microsoft Excel and Outlook with the query's results:

Using the Code

The code is written in Microsoft Visual Basic 6.0 and consists of the following files:

• Qa.vbp - The project file. Qa.bas - Contains QaModule with the majority of the program's procedures
• Logon.frm - Contains the logon interface which is displayed when a user name and/or password are required
• Interface.frm - Contains the program's main interface
• Interface.frx and Logon.frx - Contains icon data
• .\Documents\Qa_Help.docx - Contains an extensive help file

The code is a complete application which can be executed from Visual Basic or compiled to be executed as a standalone application. The code works with Microsoft Windows XP, Vista, and 7 as long as Microsoft ADO is installed. If Microsoft Outlook is installed, the e-mail feature can also be used. Also, without Microsoft Excel, the export function is limited to plain text files.

Points of Interest

Because some versions of the "Common Dialog" control can cause errors when used as an ActiveX object in a program, I prefer to use the "GetOpenFileNameA" and "GetSaveFileNameA" Microsoft Windows API functions instead.

All calls to API functions are wrapped inside a call to the QaModule.CheckForAPIError procedure which uses the "Err.LastDLLError" property to determine whether an error occurred during an API call. It also returns any return values returned by the API function to the calling procedure.

Although I could enumerate all running processes to determine whether Outlook was already running, I decided that simply using GetObject inside its own error trap was simpler. The Boolean variable "OutlookAlreadyActive" is used to determine whether Outlook should be closed automatically by the Query Assistant. Because some versions of Outlook throw an error which appears to be impossible to avoid when using the "Outlook.Application.Quit" method, it (and call to "Outlook.GetNameSpace().Logoff) is preceded by an "On Error Resume Next" statement and directly followed by an "On Error GoTo 0" statement to enable the default error trap for the procedure in question again.

   On Error GoTo OutlookNotAlreadyActive
   OutlookAlreadyActive = True
   Set MSOutlook = GetObject(, "Outlook.Application")
   On Error GoTo 0

Rem Some code not relevant to this example.

EndRoutine:
   If Not (Settings().QueryAutoClose Or OutlookAlreadyActive) Then
      If Not MSOutlook Is Nothing Then
         On Error Resume Next
         MSOutlook.GetNamespace("MAPI").Logoff
         MSOutlook.Quit
         On Error GoTo 0
      End If
   End If

Rem Cleanup code not relevant to this example.

OutlookNotAlreadyActive:
      OutlookAlreadyActive = False
      Set MSOutlook = CreateObject("Outlook.Application")
      MSOutlook.GetNamespace("MAPI").Logon
   Resume Next

Because there was no way I knew of to determine whether or not a specific database supported multiple record sets and ADO would simply throw an error if a database did not, I had to use an error trap to specifically handle this one issue:

On Error GoTo DoesNotSupportMultipleRecordSets
   Set CommandResult = CommandResult.NextRecordSet
On Error GoTo 0

Rem Some code not relevant to this example.

EndRoutine:
Rem Cleanup code not relevant to this example.

DoesNotSupportMultipleRecordSets:
   Resume EndRoutine

History

• 7/25/2014 - First version of this tip