Okay, this one is a bit out on a limb, since I don’t have a lot of followers, and expect most of them follow me for the writing or running posts, but here goes.
I have a question for my fellow Excel geeks and gurus regarding development and design.
Anybody with thoughts or opinions is welcome to jump in with comments.
And who isn’t thinking about Excel programming going into the weekend, amiright?
Here’s the background:
I have a set of workbooks that are basically set up to “act” as databases. There being a restriction on the use of MS Access, Excel became the database of choice. Let’s not publicly debate whether or not Excel is a database, it’s what I inherited and have to work with. We all know it’s “database”, with quotation marks around it. Let’s move on.
I’ve been trying to get the users out of this Excel “database” by using other, separate workbooks for reporting shells, and using SQL to retrieve the data from the Excel “database”. So far, so good.
I’m at a point now, however, where I may have the option to migrate some of this Excel “database” stuff to a real database, while keeping Excel as a front end and user interface. Beautiful, Totally in my wheelhouse.
Here’s where I’m, not stuck exactly, but rather in a bit of a quandary as to how far to take my new approach.
The Excel UI workbooks are going to require support tables to make them run. Those support tables are going to be housed in a database. Upon opening the UI workbook, the SQL statements will fire off, careening over the network via ADO connections, and sucking the data back down the pipe, only to land in the UI workbooks and make everything sunshine and roses.
Which, based on my current programming, it does.
Except I don’t want to hard code five or ten or fifteen or whatever number of SQL queries it will take to load the support tables.
So I came up with what I felt was a nice, tidy, and portable solution. A SQL table range in a worksheet that will house the SQL statements themselves, and as I iterate through it, the SQL (all SELECT–let’s not make life hard with INSERT, UPDATE, and things of that ilk) will be read from the cells, fed into a custom class I built for managing ADO connections to databases (it’s sweet, I’ll share it sometime), and off to the database the SQL will go.
Fantastic so far. No problems. Everything working like a dream.
Until the thought occurred to me, “You’re still hard coding your SQL, you’re just doing it in a worksheet instead of directly in VBA.”
Well, gosh darn it all, that’s true. The SQL would have to be written into the table range prior to the roll-out of the UI workbook, and if it changes, I’d have to recall the workbook and issue a rev’d version.
And here’s where I need the help. My next step, which is done by the way, was to create another table in the database, one that houses the SQL statements themselves. So now, when the UI workbook launches, it loads all the SQL statements first, THEN fires them off one by one until all the supporting tables have been loaded.
At this point I took a step back and thought, am I nuts? I mean, behind the regular brand of nuts? Am I building this thing up to much for a workbook application? The idea behind it was to load SQL statements that could be centrally managed in the database by an administrator. If something in the SQL changes, then the SQL statement housed in the database can be changed, and when the UI workbook runs, it will pull down the new SQL and run with that, rather than having to go into the UI workbook and change it manually in a worksheet.
So the question I have for my fellow geeks and gurus is: have I gone too far? Is this overkill? Am I SQL-drunk?