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.
Unless…
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?
Scott Lyerly 
6 responses to “Excel Geeking: A Question For Other Excel Geeks”
[…] Lyerly is looking for your input on his Excel as a front end solution. What do you think about his idea of keeping SQL statements in a database table, and using those to […]
I think it sounds like a great idea. Generally, the less hard-coded stuff, the better. Your strategy makes it much easier to update the SQL statrments, and easier updates equals better applications in my book.
I even think that I would have tried to do something similar if I faced the same situation.
A related question, why are you loading the support tables into the workbook and not working “live” with the database?
Thanks Mikael. That was the idea. As little hardcoding as possible. There will always be some, such as the SQL to pull in the SQL (getting sort of existential …), but that can be an easy, standard “SELECT *” statement, make it nice and clean.
The support tables contain the values needed for fields requiring data validation. I keep the available values in the database, where I can update them whenever something changes, and load them into the workbook at start-up. That way I can exert further control over the UI.
(Perhaps I’m too controlling…but that’s a different topic from Excel) 🙂
Hi Scott,
I also agree that you are going in the right direction.
1) Separate the database from the front end.
2) Support Tables for data validation criteria that are not likely to change in a single session.
This will minimise network overheads and maximise response times in the front end.
3) Central control and maintenance of the SQL for the Support Table data.
As for your comment “That way I can exert further control over the UI. . . Perhaps I’m too controlling …”, – NOT!
Your staged approach has allowed you to separate individual components of the system and transition from “Excel Database” to “Real Database” with minimal interference in the every day workflow. The same goes for the Support Tables. “Divide and Conquer” to optimise maintenance of every component of the system and remove any unwanted end user interference.
Obviously, your workbook is working with the whole of the database. Are you also housing the rest of your SQL in the database to be called on by the workbook? You didn’t specify, but I would assume that you have done so.
Since, in the long term, databases are subject to being moved to new locations by the IT department, I would add a simple lookup in a guaranteed location that points to the current location of the database. Once I have made a system fool-proof I look to making it future-proof as best I can.
Thanks David!
Yes, I will be using some additional SQL in the template. The overall structure of the workbook application will be to load data upon opening using SELECT, the users will make changes, then push the data back. The push back will be a combination of INSERT, UPDATE, and DELETE.
As far as the SQL to push the data back, my plan is currently to house only the first part if the SQL in the database. I need to keep the back half of the SQL kind of free form so that it can place values into variables, use the variables to complete the build of the full SQL statement, and push it back to the database.
I’m trying to figure out the best way to allow the expansion of columns if need be. So if I start off with the need to UPDATE fields 1, 2, and 3, and then need to expand to column 4 at a later date, I can make a database change and the SQL will pick up that fourth column. I think it will come down to yet another table that controls the range that gets parsed for the values.
On my former job, I wrote Views in the database that we fetched with simple queries (ok, sometimes not so simple queries) in Pivot Tables in Excel. But 99% of what we were doing were READ. If you’re going into the C, U and D of CRUD, then I’m afraid I don’t see the reason to use Excel.
Actually, I think this is the cue to get out of your comfort zone and try something new. Just think back to how your SKU Tracker opened the door for countless applications and truly deconstructed your previous framework for approaching problems.
A favorite of mine is Python + Django. You can have an application ready in ~15 minutes, and if all you’re doing is CRUD, then it is all automatically generated for you by the Django Admin (with varying levels of customization). The official website is https://www.djangoproject.com/ and their community of developers is second to none.