Beyond Excel: VBA and Database Manipulation:

ms query


May 6, 2010  8:34 PM

Building a Library of Routines for Updating – #4



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

We are just about finished with adding support routines for updating, inserting, and deleting records in a database.  As a reminder, these routines should never need modification.  The first routine should go in modSQL.  It is used to create a connection object that can be reused.  This...

April 28, 2010  4:15 PM

Building a Library of Routines for Updating – #3



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

[caption id="attachment_663" align="alignright" width="91" caption="You are here (click to enlarge)"]
Bookmark and Share     0 Comments     RSS Feed     Email a friend


April 20, 2010  1:16 PM

Building a Library of Routines for Updating – #2



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

[caption id="attachment_685" align="alignright" width="91" caption="You are here (Click to enlarge)"]

Bookmark and Share     0 Comments     RSS Feed     Email a friend


April 15, 2010  8:10 AM

Building a Library of Routines for Updating – #1



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

[caption id="attachment_682" align="alignright" width="91" caption="You are here (Click to enlarge)"]

Bookmark and Share     0 Comments     RSS Feed     Email a friend


April 5, 2010  11:24 AM

Additions to the Fields Table for Update Spreadsheets



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

 As promised in the last post, here are descriptions of each column in the fields table. [caption id="attachment_583" align="alignnone" width="587" caption="Fields Table (click to enlarge)"]

Bookmark and Share     0 Comments     RSS Feed     Email a friend


March 23, 2010  5:09 PM

Updating Databases from Excel – First Looks



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

Here is an example of an update spreadsheet.  We will use it in our tutorial. It updates the target stocking level ("Target Lvl") for Products in the Northwind database.  It could just as easily, with different configuration, update an...


March 17, 2010  8:19 PM

Updating Databases from Excel – The Basics



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

Last post I encouraged readers to approach updating databases with care and professionalism. Some may wonder why then, use Excel at all if updates require the level of care in traditional programming. The answer is that Excel is a standard means for exchanging data. As you know, the world is...


March 11, 2010  8:43 PM

Updating Databases from Excel



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

It seems almost every web portal that lists data includes an export to Excel, CSV, or XML, or combinations thereof.  What we are going to start exploring will enable you to capture that data, massage it, save it, and merge it with existing systems - using nothing more than Excel and...


March 4, 2010  6:32 PM

Drill Down – Part 2



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

Before we get to coding, let's look at the final product.  When we click our 'easy' button, our spreadsheet prompts us for dates, customers, and products.  If we just take the defaults, everything will be loaded from the NorthWinds database - like this: [caption id="attachment_544"...


February 24, 2010  6:40 PM

Drill Down – Part 1



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

Last post I showed how we can present summarized data when what our users want is supported by detail that would fill up tens of thousands of rows.  When we summarized the data, in a sense, we duplicated the functionality of a simple "pivot table."  Pivot Tables have one feature we didn't...


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: