Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Manchester, Gtr Manchester, England
    Thanked 0 Times in 0 Posts

    Locking problems and startup code (2000/XP)

    I'm having issues with record locking again and have spent the last 1/2 searching and reading posts in this forum.

    I have some users in a remote office who are having intermittent problems with record locking. I am unable to check their machine setups so am relying on feedback from them to set the picture. As I could be waiting sometime for this, I'm hopefully not going to ask anything that requires more information (than I have at present) to answer.

    The first point is the record locking options (in Tools>Options>Advanced).
    Can someone confirm the option least likely to cause unwanted locking is as follows:

    Default open mode: Shared
    Default record locking: No locks
    Open database using record-level locking: True

    with the property 'Record Locks' is set to 'No Locks' for all forms.

    I can't confirm the users have not changed the settings (in Options) at this stage but I doubt it.

    The second concerns enforcing these settings.
    Would it be reasonable to use the VBA method SetOption to set these options, say when a splash screen/startup form loads?

    Lastly (and I apologise if this is a bit vague), this is what the users could be up to:

    One user (user1) is editing a record directly in a table (the table is linked to the BE database) which I'll call tbl1.
    Another user (user2) is using a form in the database (this form is bound and its record source is a query which includes information from tbl1. However any edits here are to the information in a related table, not tbl1.

    User2 may then decide to open the same table open and make changes.

    The users assure me they are not looking at the same record, so why is someone getting locking error messages.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts

    Re: Locking problems and startup code (2000/XP)

    Record locking problems are quite frustrating, especially when you try to troubleshoot them long distance. And if you go there they probably won't be able to duplicate the problem. First, your locking settings are what is recommended for nearly all situations. And if you have users tweaking the options then you have BIG problems. But your scenario does worry me - you imply that users are able to do editing directly in tables. We usually prevent anyone other than an "administrator" from doing that by locking down the database so they have to use forms to do editing. We also often try to present only one record at a time to minimize the probability of conflicts.

    It's also possible that some other situation is causing what looks like a record-locking problem. Are you getting verbatim error messages from them? If not, try to get them to record the error number and the text that goes with it. That will sometimes help you identify a problem. Also, do you know what SR of Access 2000 they are on? I believe there were some bugs that were fixed in Jet that might cause record locking errors.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Manchester, Gtr Manchester, England
    Thanked 0 Times in 0 Posts

    Re: Locking problems and startup code (2000/XP)

    I just needed reassurance about the locking settings.

    Unfortunately we cant prevent the users from accessing the tables at this point in the project. Its not something I'm happy about.

    As for the error messages and software setup - I'm still waiting for feedback. There have been no more complaints about locking though

    Thanks again.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Niagara Falls, New York, USA
    Thanked 0 Times in 0 Posts

    Re: Locking problems and startup code (2000/XP)


    <software setup - I'm still waiting for feedback>

    This is some code I use that I got from the forum

    The GetPref("StartupMenuBar") command allows me to set up and load a customer preference table with all the customers preferences/answers/settings/constants, this eliminates hard coding and different behavior based on the customer

    HTH John

    <pre>Option Compare Database
    Option Explicit

    Global gstrTemp As String

    ' The following example shows a procedure named SetStartupProperties that passes the
    ' name of the property to be set, its data type, and its desired setting. The general
    ' purpose procedure ChangeProperty attempts to set the startup property and,
    ' if the property isn't found, uses the CreateProperty method to append it to the
    ' Properties collection. This is necessary because these properties don't appear in the
    ' Properties collection until they've been set or changed at least once.

    Function SetStartupProperties()
    Const DB_Text As Long = 10
    Const DB_Boolean As Long = 1

    'MsgBox "gstrTemp: " & gstrTemp

    ' Find help on this by looking for "Set Options from Visual Basic"

    ' Tools->Options->View
    Application.SetOption "Show Status Bar", True
    Application.SetOption "ShowWindowsInTaskbar", True '// Access 2000

    ' Tools->Options->General
    Application.SetOption "Track Name AutoCorrect Info", False
    Application.SetOption "Perform Name AutoCorrect", False
    Application.SetOption "Log Name AutoCorrect Changes", False

    ' Tools->Options->Edit/Find
    Application.SetOption "Confirm Record Changes", True
    Application.SetOption "Confirm Document Deletions", True
    Application.SetOption "Confirm Action Queries", False

    ' Tools->Options->Advanced
    Application.SetOption "Default Open Mode for Databases", 0 ' Shared, -1 = Exclusive

    Application.SetOption "Default Record Locking", 2 ' Edited record only
    ' 0=No Locks, 1=All records
    Application.SetOption "OLE/DDE Timeout (Sec)", 30
    Application.SetOption "Refresh Interval (Sec)", 60
    Application.SetOption "Number of Update Retries", 2
    Application.SetOption "ODBC Refresh Interval (Sec)", 1500
    Application.SetOption "Update Retry Interval (Msec)", 250

    ' ? Application.SetOption "Built-In Toolbars Available", blnResult

    'Where blnResult is False if it is an MDB, and true if an MDE.

    If gstrTemp = "Development" Then
    ChangeProperty "AppTitle", DB_Text, "*** NBS Development Enviornment ***"
    ChangeProperty "AppIcon", DB_Text, " "

    ChangeProperty "StartupMenuBar", DB_Text, "(default)"

    ChangeProperty "StartupShortcutMenuBar", DB_Text, "(default)"

    ChangeProperty "StartupForm", DB_Text, "ezy_Session"
    ChangeProperty "StartupShowDBWindow", DB_Boolean, False
    ChangeProperty "StartupShowStatusBar", dbBoolean, True ' 10/23/03
    ChangeProperty "AllowBuiltinToolbars", DB_Boolean, True
    ChangeProperty "AllowToolbarChanges", DB_Boolean, True
    ChangeProperty "AllowShortcutMenus", DB_Boolean, True
    ChangeProperty "AllowFullMenus", DB_Boolean, True
    ChangeProperty "AllowBreakIntoCode", DB_Boolean, True
    ChangeProperty "AllowSpecialKeys", DB_Boolean, True
    'ChangeProperty "AllowBypassKey", DB_Boolean, True
    Else ' must be production

    ChangeProperty "AppTitle", DB_Text, GetPref("AppTitle")
    ChangeProperty "AppIcon", DB_Text, GetPref("AppIcon")

    'ChangeProperty "StartupMenuBar", DB_Text, "NBS Building Menu"
    ChangeProperty "StartupMenuBar", DB_Text, GetPref("StartupMenuBar")

    ChangeProperty "StartupShortcutMenuBar", DB_Text, GetPref("StartupShortcutMenuBar")

    ChangeProperty "StartupForm", DB_Text, GetPref("StartupForm")
    ChangeProperty "StartupShowDBWindow", DB_Boolean, GetPref("StartupShowDBWindow")
    ChangeProperty "StartupShowStatusBar", dbBoolean, GetPref("StartupShowStatusBar") ' 10/23/03
    ChangeProperty "AllowBuiltinToolbars", DB_Boolean, GetPref("AllowBuiltinToolbars")
    ChangeProperty "AllowToolbarChanges", DB_Boolean, GetPref("AllowToolbarChanges")
    ChangeProperty "AllowShortcutMenus", DB_Boolean, GetPref("AllowShortcutMenus")
    ChangeProperty "AllowFullMenus", DB_Boolean, GetPref("AllowFullMenus")
    ChangeProperty "AllowBreakIntoCode", DB_Boolean, GetPref("AllowBreakIntoCode")
    ChangeProperty "AllowSpecialKeys", DB_Boolean, GetPref("AllowSpecialKeys")
    'ChangeProperty "AllowBypassKey", DB_Boolean, False
    End If

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts