Database Structure for Workflow Management System

Greetings,
I've read this over a few times, and I'm not certain how to answer. Seems I'm not alone in that seeing as how no one else has replied up till now. So let's see.

First of all, graduate to a newer version of Access. While A97 was a very robust version, it has limitations and less functionality than its latter counterparts.

That been said, you have not mentioned a few things. Who for example will be introducing work elements into this system? Will he/she use the same interface?

The approach you've taken using recordsets was certainly a smart one. With that many users sharing the same data, there's bound to be issues at one time or another with recordlocking.

Hopefully you already understand that you should split this database into a Front end and Back end. Each user would then get their own copy of the front end with all sharing the back end data.

Although a continuous form provides a nice appearance when presenting data, it may not be your best bet as it leads to issues if you are trying to select multiple records to perform a single action. We don't yet know what those actions are as they weren't really described, however, if you need to perform an action from a command button that may update multiple lines at once, you would have to code that based on the recordset clone, and provide some mechanism for the user to select in this manner.

If the form is a simple as clicking in two places (your outside subforms) to filter records in the detail section, then the design is fine. You might also consider a tree view in this case though so that the user simply has to "drill" down to his elements, and not have to refresh the data each time he selects a new category. You could use the "click" event of your tree view then to populate your detail section. Not unlike a directory browser in that respect and users are familiar with that interface.

You should consider the Update/Append approach to changing data in your backend. I wasn't clear upon reading your setup if that's what you're planning to do anyway, but what I mean is use Update and Insert statements based on the current record to update records in the back end. This will preclude a lot of issues that could occur if you based your form directly on a query or table. You might even consider temp tables for this, as you'll have a lot of users banging against that back end. So each user would have his/her own front end, and the data they are viewing could/would be populated in their front end. Once they change data, use the update/insert method to write to the main table. This also gives you (the administrator) a better opportunity to validate any data being changes.

There's not a lot more I can add at this time, The approach seems simple enough, but the issues you're going to be facing is managing versions and distribution of the application. Using a single front end and telling your users to log in all at the same time will not work in your case.

Hope this helps.
J