Query Assistant - connect to databases and execute queries.

[Peter Swinkels]

What is the Query Assistant?
A tool intended to help make connecting to databases, executing queries, and export their results.

What can it do?
1. Connect to database using Microsoft ADO as specified by a connection string in a settings file.
2. If configured as such, request the user for a password and/or user name and fill in the respective place holders in the connection string.
3. Load a query specified as a command line argument or as selected by the user from inside the program's interface.
4. Display input boxes for controlled input as specified by input place holders inside, if present, a query.
5. If configured as such, execute a series of queries in a batch.
6. Export a query's results to a text file or Microsoft Excel spreadsheet, when required.
7. Attach the file with the query's results to an e-mail, fill in the sender, recipient, subject, and body as specified in a settings file and then send it.
8. Insert data such the date, time, an operating system environment variable's value, an input box's value into a query or e-mail.

It also comes with an extensive help file describing:
1. What the program does.
2. The supported settings.
3. The supported command line arguments.
4. The supported file types for exported query results.
5. How to define input place holders in a query.
6. How to insert data into an e-mail or query.
7. How to create a tiny example database, query and, settings file.
8. How to view a description for each control inside the interface.

An image showing the program's main interface, Microsoft Excel and Outlook:
Attachment 117155

Contents for Qa.v1.65.zip:
.\Qa.v1.65\Interface.frm
.\Qa.v1.65\Interface.frx
.\Qa.v1.65\Logon.frm
.\Qa.v1.65\Logon.frx
.\Qa.v1.65\Qa.bas
.\Qa.v1.65\Qa.vbp
.\Qa.v1.65\Documents\Qa_Help.docx

Notes:
-Also included is the original Dutch language version. Its file formats are slightly different for reasons of backwards compatibility and because of language differences.
-Before the program can be used, a settings file, containing at least a connection string under the appropriate header ("[Connection]" or "[Verbinding]" (Dutch)). The program looks for a file called "Qa.ini" by default unless otherwise specified in a command line argument. The setting file's extension must always be ".ini".

-The executables can be downloaded here: https://drive.google.com/open?id=0Bw...zlZa0JJRW05eFk

BTW:
See my latest post for bug fixes and sample files.

[techgnome]

What's in the zip? I can't download it as Chrome things it is malicious. I'm guessing you included the compiled binaries didn't you? Yeah... don't. Just zip up the source and post that. Don't include any compiled bits. DLLs, EXEs, or OCXs.

-tg

[dday9]

@TG -

I've opened up the ZIP using Mozilla and here are the contents:

1. Documents(folder)
   
   1. QA_Help.docx
   2. ToDo.txt
2. Interface.frm
3. Interface.frx
4. Logon.frm
5. Logon.frx
6. QA.bas
7. QA.vbp

[techgnome]

Huh... OK... wonder what Chrome's issue was with it then.

Very well then. Carry on. nothing to see here.

-tg

BTW - @Peter - That actually looks like a handy tool.

[Peter Swinkels]

New versions have been uploaded - fixed:
1. Possible freezing while exporting to Microsoft Excel.
2. Possible error after closing a Microsoft Outlook message composition window.
3. Microsoft Outlook requesting the user to select a profile.
4. Multiple errors after specifying an invalid path to export to.
5. Unnecessary exporting after a failed query.
6. A file that hasn't been overwritten while exporting is still opened during a batch.
7. The status bar indicating that a failed action was performed successfully.
8. A run-time error while reading the settings file causes the remaining settings to be ignored.
9. Generic error messages where information such as the path/settings file section/setting in question could have been provided.
10. Minor glitches involving the status bar (duplicate/missing messages.)
11. Overuse of the "End" statement.
12. Overuse of the "On Error Resume Next" statement.
13. A few typo's.
14. Files remaining open after an error.
15. Qa blocking logging off or being terminated by the Task Manager. ("Do you want to quit"?)
16. I forgot to re-enable the error traps in the Dutch language version. (fixed)
17. No more late-binding. This may mean that you have to change the references for ADO/Office when opening the project containing the program's code.
18. Sending e-mails through Outlook is now done from inside a class and the use of events.
19. Manual has been updated.
20. Qa now takes advantage of the Collections object.
21. POTENTIALLY SERIOUS: Fixed a bug where files could be deleted if the export path contained a wildcard ("*") character.
22. Fixed a bug an export in a batch would contain the results of earlier queries in that batch.
23. Cleaned the code.
24. Added some more checks and warnings.
25. Updated the manuals.
26. English version: translated a few overlooked items.
27. Removed an unnecessary variable declaration.
28. Restructured and cleaned the command line argument processing code.
29. Fixed a minor issue where in a some cases an empty query file would be treated as though there was no file whatsoever.
30. Paths specified as a relative path are now always displayed as a complete path in error messages.
31. Made certain procedure, structure, and variable names less ambiguous.
32. Made constant names more legible by inserting underscores in composite names.
33. Indexed the help files.
34. Fixed a bug causing long file names contained in quotes not be recognized.
35. Query results are now always displayed - regardless of any errors.
36. Cleaner error handling if Microsoft Outlook can't be started/connected to. (Less error messages.)
37. The statusbar now "remembers" previous status information.
38. Fixed a glitch causing certain messages popping up unnecessarily.
39. Added an option to make the program's attempt at retrieving multiple recordsets optional.

