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 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.