The Macgyver Approach, or is it MacGruber?

[emorris1000]

If (desire to skip backstory) Then Goto 15

Else

Read on

I have been working on this database off and on for ~1 year. The first number of months were just trying to figure out how to programattically upload the data (~250-500k records in ~1k files with inconsistent formats.) But I got that, then spent the next 6 months periodically messing with it or completely forgetting it for a couple of months.

Then I realized that the whole thing had become a tangled mess of garbage filled with a bunch of prototype tools that I had developed while trying to learn how to use Access. I have a programming background, sort of, but had almost no database experience. So I had like 40 queries, terrible naming conventions, redundant forms, and too many temp tables to admit to in public. But there was at least some value in it, I learned quite a bit about things.

I scrapped the entire front end and started again. Cleaned the naming conventions and designed properly. Within 2 months I had something that looked good. 2 primary forms and 4 queries gave me access to the data I wanted in a strong variety of ways. Context menus lead to non-primary form. All data exports to Excel are automated etc etc.


15


I finally launched my alpha executable at work today for some people to play around with, and the people using it like it, a lot. But there's something in my gut that makes me wonder if I have McGuyvered this too much.

Every form is unbound. I used to try and use bound forms but then I decided that I liked combo boxes and drill-down listboxes more than subform/datasheets, and I couldn't figure out how to do that with bound forms so I unbound every form. Also I still don't entirely get bound forms.

There are ~15 tables and technically none have defined relationships. They used to, its just that when I manually split the database it killed the relationships. Everything still worked and I didn't even notice the relationships were gone until a couple of hours ago. Everything still works fine because they are all joined in queries as needed.

I think I have done a good job of removing excess/repeated fields, but I still don't really get what normal form it is, if it even is one.

There's a couple of key fields that exist in almost every table. The relationship structure (if it existed) would be a bit of a spoked wheel with a couple of central hub tables from which a bunch of different sub tables sprang from. But there are some really wierd relationships in there that I am pretty sure are highly innapropriate and would give a proper DB designer a coronary.

Anyways, long story short is that I have no idea what I am doing when it comes the theory of design. I got it to work, but I have no idea if it's going to explode in my face. Part of the problem is that I simply can't find good examples of this type of usage. Basically it is a prototype LIMS system that manages research project data from a couple of batch chemical reactors and the analytical data of the products and the ingredients, as well as sensory data from the reactor.

---------

To the question then. MacGyver or MacGruber? Clearly you can teach yourself to use Access and then do so in non-traditional ways, but what are the mistakes that can lead you to total MacGruber situations?

The only real problems I have right now have to do with proper implementation of a backend on a server, and I am pretty sure I can figure those out (move the front end to the workstation etc etc.). So things are running good, but I have this small voice in the back of my head that keeps saying "yeah, it's working fine now, but this is gonna blow up eventually."

 

[The_Doc_Man]

Unbound controls are common. Unbound forms? Less common. If you have a good design in mind, form-binding should be natural and obvious. If it is not, you have possibly confused yourself badly. (Not to worry... stuff like that happens to me, too.)

If the form binding isn't obvious to you, there is a disconnect between your design and your real business or entity model. See, a form is just a window into the database with the ability to do updates or special functions thrown in. If nothing is bound, then you are using the form strictly as a control panel. OK, nothing really wrong with that, but your inability to construct meaningful forms from the tables means your viewpoint doesn't match the reality of the data. I would say that it is time to step away from the keyboard. Do some sketching of data flow to see where your real data fits. Work on the problem from a different viewpoint - data flow modeling. Somewhere in there, you should find what you need to figure out the form binding issue.

 

[emorris1000]

Ah, one thing I forgot to mention. From the point of view of the users, this is an entirely read only database.

It's also graphically intense. Lot's of graphs. And graphs in access seem to be a pain in the butt. I used to run graphs with bound forms using a parent/child relationship to define the graphs, but there were a LOT of glitches with that so I just redesigned the graphs to build from a query that referenced an unbound forum control.

That's what a lot of this is built with, one shot queries for the listbox/graph/etc that reference an unbound forum control, like a listbox, that has a query to a table (or a number of tables) as it's recordsource. I still don't entirely understand the purpose of binding instead of just using recordsource queries, but I guess that has a lot to do with me running this as a read only tool. I found that doing it like that gave me more control over what I wanted and less of the "you can not build a link between unbound forms" errors that made me so incredibly mad.


Edit: Ok, looked into this. My two primary forms are all bound. The graphical/analytical forms that they generate are all unbound.

 

[CBrighton]

As someone who is self-taught in Access I started off with 100% unbound databases.

They may not be the most common way of doing it, but they are not inherently going to go wrong either.

These days I use mostly bound controls, with the odd unbound control for filtering forms / reports, etc.