Beyond Excel: VBA and Database Manipulation

Sep 18 2009   3:53PM GMT

MS Query – Example Use



Posted by: Craig Hatmaker
Tags:
database
development
excel
Microsoft Excel
ms query
odbc
sql
vba

So far I’ve talked about how to use MS Query.  It’s easy.  It’s useful.  It’s free.  It’s included with every version of XL.  It works with just about any database provided you have the (usually free) ODBC driver.  Though we won’t be using MS Query in any of the spreadsheets we could deliver to end users – it is still very useful in the hands of the right end users.  Here’s an example.

Last week, our B2B (Business to Business) specialist was alerted to a problem.  A payment from an important customer was delayed because an EDI 214 (a B2B document that conveys shipment status) failed to reach them.  Our B2B team is very good at what they do so this was a big surprise, and though this was the first occurrence, our B2B specialist wasn’t about to let this incident go without a thorough investigation.  Her first concern was damage control.  She needed to know if there were other transactions like this.  She’s not a developer, but she’s not afraid of technology.  So using MS Query she connected to our IBM Server’s DB2 database, joined a few files, and quickly determined there was indeed only one transaction that failed to generate the proper 214.

She’s happy because she’s certain the problem is isolated.  I’m happy because instead of two people, our B2B Specialist and a developer getting tied up, she was able to handle this quickly on her own using a simple, free tool included in her copy of XL.

Now some DBA’s might be shocked at the idea of letting anyone have SQL access to the database.  After all, they could run a query that would bring the machine to its knees, right?  Possibly.  But in the last 10 years I haven’t seen that happen.  Maybe we’ve just been lucky.  Maybe our machines are oversized.  Maybe our operating systems are smart enough to isolate CPU hogs.  On rare occasions, I’ve seen long running, full table scan queries that frustrated the user who submitted them, but I’ve not seen those queries throttle the CPU and choke out other users.  If that ever does happen, we know we can quickly kill the rogue session and restore CPU cycles to the masses.  In the mean time, we will enjoy productivity improvements and end user job satisfaction increases – at no cost to the company at all.

In my opinion, the risk/benefit ratio for this approach is solidly in the favorable column.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: