Beyond Excel: VBA and Database Manipulation

Sep 17 2009   6:44AM GMT

Data Sources



Posted by: Craig Hatmaker
Tags:
development
excel
Microsoft Excel
ms query
odbc
sql
vba
In my last post, I shared how you can explore just about any database using MS Query, a useful, friendly, and free tool included with every version of XL.  To use it with your database, you must point it to the appropriate data source. 
Choose Data Source

Choose Data Source

When you start MS Query the first thing it asks for is the “Data Source” name.  A “Data Source” name is required.  At a minimum, a “Data Source” name points MS Query or ODBC to a driver that understands the application’s standards (such as Oracle, DB2, Access, Excel, or MS SQL Server) that your data conforms to.  

If you don’t see your required database in the list, you can create it by selecting <New Data Source> and clicking OK.  That brings up the “Create New Data Source” dialog. 

Create Data Source

Create Data Source

Step 2 is perhaps the most daunting.  Sometimes there are more than one driver listed that support your database.  In that case, it usually doesn’t much matter which one you choose.  Sometimes there are no drivers that support your database.  In that case, you need to load that database’s ODBC driver on your PC. 

If you don’t know where to find your ODBC driver, first contact your DBA or Systems Administrator.  If they don’t have it, search the database provider’s website under product support for driver downloads.  If it’s not there, contact the database provider’s sales or support team.  If all else fails, Google your database name with “ODBC driver” to see if someone else provides it.  I have found a few old, non-relational databases with third party ODBC drivers for sale; but, the dominant database providers don’t charge.  Microsoft includes theirs and several once prominent PC databases with their operating systems.  Oracle’s is freely available from their website.  IBM’s comes with their PC client or server CDs.  Several powerful free databases such as mySQL give the driver away.

 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: