Saving records in a multi-user environment

[dsmaj]

Here's my daily question for you geniuses

What is the prefered method of saving records in a multi-user environment?

My project is nearing completion, and I've begun to turn my attention to recordlocking, and the methods with which I am saving records in general. Currently my code is riddled with random record saving code ranging from running stored querydef's from CurrentDB to using docmd.runsql() to run dynamically created action queries, and everything in between. I'm sure the correct method of running these queries is a little more involved than what I've been doing, and I'd like to begin converting my code. So what's the best way to perform these record saving operations? Keep in mind the following:
1) This is a multi-user fe/be split database
2) I don't want that annoying access dialog box to pop up that informs the user that they're "Permanently changing (x) number of rows."
3) I have no idea how I'm going to handle recordlocking yet. However, I've been assured that there will not likely be more than two or three people with the same table open for writing purposes at the same time.

In addition, the way my application is structured, I have no forms based on queries or tables. Any operations that are performed on existing record's are done by returning recordsets to be used within the code--I have found this to be a much more flexible approach with respect to ensuring the integrity of data that is exposed to user modification, among other things. With this in mind, is there a record locking technique that will allow me to lock a specific record only, or do I need to lock the entire table while a user is working with data within it?

On a related note: Setting asside the obvious 'good programming' practices, does access automatically clean up hanging objects and open tables/recordsets at the end of a routine or code-block where the object is locally/privately defined? Or must you manually clean things up?

Thanks in advance folks,
Sam

 

[dsmaj]

- by using bound forms based on queries. You seem to have done a whole lot of coding to avoid using bound forms so I don't know what to tell you. You could have attained all the "control" you needed by simply using the correct form events to ensure that data is properly edited. This is a mistake commonly me made by experienced programmers using Access for the first time. Rather than learning how to use form events they simply avoid them entirely and write their own code.

You caught me Pat This is in fact my first Access application. I've done a lot of development previously in C and just feel "right" coding exactly what I want to happen. On the upside, the application works great, and I've got a manageable balance of scalability and low-level control. Despite my lack of Access experience (not that it's really hard or anything), I was somewhat suckered into developing this application. Some of my very Access-oriented co-workers will be looking over it before deployment, so I hope I don't get ragged on too hard for writing ten-times more code than was needed

I generally use the default setting for queries which is No Locks. This will occasionally result in a conflict and a user will be warned that the record he was changing is locked, etc. As long as your users understand their choices and know what to do if this message appears, you shouldn't need to do anything.

So let me get this straight, setting "No Locks" will in fact not allow a user to modify a record?

If you want to avoid this message, you can change the setting on your queries to Edited record. This will prevent someone from attempting to edit a record that is currently "dirty".

This sounds like what I want to do, however I'm going to need to deal with it programatically at the time that I try to open a given recordset--does Access just throw an error code or something if the record is "dirty"?

The third setting of "All Records" is pretty extreme and will probably cause more problems in a multi-user environment than it solves. However, if you are doing bulk updating it is probably a good idea to use that setting for that particular update query. This will prevent any interactive user from locking a record that your query may need to update.

By 'Bulk Updating' in this situation, you're refering to updating all, or most records in a table, correct?

In general, can a user still open a locked record or table as read-only in any of the above discussed situations?

Thanks Pat,
Sam

 

[dsmaj]

- Your unbound form method will not protect you from write conflicts. A record is not locked until someone tries to save a change to it. During the period of time that the update is being made, no one else will be able to read the record but if two people read it before one of them tries to update it, the second person will get an error if he attempts to also update the record.

What about the LockEdits property of the .OpenRecordset method? It would suffice for me to be able to specify a locking-type when I open my recordset, and have it persist until I close out that recordset....would this work the way I think it would? Taking a quick look through MSDN, I see something about Pessimistic locks with .OpenRecordset which, despite its name, sounds like it might be what I want. However I see that using a Pessimistic lock, Jet will lock the entire page within which the recordset resides--given that a page is 2k, and the recordsets I am returning are much smaller than that, this could cause me some problems....Any ideas here?

Thanks,
Sam

 

[dsmaj]

Uh oh...I am in fact using a Memo field for a few of my tables--tell me more about this bug...

I think I may be overestimating the transaction volumes this database will be handling, so I'm going to do some testing with No Locks and see how it goes.

As far as bulk updates...are my action queries actually considered bulk updates? None of them are actually updating more than one record...and in many cases, they're simply auto-numbered append queries. Must I treat these queries as bulk in nature?

Thanks,
Sam

 

[dsmaj]

Update

That's a good idea Pat...here's a fairly comprehensive overview of the results I'm getting under various conditions...

I have a bound form, bound to tbl_Deliverables, and an unbound form that is simply trying to run an SQL UPDATE query...

With the bound form set to "NO LOCKS":
- If I select a record, modify it, but do not move off of the record (which would cause it to update), and then I open my unbound form, the record that I am in the process of modifying in the bound form displays WITHOUT the changes. If I try and make changes to the record with my UPDATE statement in my unbound form, the changes are written to the database without error. When I subsequently move off of the record in the bound form, Access displays that dialog box with the Save/Copy/Drop options. This scenario is simple for me, since the UPDATE query runs relatively instantaneously (i.e. a user isn't afforded the opportunity to 'sit' on the record while they are modifying it) This is where things become a bit of a problem however, since between the time that the records data was loaded, and the time that the UPDATE query runs, another user may have loaded and UPDATED the same record without the current user ever knowing. This is why I would be interested in some way to 'set the dirty bit' manually on a record when the data is loaded, and then unset it after the record is UPDATED. (Can I do this somehow?)

With the unbound form set to "Edited Record":
- If I do the same as above, I receive the standard Access "can't update record(s)" message.

In both cases, if the record is not currently being edited in the bound form (i.e. it has already been saved) then there are no restrictions on the unbound form at all (which is fine).

With the unbound form set to "All Records":
- If I do the same as above, the application actually errors out with run-time 3008 "the table is already exclusively opened by another user", and drops into my VBA code where I try to run the UPDATE. This actually happens if I try to edit ANY record in the table that the bound form is bound to--regardless of whether the record is currently being edited or not. I found it particularly interesting that this was not handled by a standard Access error-handler (similar to the one for "Edited Record" locking).

Given the nature of most of my queries (especially my action queries), being able to 'set the dirty bit' as mentioned before, would be especially valuable. I'm beginning to realize now why you suggested a bound form, Pat However, I still think I prefer the coded-out version of what I'm doing, as I can understand what's going on by looking at the code (and my thorough comments of course), rather than having to shuffle through bundles of forms and their property windows to see what's going on. Anyway, that's probably just my old-school preference. However, I have heard that using a bound form is not a good idea for split databases that are running over a network. I can see how this might be, due to the fact that the recordset is then being returned no matter what (usually) when the form is loaded. The way I've done things, I'm ONLY running the queries when absolutely necessary...my forms are simply interface, and aren't loading up recordsources that aren't necessarily going to be used. Anyway, is there much truth in "what I've heard"?

Whew...long post. Anyhow, to sum up my current questions:
1) Can I manually set a 'dirty bit' on a record? (Even if it's somewhat of a hack)
2) Am I experiencing expected results with the "All Records" locking scheme?
3) Is there an inherent benefit to not using bound forms in a split network database?

Thanks again,
Sam