Over the years I have become more and more convinced that Data Entry should be performed on forms bound to temporary tables and at the end of the transaction a query should append the records to the permanent tables.
Any thoughts on this?
My experience is that there's always more than one way to skin a cat.
In some cases, I found that all I needed to do was bind a form to an ADO recordset. Other time, it was temporary tables, but most of time, it's simply directly to the table/one-table query.
In my experience, the requirement for a bulk transaction is relatively rare for two reasons.
1) By updating row by row, we don't need as many locks which is good thing for all of us.
2) By making the update more immediate, it's less likely that we'll run into deadlocks due to two users retrieving same rows at wrong time.
Overall, it's more simpler and safer to do row-by-row update. That should, however, not imply that there can never be a use for bulk transaction. I've written some forms where bulk transaction were an essential feature. More often than not, whenever a bulk transaction was needed, it was because it'd span more than one table. As an example, if the business rule was that there cannot be a parent record with no children, it would be a good candidate for bulk treatment and Access does not really make it easy to wrap such thing in a single transaction (at least not with bound forms). For those cases, I turn to alternatives already mentioned or on occasions, use a multi-table query form allowing the user to enter data for both parent & single child in a single form. This only works as an insertion and if they want to separate the insertion from updates. I know at least few other Access developers believe that insertion should be separated from updates for reasons like those.
But for large majority of data where a minimum unit would be a row, BeforeEvent suffices to validate the integrity of data and thus we don't really need the extra overhead that bulk transactions introduce.
At least that's how I see it.
