I apologize in advance for the length of this post, I wanted to include enough data to make my questions clear.

I'm creating a database for multiple kinds of audits, and I'm trying to do a couple difficult (and usually not recommended) things that may make my life easier in the long run. The source data comes from a very large data warehouse, I randomly items to audit, then copy the data elements the auditors are interested in to a SQL Server (2008).

Each audit has a top level set of data, 2 or more subtables, and possibly but not always subtables below those. Some but not all of those items can be marked "wrong" by the auditor, and the corrections would be children to those data points, along with a scoring field that indicates how significant the error was.

At first there will only be one kind of audit, but I expect that will expand to dozens, each of which may have overlapping but differing sets of data to display and audit. Each audit may also have fields added or removed over time as the auditors refine their processes. If I use traditional tables, I would potentially have to create dozens of similar tables, or have very wide tables with lots of blanks, as well as the associated queries, and multiple display forms.

1. I'm thinking of using a hierarchical data table to represent all the audit data. Each data element is stored as a varchar, along with a key representing the data type, and a recursive key linking them through parent fields to the top level of the audit. I plan to use recursive CTE's to extract the data as needed. None of the data needs to be edited except comment fields, and the child fields the auditors add (for corrections & scoring). Some of the fields represent foreign keys to lookup tables elsewhere in the DB.

? Any pitfalls I should be aware of for this method? I think I've figured out the rough methodology on how to handle, but haven't started the actual programming yet.


2. Since the data to be audited varies for each type of audit, and may change over time, (and I'm not sure if the resulting data from the CTE will be in a bindable format) I was hoping to use a dynamic form to represent the data, so that I can use table-stored settings for order, placement, width, height, auditability, etc. for the various different data elements, and put subtables into a tab control or subframe.

? Does Access support any form of on-the-fly form construction / editing? If so, would it still work in the run-time version of Access? Are there bloat/corruption concerns doing this?

? If not, could I instead embed an HTML or ASP.Net window inside the form after constructing it via code? How difficult would it be to link individual fields and/or buttons in that window to click events I could respond to in VBA? What's the best technology/language to use for this? I'm no web programmer but I'm a VBA and SQL expert and have used C++ in the past. Note that my company is still stuck on IE 7 and some people have IE 6 still, though I could require them to upgrade to 7 for this app.

? Is there another option I'm not aware of that might work better? I suppose moving to VB.Net would work but don't have the time to do so, and distributing executables can be problematic in our controlled software environment.