Click here to Skip to main content
15,907,493 members
Articles / Desktop Programming / Win32
Tip/Trick

Query Assistant - Connect to Databases with ADO and Execute Queries

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
24 Jan 2022CPOL3 min read 17.3K   372   10   3
A Visual Basic 6 program demonstrating how to access databases using Microsoft ADO and how to automate Outlook and Excel.

EDIT: I posted a message concerning this article below.

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.

VB.NET
   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:

VB.NET
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

License

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


Written By
Netherlands Netherlands
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA24-Jan-22 19:10
professionalȘtefan-Mihai MOGA24-Jan-22 19:10 
NewsThe latest version of this project. Pin
peterswinkels24-Jan-22 2:17
peterswinkels24-Jan-22 2:17 
GeneralForgotten and neglected Pin
peterswinkels26-May-16 8:27
peterswinkels26-May-16 8:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.