Updating multiple tables in a ms access database

09-Dec-2017 05:42

It's good design and also makes the apps more stable, as only the data tables end up being opened by more than one user at a time. While the 255-user limit in Jet/ACE includes both read-only and write users, it's the number of write users that really controls how many simultaneous users you can support, and in your case, you've got an app with mostly read-only users, so it oughtn't be terribly difficult to engineer a good app that has no problems with the back end.Basically, I think your Oracle background is likely leading you to misunderstand how to develop in Access, where the expected approach is to bind your forms to recordsources that are updated without any need to write code.Now, for efficiency's sake it's a good idea to bind your forms to subsets of records, rather than to whole tables, but even with an entire table in the recordsource behind a data editing form, Access is going to be fairly efficient in editing Jet/ACE tables (the old myth about pulling the whole table across the wire is still out there) as long your data tables are efficiently indexed.Record locking is something you mostly shouldn't have any cause to worry about, and one of the reasons for that is because of bound editing, where the form knows what's going on in the back end at all times (well, at intervals about a second apart, the default refresh interval).It's crackable, yes, but your users would be committing a firable offense by breaking it, so it might be sufficient.I would tend to not implement Jet ULS at all and instead just architect the data editing forms such that they checked the user's Windows logon and made the forms read-only or writable depending on which users are supposed to get which access.

The write users would log on as some other username (transparently, in the shortcut you provide them for launching the app, supplying no password), and that would be used to set up the forms as read or write.

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.

The key point here is that Access apps are databound, so as soon as a form is loaded, the record has a read lock, and as soon as the record is edited, whether or not it is write-locked for other users is determined by whether you are using optimistic or pessimistic locking.

Again, this is the kind of thing Access takes care of for you with its default behaviors in bound forms.

The write users would log on as some other username (transparently, in the shortcut you provide them for launching the app, supplying no password), and that would be used to set up the forms as read or write.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.The key point here is that Access apps are databound, so as soon as a form is loaded, the record has a read lock, and as soon as the record is edited, whether or not it is write-locked for other users is determined by whether you are using optimistic or pessimistic locking.Again, this is the kind of thing Access takes care of for you with its default behaviors in bound forms.I haven't done but one such app in my 14 years of professional Access development.