Cirtix and Access 2000

Posted on 2006-03-26
Last Modified: 2008-03-17
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?

Question by:iglink
    1 Comment
    LVL 39

    Accepted Solution

    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

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now