Sense check of the way I've gone about this please? - MsAccess ADO Multi-User system

[ClimoC]

Howdy

I've built a little app to reside on our shared-drive, which is designed to store relatively few records, and accessed by probably not more than 4-6 people at any single one time. Only one record can be Added/Edited/Deleted at a time (there are no batch delete/edit/add functions)

Here's the basics:
DB Filetype: '.accdb' - I believe it's a 2007 vintage
DB Conn Type: ADO (2.8)

ADO Connection Provider: ACE.OLEDB.12.0

All housed in an .xlam, which uses Excel's Userforms to interact with the data (seems tidier to do it here, ad I'm more familiar with them, and because there are linked pivottables and charts to the DB)

ADO CursorLocation : adUseClient

Rs's opened thusly:

FinRS.Open "Finance", ConnLink, adOpenStatic, adLockBatchOptimistic
Users.Open "Users", ConnLink, adOpenStatic, adLockBatchOptimistic
'etc

Userform has search field. Search for an item in the recordset, results returned to Listbox.
Listbox_Click event fires, pulling field values into relevant Text,Combo,and Listboxes

UserformControl_Change events fire something like this:

'ActiveID is a hidden label on the userform, which stores the (access')ID of the record selected in the Listbox
Private Sub NotesBox_Change()
Call FieldLookup("NotesBox", CLng(ActiveID))
End Sub


Public Function FieldLookup(ByVal CtrlName As String, ID2USE As Long)
If Not Application.EnableEvents = False Then 'Part of a solution to trigger when and when not to call certain routines for populating the userform, which has                                                                        'multipage tabs. 
                                                               
            Select Case CtrlName
            '###################
                        'Numeric Fields
                        Case "NotesBox"
                                        FinRS.MoveFirst
                                        FinRS.Find "FK_ID = " & ID2USE
                                        FinRS.Fields("Notes").Value = ConsoleForm.Controls(CtrlName).Value
                                        FinRS.UpdateBatch adAffectCurrent
                        Case "DeliveredDate"
'etc

The final step, which I haven't done just yet, is to pop in a few 'FinRS.Requery's where required, so that it gets the latest data from the table (Or, set it up to run as a time-scheduled function, every 10 seconds or so?)

Will this suffice in a Multi-User system? Do I need to introduce transactions (Begin, Commit, etc) around my batchupdates?

It's all working swimmingly now, but in a Local Desktop offline version. I want to be sure I won't run into any major issues once it goes out onto the Share-Drive and gets multi-user access.

Thanks
C

 

[xenou]

I wouldn't use batch updates at all -- not sure why you are doing that here. Also unless you have a reason for adOpenStatic I'd prefer adOpenDynamic. Static cursors hide changes from other users.

Edit: I only use transactions if I want to be certain that two or more queries succeed -- i.e., if I delete something then update something and I don't want to do the update unless the delete was successful ... etc etc. I guess another way of saying this is if you actually wanted to be able to run a rollback command, use a transaction, otherwise don't bother. If you don't use a transaction explicitly then your queries are in effect committed as they occur.

By Share-Drive do you mean a shared drive on your LAN (not a remote computer, right?)

 

[ClimoC]

I wouldn't use batch updates at all -- not sure why you are doing that here. Also unless you have a reason for adOpenStatic I'd prefer adOpenDynamic. Static cursors hide changes from other users.

Edit: I only use transactions if I want to be certain that two or more queries succeed -- i.e., if I delete something then update something and I don't want to do the update unless the delete was successful ... etc etc. I guess another way of saying this is if you actually wanted to be able to run a rollback command, use a transaction, otherwise don't bother. If you don't use a transaction explicitly then your queries are in effect committed as they occur.

By Share-Drive do you mean a shared drive on your LAN (not a remote computer, right?)

Hey xenou,

Yes, shared drive on LAN.

Every deduction I can make from its behaviour says that (possibly due to the connection type) '.Update' will update the recordset locally only (or at least only to the buffer/cache) whereas using Updatebatch writes it into the table within a couple of seconds (preferable)

Every bit of documentation I can find on Ado just mentions that batch update will write all changes back in a batch. But I did read something that said it commits to the table too - .update, I don't fully understand its behaviour, and find no more comprehensive reading on it than 'It changes the value'

 

[xenou]

Every deduction I can make from its behaviour says that (possibly due to the connection type) '.Update' will update the recordset locally only (or at least only to the buffer/cache) whereas using Updatebatch writes it into the table within a couple of seconds (preferable)

Update will make changes to the database. I'm not sure why you think this. But you are doing two things that don't really make sense here:

1) using adOpenStatic for the cursor type
2) using adBatchLockOptimistic for the locking type

Use adOpenDynamic for the cursor, and use adLockOptimistic for the locking type (unless of course you want to stick with batch updates). I think you are overthinking this - don't worry about the buffer so much. I suspect part of your problem is you aren't seeing changes, but that is because you are using a static cursor. That's just an educated guess of course.

.update, I don't fully understand its behaviour, and find no more comprehensive reading on it than 'It changes the value'

I think you are getting the picture now In a way it seems you've almost got things backwards. The point of batch updates is to cache changes and commit them all at one time. "Normal" updates (if you will) don't cache changes - they are committed immediately. You can take me with a grain of salt though - I've never used batch update except in tests just to play around with it.

Edit: actually doing a little reading myself, if you use a client side cursor you always get a static cursor. So maybe try instead a server side cursor, which means you can use a dynamic cursor (but in fact it seems in this case you'll always get a keyset cursor just to keep it confusing).

 

[ClimoC]

Update will make changes to the database. I'm not sure why you think this. But you are doing two things that don't really make sense here:

1) using adOpenStatic for the cursor type
2) using adBatchLockOptimistic for the locking type

Use adOpenDynamic for the cursor, and use adLockOptimistic for the locking type (unless of course you want to stick with batch updates). I think you are overthinking this - don't worry about the buffer so much. I suspect part of your problem is you aren't seeing changes, but that is because you are using a static cursor. That's just an educated guess of course.


I think you are getting the picture now In a way it seems you've almost got things backwards. The point of batch updates is to cache changes and commit them all at one time. "Normal" updates (if you will) don't cache changes - they are committed immediately. You can take me with a grain of salt though - I've never used batch update except in tests just to play around with it.

Edit: actually doing a little reading myself, if you use a client side cursor you always get a static cursor. So maybe try instead a server side cursor, which means you can use a dynamic cursor (but in fact it seems in this case you'll always get a keyset cursor just to keep it confusing).

Haha yes, very confusing. Haven't even heard of a key set cursor before...

So would you say that if I changed updatebatches to updates, cursor and lock types as you've detailed above, and put in the requeries where appropriate, that this should work as simple single-record multiuser system? (single record as oppose to batch stuff - adaffectcurrent in essence)

Thanks again xenou

C

 

[xenou]

I don't really know - I probably wouldn't do this in Excel I'm afraid (I'd use Access directly). It seems from my own researches that the simplest thing you could do would be to use a server side cursor. I don't worry about locks and cursor types very much - I always specific adOpenDynamic and adLockOptimistic. The kind of cursor and lock type you actually get depends on what the provider supports (i.e., Jet or Ace). Client side cursors are always static (with Jet). But even their you are only talking about the row(s) in your recordset, which might just be one row the user is currently on. It's not clear to me why you are having so much trouble - typically you just make your changes and all is well, especially with such a small number of users.

 

[ClimoC]

I don't really know - I probably wouldn't do this in Excel I'm afraid (I'd use Access directly). It seems from my own researches that the simplest thing you could do would be to use a server side cursor. I don't worry about locks and cursor types very much - I always specific adOpenDynamic and adLockOptimistic. The kind of cursor and lock type you actually get depends on what the provider supports (i.e., Jet or Ace). Client side cursors are always static (with Jet). But even their you are only talking about the row(s) in your recordset, which might just be one row the user is currently on. It's not clear to me why you are having so much trouble - typically you just make your changes and all is well, especially with such a small number of users.

Well I think that the first time I tried Update, with the proper Lock and Cursor types, I think I made the mistake of testing it with a field whose onChange event didn't call the fieldlookup/writevalue function! (D'oh!)

So the update is working Juuuust fine now.

My last bit of 'concern' is requerying. Given that the underlying tables need time to reflect the changes passed to them by the recordset connection (if you put Rs.Requery straight after update, you get the copy of the database before the change has time to write), I'm maybe just thinking of slapping it in as an On-Time event, set locally within the Excel instance, which can change the interval / next run based on actions done (so if you edit a field, it waits 5 seconds before requerying)

Do I need to requery in a multi-user system? It sounds a stupid question, but I've already asked a few of those that weren't as straight-forward as I thought. A recordset is just a cached copy, right? You can use update to make the underlying table reflect the values of the record the cursor is on, I would think that all other records stay as they are until a requery is fired?

 

[xenou]

(if you put Rs.Requery straight after update, you get the copy of the database before the change has time to write)

I don't think so - you get the updated changes (in fact, you would get them even without a requery).

A recordset is just a cached copy, right? You can use update to make the underlying table reflect the values of the record the cursor is on, I would think that all other records stay as they are until a requery is fired?

Not entirely. When you update the recordset you update the data source. Caching is not meant to be a way to use the recordset independently from the data source, unless you are using a disconnected recordset. In either case, updates, not requeries, are how you make the changes back to the database and you are updating the dataset here. It's true that sometimes you need to requery but not as often as you might think. Just navigating to the next record is often enough to cause a resynch of the recordset. It's still not clear why you are having so much trouble. You might need to provide a test case that demonstrates the problem.

 

[ClimoC]

I don't think so - you get the updated changes (in fact, you would get them even without a requery).



Not entirely. When you update the recordset you update the data source. Caching is not meant to be a way to use the recordset independently from the data source, unless you are using a disconnected recordset. In either case, updates, not requeries, are how you make the changes back to the database and you are updating the dataset here. It's true that sometimes you need to requery but not as often as you might think. Just navigating to the next record is often enough to cause a resynch of the recordset. It's still not clear why you are having so much trouble. You might need to provide a test case that demonstrates the problem.

Thanks xenou. I ran two instances next to each other, changed a record in one (this is all with the new dynamic server based cursor, lockoptimistic, with updates, not batches), and the other RS didn't reflect the change that the first one had written back to the DB. I had to get it to do a requery before it saw the change the first instance had made.

All seems to work now... but I've said that a dangerous number of times before

C

 

[xenou]

Just out of curiousity do you really expect two users to be simultaneously working on the same record very often (updating and or viewing it?)