Beyond Excel: VBA and Database Manipulation

Mar 11 2010   8:43PM GMT

Updating Databases from Excel



Posted by: Craig Hatmaker
Tags:
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 ODBC.

We’re moving into controversial waters here.  It has been traditionally accepted that nobody should update a database except through a predefined program – for good reasons.  Updating servers from ODBC and Excel is considered by many auditors to be a violation – for good reasons.  DBA’s cringe at the notion of users updating server databases - for good reasons - because “fools rush in where angels fear to tread” (English poet Alexander Pope’s An essay on criticism, 1709).

In the last few weeks I have seen a few requests in the Question/Answer section of this site by people seeking ways to update server databases, especially the AS/400′s (iSeries) database, from Excel.  “It’s possible,” I explain, “but the answer is far too complex for a Question/Answer forum.”  Indeed it is, because if you want to avoid disaster, you MUST do the kinds of things traditional programmers have always done – check the data for sanity and silly mistakes before posting it to the database.

The techniques we will discuss will be along those lines.  We will see not only how to update the database, but also how to make sure the user put data in the proper columns, and that dates are valid, numeric fields contain numbers, text fields do not exceed field widths, codes and IDs match those in code tables and/or master files. 

We will be building on what we have done so far – so if you are new to this blog, please go back to the beginning and join us after you have built your library of routines given in prior posts.  We’ll be here waiting.

Function or Subroutine Module, Class, or Form Located in Post
Worksheet_BeforeDoubleClick Sheet1 Drill Down Part 2
Get_Data Sheet1 Drill Down Part 2
Prompt Sheet1 Drill Down Part 2
Build_SQL_Group_By modGeneral Group By and Aggregate SQL Functions
Add_XLFormula modGeneral Adding Excel Formulas – Part II
Parse_XLFormula modGeneral Adding Excel Formulas – Part II
Format_Results modGeneral Setting Column Widths or Hiding Them
Sort_Data modGeneral Adding Sorting
Freeze_Pane modGeneral Preserving Column Headers and Key Values
Build_SQL_Select_Fields modGeneral Adding Tables and Columns to the Fields Table
FieldColumn modGeneral Adding Tables and Columns to the Fields Table
Create_Named_Range modGeneral Creating the Fields Table
Fix_Name modGeneral Creating the Fields Table
Build_SQL_ID modGeneral It’s Time to Play
All Code and Properties for… frmPrompt Asking for It
All Code and Properties for… frmDatePicker Looking for a Date?
All Code and Properties for … frmSelect_Multiple Searching for Codes
SQLLoad modGeneral Say Goodbye to QueryTables
SQLRead modGeneral Using ADO to Read a Database
Pivot_Template modGeneral Wrapping things up
Setup_PivotChart modGeneral Building a Library of Routines – Setup_PivotChart
Setup_Pivot modGeneral Building a Library of Routines – Setup_Pivot
Settings modGeneral Building a Library of Routines – Settings
NameExists modGeneral Building a Library of Routines – ?Exists
ShapeExists modGeneral Building a Library of Routines – ?Exists
WorkSheetExists modGeneral Building a Library of Routines – ?Exists
PivotTableExists modGeneral Building a Library of Routines – ?Exists
ChartExists modGeneral Building a Library of Routines – ?Exists
Create_Easy_Button modGeneral Code for “easy” Button

 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: