Chatmaker   950 pts.  |   Nov 25 2009  10:48PM GMT

Hi Rod,

I just wanted to give you a heads up that my blog will be covering how to update data on any database, including the iSeries directly from Excel (This will make some ITIL auditors cringe).

We “post” employee time sheets directly to our iSeries from Excel using just the VBA that’s included free with Excel, and the “iSeries Access ODBC Driver” that came with your operating system. My users love it.

Excel and Pushing Data to the AS/400
As you already know, users prefer entering data in Excel where they can model it, sort it, test it, and finally - upload it into a host application. The big problem is layering the data edits over top of the users’ entries to ensure what goes in the database is clean.

Traditionally, we push a flat file version of the table to the iSeries and run a batch program to check the data, and if it’s clean, add it to the database. The problem with this approach is that when the data isn’t clean, communicating back to the end user what went wrong becomes an IT disruption at a time when scheduling pressures might already be in play (such as making sure those time sheets are in, in time for payroll).

In my opinion, the better approach is to put the edits in Excel to insure data is clean at the source - just as any mainframe application would. This gives the user immediate feedback that they like and can also be engineered to help them, through defaults, drop down list boxes, formulas, copy and paste, coded edits, etc. to make clean entries.

If this is of interest to you, please check out my Blog in a few weeks (or if you’re ready to start reporting on iSeries data directly within Excel - check it out today). In any case, if anyone has opinions on this potentially controversial technique, I’d appreciate comments posted to: Beyond Excel: VBA and Database Manipulation