Updating multiple tables in a ms access database
In general, I don't use transactions except for maintaining atomicity, e.g., when creating an invoice, or doing any update that involves multiple tables.
It works about the way you'd expect it to but is not really necessary for the vast majority of operations in an Access application.
Now, unbound forms are actually fairly easy to implement in Access (particularly if you name your editing controls the same as the underlying fields), but going with unbound data editing forms is really missing the point of using Access, which is that the binding is all done for you.
And the main drawback of going unbound is that you lose all the record-level form events, such as On Insert, Before Update and so forth. Now, of course, Access ships with a default database engine, originally called Jet and now revised and renamed ACE, but there are many levels at which Access the development platform can be entirely decoupled from Jet/ACE, the default database engine.
Whether or not you want to record group membership in a data table, or maintain Windows security groups for this purpose is up to you.
You could also use a Jet workgroup file to deal with it, and provide a different file for the write users.
We're thinking of "growing" a little MS-Access DB with a few tables, forms and queries for multiple users.
(Using a different back-end is another, but more long-term option that is unfortunately currently not acceptable.) Most users will be read-only, but there will be a few (currently one or two) users that have to be able to do changes (while the read-only users are also using the DB).
"Transactions" is a topic of great importance for unbound and stateless apps (e.g., browser-based), but for data bound apps, the editing and saving all happens transparently.
With pessimistic locking, you don't to code, but you will almost always need to, as you can't just leave the user stuck with the default behaviors and error messages.
Jet/ACE has support for commit/rollback transactions, but it's not clear to me if that's what you mean in this question.
If you use Jet ULS, it can become really hairy to get it right.
It involves locking down all the tables as read-only (or maybe not even that) and then using RWOP queries to provide access to the data.