[DEXWERX]

I HATE Chrome is (but continue to use it).... marking non-malicious things as malicious. Now it doesn't even give you the option to "keep anyway" anymore, without completely disabling fishing protection first. IDIOTIC
/endrant

[gibra]

Qa.ini is missing.

Therefore, project fails to run.

I suggest to TEST before to public.

[Peter Swinkels]

Post deleted.

[DEXWERX]

Can you put the manual in a better format than docx? like PDF or RTF or text?

It just makes it harder for people to read the manual.

Not trying to make excuses but the way your app just bails out, when there is no INI file, (not even an example one) is pretty blunt/rude.

My 2 cents. Regards.

[Peter Swinkels]

Can you put the manual in a better format than docx? like PDF or RTF or text?

It just makes it harder for people to read the manual.

Not trying to make excuses but the way your app just bails out, when there is no INI file, (not even an example one) is pretty blunt/rude.

My 2 cents. Regards.

REMOVED PARAGRAPH

BTW:
Converting the help document probably is a good idea. But people, if you can't open it for some reason, then let me know.

EDIT:
While I didn't agree with the way some people have communicated (sometimes I can be rather blunt myself) most still had a point, so:
-Help documents are now in *.pdf (Adobe Portable Document Format.)
-The program now offers to generate a default settings file and asks you to provide database connection information, but you still will have to customize the settins file manually if to change the default settings.
-I found some more stray "End" statements, except for the one in the general error handler, I have removed them all now.
-Fine tuned the code a bit more.
-Fixed some typo's.

[gibra]

First of all, I do test before posting. I suggest that you read the manual before writing blunt posts about FREEWARE which means you could be more polite. I don't mind criticism, unless it's thrown in my face like this, especially when I put a huge amount of work into something. I think that implying that I don't test is a bit offensive. Although my program isn't very user friendly about it, what you're complaining about isn't a bug.

EDIT:
I toned my reply down because my initial reaction was perhaps a bit too severe.

EDIT 2:
I reread the manual, although it does address the issue described by Gibra, some clarification wouldn't hurt and I will be fixing some minor glitches which escaped my notice.

I do not understand why all this polemic.

I made a simple warning that the project will not run because it can not find the file QA.INI
There is nothing to read, if a file is missing.
If you post a sample project, this project should at least start and run.
If you do not want constructive criticism, then not to publish anything.

Now I have seen (after my warning) that you have modified the project, and now when you start creating the file if it exists QA.INI. Fine.


No need to argue. Keep calm and patience.

[Peter Swinkels]

Post deleted.

[LaVolpe]

@Peter. Don't take comments personally unless they are calling you an idiot or worse. Most times when people post "this doesn't work" it is because they downloaded, ran, and poof -- no workie. Those comments are generally meant as a head-s up, not an insult. Also remember that how you interpret someone's comments is not always the way it was intended. We should not try to interpret intent. Too many language differences; not all people posting have English as their first language. And with the state of our public school systems, not sure many Ameicans have English as their first language -- ok, poor joke, but a good example of implying intent as that statement would likely irritate many readers thinking I was talking about them. Just an example.

Of course, we probably should download our own zip and run it in a different folder which would catch missing files or highlight poor assumptions we've made. We all have had that 'senior moment' uploading something that excluded a pertinent file, included an oudated file, etc, etc, me included, Lord knows.

In the future, maybe placing in a bolded statement in your initial post, something like caveats: Before you run this, do this, or that first. I don't know if your original post included that, the current one does. People are more likely to see that instead of opening a help file or readmefirst type of file. Just the way it is. People are likely to not spend a lot of time delving into the finer details of a complex app (free ones especially) unless the example(s) work out-of-the-box. If they like it, then they'll spend the time looking over the documentation (whether in the code as comments or external files). But they need the sample project up & running first to determine if investing their time is worth it, to them.

Edited: nit-picking is a form of positive criticism if you read it emotionally detached. At least that is the way I try to view it

Just some words of wisdom from a prolific poster

[Peter Swinkels]

original post deleted.

[LaVolpe]

I suppose, you could argue that the fact that some one bothers to nitpick something at all is positive.

Bingo

[Peter Swinkels]

Okay, the Query Assistant has been heavily modified since it was first uploaded so I'm going to bump this thread and list the most recent changes:

1. Fixed a few translation errors.
2. Cleaned the code.
3. Fixed some inconsistencies regarding the status bar.
4. Fixed some issues involving this program using Microsoft Outlook to generate e-mails.
5. Completely revised the way the program handles directories and files among which a bug involving network paths.

Also, I usually appreciate feedback even though I reacted poorly to some people's comments in this thread. I had invested a lot of time and energy in this program (and yes I always test it before posting, but bugs can slip through.), but that's no excuse for me to react so negatively to a comment. So, if any one does have any feedback, don't hesitate to post it.

BTW:
I attached TestData.zip to this post. It contains a few simple queries and some data which hopefully make it easier for people to understand how this program is intended to be used.

EDIT:
Looking back at this whole incident I realized it was naive of me to try to offer free programs without being able to truly provide support. Assuming my users would be able and willing to examine the source code or just deal with the manual when issues arose was shortsighted in hindsight. I realize now that most people using a software product expect support when they face issues, and that if they don't get that support they will probably move to something else. Even if it means paying a little more.