Cirtix and Access 2000

I have an Access 2000 mde which shells out a vb application.  It has active X and OCX components required.  Its divided into front end mde and back end mdb.

I have a client that wants to install it on a Citrix environment with 60 users all connecting to the same back end  Access database.

It is unlikely that there would be more than a dozen accessing at any one time and the sites would have their own filtered data so its even more unlikely that there would be any two sites trying to change a data record at any one time

Is this likely to work and are there any pit falls?

Are there likely to be record locking issues?

Any suggestions to install?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A dozen uses on Citrix/TS should not be a problem as long as each user is not sharing the front end.  You might want to consider upsizing to SQL Server express (the "free" version of SQL server) for better performance and sharing.

As far as record locking:
One of the biggest concerns in a multiuser environment is what happens if two or more people try to change the same information at the same time.  The way that this is handled is through locking.  If you look up "lock" in Access and VBA help, you will see record locking, all record locking, page locking, optimistic locking, pessimistic locking.  99% of the time you want pessimistic record locking (editied record) or optimistic record locking (no locks).  Then the question becomes optimistic or pessimistic locking.

The optimist says "It's unlikely that two people will be in the same record at the same time."  The pessimist says "Yah, right!  And most likely, it'll be my boss that's in the same record as me!!"  I our medical office, we have over 2000 patients in the database so you might think it is pretty unlikely that two people would be changing the same patient at the same time.  Until you realize that we are only seeing ten of those 2000+ on any given day.  Until I switched to pessimistic locking for the patient records, we were getting a lot of write conflicts.  I use optimistic locking for the rest of my db.

If you use optimistic locking, you can reduce the risk of write conflicts by breaking your forms up into subforms or tabs.  Records are saved when you move to a subform or another tab.  You can also put in "Me.Dirty = False" in after update events for some or all controls to save the record.

If you don't want the second person to be able to save changes to a record when someone else has not finished saving then that is called pessimistic locking.  The code below (thanks to Jim Horn if my memory serves) will notify the second person that the record is in use and by whom.  The biggest downside of pessimistic locking is the famous employee went to lunch without saving or logging off.  The computer goes to screen saver, locks and no one but the missing employee can save the record.  This can be resolved by 1: automatic logoff (of database or windows) after timed inactivity; 2: automatic save by setting the timer interval with the form's on dirty event and "Me.Dirty = False" in the timer; and 3: a reminder to save the record.

Function IsRecordBusy( _
    rs As Recordset, _
    Optional UserName As String, _
    Optional MachineName As String, _
    Optional CreateMsg As Boolean = True) As Boolean
      ' Accepts: a recordset and two string variables
      ' Purpose: determines if the current record in the recordset is locked,
      '          and if so who has it locked.
      ' Returns: True if current record is locked (and sets UserName
      '          and MachineName to the user with the lock).  False if the
      '          record isn't locked.
      ' From: Building Applications Chapter 12
          Dim ErrorString As String
          Dim MachineNameStart As Integer
10       On Error GoTo IsRecordBusy_Error

20        IsRecordBusy = False
40        rs.Edit                     'Try to edit the current record in the recordset.
50        rs.MoveNext
60        rs.MovePrevious
70        Exit Function               'No error, so return False.

80       On Error GoTo 0
90       Exit Function

100       If Err = 3260 Or Err = 3197 Or Err = 3188 Then   'Record is locked -- parse error string.
110           If CreateMsg Then
120               ErrorString = Error$
130               UserName = Mid$(ErrorString, 45, InStr(45, ErrorString, "'") - 45)
140               If UserName = "" Then UserName = "(unknown)"
150               MachineNameStart = InStr(43, ErrorString, " on machine ") + 13
160               MachineName = Mid$(ErrorString, MachineNameStart, Len(ErrorString) - MachineNameStart - 1)
170               If MachineName = "" Or MachineNameStart = 0 Then MachineName = "(unknown)"
180               MsgBox "This record is being used by " & UserName & " on station " _
                      & MachineName, vbExclamation, "Record Lock Warning"
190           End If
200       Else
210           MsgBox "Error: " & Err.Number & ", " & Err.Description & ", IsRecordBusy, " & Erl
220       End If

   On Error GoTo 0
End Function

Call the function with:
    Set rstForm = Me.RecordsetClone
    strBM = Me.Bookmark
    Set rstFormClone = rstForm.Clone()
    rstFormClone.Bookmark = strBM
    If IsRecordBusy(rstFormClone) Then
          Cancel = True
          Exit Sub
    End If

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.