Concurrent User Issue with Form - Users View Same information (1 Viewer)

[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 MyConnObj As New ADODB.Connection 'ADODB Connection Object
Dim sqlStr As String ' String variable to store sql command
Dim ord As Integer
Dim acct As String
Dim user As String
user = UCase(Chr(39) + Environ("Username") + Chr(39))

'This code executes the scrolling to the next record
'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

'Connect to Data
        MyConnObj.Open _
        "Provider = sqloledb;" & _
        "Data Source=mydatasource;" & _
        "Initial Catalog=thisisright;" & _
        "User ID=myid;" & _
        "Password=mypass;"
        
'Assign Variables to account and the order
acct = Chr(39) + Me.List22 + Chr(39) & ";"
ord = Me.Text24
    
'Performing the update to the backend
sqlStr = "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
MyConnObj.Execute sqlStr

Set MyConnObj = 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

 

[Molemanryan]

Please help! If any additional information is needed please don't hesitate to ask!

 

[shadow9449]

Ryan:

Most obvious question:

Is it possible that more than one user has the same Windows user name on the network? I've encountered networks where ALL computers had the user name as "user" and that made a mess when trying to identify who entered information. I changed the method of identifying users by computer name so Environ("Username") became Environ("Computername") and that solved it.

SHADOW

 

[Molemanryan]

Shadow,

I don't think there is someone logged in at two separate locations. I'm working in a corporate environment, each user has their own station and user id. I can investigate to see if this is occurring. However, the username being assigned shouldn't affect the the concurrent issue. The dlookup is not conditional on the username being filled in, although it could be. It's based on the item being not selected and not completed yet, which is some of what is being recorded when the item is blocked off.

Ryan

 

[shadow9449]

Shadow,

I don't think there is someone logged in at two separate locations. I'm working in a corporate environment, each user has their own station and user id. I can investigate to see if this is occurring. However, the username being assigned shouldn't affect the the concurrent issue. The dlookup is not conditional on the username being filled in, although it could be. It's based on the item being not selected and not completed yet, which is some of what is being recorded when the item is blocked off.

Ryan

Ok, I was just guessing based on the little I saw. I saw that you were identifying by user name so I took a jab

Sorry I couldn't help more, sight unseen

SHADOW