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

 

[ClimoC]

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?)

Not especially - it's more down to things like the Pivot tables and charts. There are some figures that people need to be able to have asap. So if one person makes a change on the other side of the road, within a couple of seconds all the live data tables will refresh and give the latest figures

But I think more to the point is that I have about 17 tables in the DB, 5 of them used frequently and in conjunction with one another.

So viewing (whilst another/multiple people view or edit), yes, definitely,and editing - it'd be rare but not unexpected if something's being input in a hurry by several departments.

 

[ClimoC]

Just a question aside from this xenou, when I close the recordset and connection, it's not going to try and write the recordset (in its state at that time, with its values) to the underlying table, is it?

Googled it to death but find no answer either way... Wouldn't want someone's un-requeried RS to write over a more up to date one... I suppose I could just force a requery before the close method to be sure, but wondering if it's even necessary?

 

[xenou]

According to my reference (the ADO Programmers Reference by Sussman), a recordset that is open will generate an error if there are changes in it that haven't been updated. That has been my experience with ADO - you have to update or cancel the update, then close. However, it also says that if in batch update mode, all changes are lost, which sounds reasonable (you are essentially discarding the batch).

Requeries don't have anything to do with updating the database. An unedited recordset that is closed is simply closed, re-queried or not. So you don't have to worry about that. If you tried to requery an edited recordset that had not been updated you'd get an error just the same way as if you tried to close it.

Note that it's a good idea with ADO to check that the database is open before trying to close it (this is another error):

If rs.State AND 1 Then
    rs.Close
End if

 

[ClimoC]

If rs.State AND 1 Then
    rs.Close
End if

[/QUOTE]


Good looking out. Thanks mate

 

[xenou]

I suppose you could go one further:

If rs.State AND 1 Then
    If rs.EditMode <> 0 Then
        rs.CancelUpdate
    End If 
    rs.Close
End if

Supposedly ADO is good about taking care of objects if you don't clean up very well though ... so maybe you can just not worry about that stuff. It's still a habit of mine to always close ADO objects that I open. I do find that there are errors generated with ADO if you try to do things like close recordsets that are unsaved, or close recordsets that don't exist. So I tend to worry more about making sure that errors are handled.