Molemanryan
New member
- Local time
- Today, 03:40
- Joined
- Jan 18, 2012
- Messages
- 4
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:
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
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:
Code:
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
Thanks!

Ryan