Hi Guys,



I'd like to get your thoughts on what you feel is the best approach for managing junction tables when using unbound forms and controls. My situation is this, I have 2 junction tables in my database one of these junction tables functions more as a lookup table. It is a marriage of two lookup tables and will not be modified unless one of the lookup tables is modified. This table may in fact not be needed, but I created it anyway just to be safe. The other table will be modified as records are entered, edited, or deleted.

The controls on the form are unbound, except for those that display data from lookup tables. They are bound only to their associated lookup tables and not to any of the fields in the primary database tables. All data insert, update, delete, and display functions are handed manually through code. Recordset Adds, Updates, and Deletes are issued and data is moved to and from the database fields or form fields as appropriate. The junction table may or may not have records for a matching record depending on what kind of record it is. If records do exist, there can be 1 to N of these records.

I am considering using Transactions for the process since the creation of each complete record involves managing multiple tables. This way the process is treated as a single unit and can be canceled with a rollback if I choose.

I would be interested in hearing your thoughts on the matter. This is only a discussion and all opinions are welcome and will be respected. Thanks in advance for your input.