Beyond Excel: VBA and Database Manipulation

Mar 23 2010   5:09PM GMT

Updating Databases from Excel – First Looks

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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 item master in SQL Server, iSeries DB2, Oracle, or just about any other relational database.
Update Example

Update Example

Normally, update spreadsheets have three buttons, “New”, “Load”, and “Post”.  I’ve trimmed this example to just two buttons for now.  We’ll add the “New” button later.
When the user first opens the spreadsheet, it is blank except for the buttons.  The user can click “Load” and select which products to change, or they can load all products as I have done in this example.  As followers of this blog might guess, the “Load” button acts just like our reports except for one formatting difference – the yellow cells. 
Yellow cells are the only cells that can be changed.  We use equivalent functionality to Excel’s “Format cell” > “Protection” > “Locked” option, along with the ActiveSheet.Protect Contents:=True to block users from changing anything outside the yellow areas.  We also use these cell qualities to guide keyboard navigation (cursor keys) to the next open cell.
Another change you might have noticed is the “ACD” column.  “ACD” stands for “Add, Change, Delete”.  An “X” indicates that a record already exists in the database.  When the user changes the “Target Lvl” column, the appropriate “ACD” cell changes to “C”, indicating the record changed.  When the user clicks the “Post” button, our spreadsheet finds rows with an “ACD” set to “C” and executes an SQL Update statement for us.  Fortunately, we can use the Fields Table to help take the drudgery out of constructing SQL statements.  Here is what the expanded “Fields Table” looks like.
Fields Table

Fields Table

 You can click the graphic to display a larger version of it.  The next post will explain the new fields.

 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.

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:

Share this item with your network: