Locking problems and startup code (2000/XP)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

Darsha

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

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

Reply | Quote

Darsha

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

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote