Click here to Skip to main content
Click here to Skip to main content

Query Assistant

, 29 Jul 2014
Rate this:
Please Sign up or sign in to vote.
Query Assistant - connect to databases with ADO and execute queries.

qa.v1.61.zip

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 - Contain icon data.
  • .\Documents\Qa_Help.docx - Contains an extensive help file.

The code is a complete application which can executed from Visual Basic or compiled to be executed as a stand alone 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 article.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

peterswinkels

Netherlands Netherlands
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 29 Jul 2014
Article Copyright 2014 by peterswinkels
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid