Beyond Excel: VBA and Database Manipulation

May 26 2010   10:33PM GMT

Validating Data Entries



Posted by: Craig Hatmaker
Tags:
database
development
excel
Microsoft Excel
ms query
odbc
sql
tutorial
vba
Practically no one uses Excel to update databases directly.  I think the reason is because we think of Excel as a tool that end users can do whatever they want to with and updating databases requires discipline.  Just using Excel to shotgun records into a database can be disastrous because putting bad data into a database is often far worse than putting no data in at all.  That’s not to say Excel can’t be an excellent tool for updating databases, it just means we have to approach the subject with the same discipline as we would with ‘normal’ or ‘traditional’ programming methods.
 
At the heart of that discipline is good data validation, also known as ‘edit checks’. 
 
Today’s post won’t share any code.  I want to provide an overview of the process before we start coding.  Here is a simple data flow diagram (DFD) to help the discussion.
Check Entry Overview

Check Entry Overview

As always, I use lots of little functions instead of one monolithic program.  In some cases, my purpose is to build a routine that can be used for other circumstances.  In other cases, my purpose is to isolate where changes should occur from places where, in all likelihood, they never should occur.  And in all cases, my purpose is to have small bits of code that perform specific functions that can be thoroughly tested before being placed into production.  That’s no assurance that something won’t go bump in the night (thus every routine has error handling).  Nor does it mean that experience won’t lead us to better methods and thus, changes to underlying code.
 
The first routine is the worksheet’s specific validation controller.  Though it is expected that this routine will require modifications, much of it should not need any changes (and in many cases, I have used it without any modifications).  The first function it performs is one such piece that should not need modification.  It handles some housekeeping such as making sure all global variables have been initialized (I normally don’t like global variables.  I made an exception for speed here) and clearing any previous error indications from the data to be validated.
 
Below the housekeeping piece is a loop that processes all rows, and all cells in those rows.  Within the inner loop is a place where, if you need unique data validation logic, you can put it.  Below the section set asside for your modifications is a routine that handles all the normal data validation stuff, such as checking dates, making sure numeric fields contain numbers, making sure text fields aren’t longer than they should be, converting to upper case when necessary, handling Yes or No flags, simple Excel Table lookups, and database lookups.
 
Database lookups require specific information and so the generic routine really doesn’t handle them.  It passes control back to the worksheet where a template routine awaits your modifications. 
Note: As you can see, things that need your attention, are isolated in the worksheet code.  Things that you should never have to modify are placed in modules.
 
The last function of the Check_Entry routine is to highlight any cells in error with red, provide a brief description of the problem and/or it’s resolution, and communicate back to the calling routine if any errors were found. 
 
That’s the overview.  Next post – we dive into code. 

 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: