Users see same information---help!

[Molemanryan]

Hello everyone,

I am having some issues with a custom access database I have created. Basically the database acts as a workflow tool that aids users prioritize/divvy up work in a list. I have about 10-15 users active in this tool at any given time working items.

The database has been split, and each user has their own copy of the front end on their local PC. I also have the back end data stored on a SQL server (2005), and I have the front end(s) connected to the data via ODBC.

The front end has a form which pulls one item for a person to work, it does so with Dlookup. It's very important that this item is unique and not being worked by anyone else at the time the user has it open. I have programmed some safeguards in VBA to prevent multiple users from access the same item, whereby when the item is loaded on the form it will update that item in the back end table to prevent other users from accessing it.

Here's the dlookup statement from the form:

=DLookUp("min([Order])","[dbo_to be checked]","[completed]=0 and [selected] = 0 and cdate(now())-cdate([reduction1])<7 and [order]>0 and (cdate([lock valid to]) - cdate(now()))>6")

This will find the smallest order number to display based on the item not being completed, not being selected and that the item's age is only about 1 week old but no older.

Below is the code which blocks off the record for the current user:

Private Sub getnew()
Dim user As String
user = UCase(Chr(39) + Environ("Username") + Chr(39))

'This code executes the scrolling to the next record
'This may need to be augmented with an 'opened' field instead of line item locking
'Resetting the system message text to prevent user thinking item is still paused

DoCmd.SetWarnings False

'Reset variables
Me.Text61 = ""
Me.List89 = Null
Me.List107 = Null
Me.Text85 = Null
'Update Form
Me.Refresh

Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object
Dim Myrecset As New ADODB.Recordset
Dim sqlStr As String ' String variable to store sql command
Dim ord As Integer
Dim acct As String

    
    With MyConnObj
    .Mode = adModeShareDenyNone
    .CursorLocation = adUseServer
    .Open _
        "Provider = sqloledb;" & _
        "Data Source=thisisright;" & _
        "Initial Catalog=thisisright;" & _
        "User ID=thisisright;" & _
        "Password=thisisright;"
    End With
    
        acct = Chr(39) + Me.List22 + Chr(39) & ";"
        ord = Me.Text24
    
    With Myrecset
    .ActiveConnection = MyConnObj
    .Source = "update [to be checked] set [selected] = 1, [type] = 'Version 1.6',[start time] = getdate(), [user] = " & user & "  where [Order] = " & ord & " and [selected] = 0 and [contract account] = " & acct
    .CursorType = adOpenDynamic
    .LockType = adLockPessimistic
    .CursorLocation = adUseServer
    .Open
    End With
    
'    Myrecset.Update


' Clean Up and go home.
Set MyConnObj = Nothing
Set Myrecset = Nothing
DoCmd.SetWarnings True
End Sub

Essentially what occurs is users have the same account open and work the same item, and the last user in writes over everyone else. I've heard about adding record locking but I am not too familiar on how to do this, or if this is the right way to go. Also, to pull the information up on the form is starting to take too much time in some cases ~30 seconds, any suggestions on speed improvements or alternative implementation would be great as well.

Thanks!

Ryan

 

[jdraw]

I do not have experience with record locking in multiuser . but I did some searching and found this
http://www.softcoded.com/web_design/upgrading_access.php

I also recall some issues with the Access Min() function, the suggestion was to use DMin()

Hope it's useful. Please post anything you find since there doesn't seem to be many examples of record locking to prevent "clashing/overwriting".

 

[spikepl]

Let's see:

1. You have implemented a measure that should give the user a record that is not being worked on by another user.

2. You have implemented a measure that indicates that a record is being worked on by another user.

Despite all of the above, users do get to work on the same record. The logical implication is that your measures do not work as you intended.

Perhaps 1. does pick up locked records.
Perhaps 2. doesn't lock them as you intended
Perhaps 1 & 2 work as intended but 1. is done and then some time passes before 2. is done - your code doesn't show what happens when.

There is some debugging work here, that only you can do.