Excel in Front, part 1

This post is long overdue - I've been flat-out, coding a very interesting application for a manufacturing client, using Excel as the front end on a shared Access database.  The goal is to give a workgroup the ability to perform simultaneous edits to their work-in-progress tracking data, without any of the hassles, bugs, hideous file growth, sharing violations, lock-outs, etc. that you get with the built-in workbook Sharing mechanism.  There's a host of business logic involved, and they want the ability to design their own reports at will.

Again, I find myself reinventing the wheel.  "Why not develop your application in Access?" one might ask. My reasoning: Customer didn't have it, doesn't want it, can't support it, and more importantly, developing in Access is painful.  I prefer Excel.  The advantages weigh in: custom business logic is more easily coded in Excel.  It is.  And we needed in-grid editing, flexible data presentation, filtering, sorting, graphing... and whatever else we can dream up for versions 2 through N.  That's all in my Excel quiver.

"Oh, right. Excel lets you Link to a data source and populate tables..." yeah, I thought about it, but the users don't want to fart around with Data Connections, and my experience tells me it's a long road coding for those APIs. (if you know better, please tell me.)  And I wanted this to be an always-disconnected setup, where the workbook simply grabs data or sends updates to the db in quick bursts - no live links to network files that might be unavailable, renamed or changed.

The db was designed using an SQL Script to create the tables, populate the lookup data, and set up primary keys, defaults, and so on.  I ran the script to create the database in SQL Server, and then imported the tables into my .mdb file using Access.

I did not use the Access database design tools, though you certainly can.  I prefer to use a build script whenever designing a relational database; you drop and recreate the db often as you rework your design - that's Agile.  I also find that Access is a bit too user-friendly, in other words, not powerful enough.  I guess I can type SQL faster than I can ponder through menus.

In Excel, I set up a file-select control, and some text boxes for username and password.  The users on the network will each have a local copy of the application workbook - It's not linked, welded, cross-referenced or otherwise umbilical'd to any other file.  Can't you smell the freshness?  No pop-ups... ever.  As updates come out, they will be emailed to the users.  They will enter their credentials, file-pick the .mdb file, and work. That's Data Separation.

Here, again, I'm not tying into any built-in user management.  In a secure enterprise situation you probably have to set up database user accounts linked to network id's, but my client is looking for (and I strive to offer) the simplest solution wherever possible (SSWP), so we won't be contacting network support for domain credentials and such - the .mdb file is on a password-secured network drive.  Our application will maintain it's own user list... VBA code does the rest.

The project is still underway - next post I'll explain how I set up my object classes, control cell-editing, filter and sort, and conclusions: will it be another home run, or a teetering, reeking, house-of-cards? Stay tuned.