We use Access XP, a split back end and never open the front end mdb 
exclusively.  The front end seldom goes above 12MB.  We do, however, get 
the ~DB Locked/Opened Exclusive by other user~ message.  We do 
compact/repair often, which reduces the sizes to 6MB - but it does not 
seem to fix the problem.  We have found that recreating the mdb and 
importing all objects does fix the problem (reduces size to 4MB).  We 
recently started using SourceSafe, but the problem existed before SS.

Anyone had experience with this problem?

Bob Orta
Arizona Health Sciences Center
1501 N. Campbell Ave.
P.O. Box 245018
Room 2348
Tucson, AZ  85724-5018

I had the same problem before except it was in A2K. The only thing I found 
that corrected the problem is the following function:

Public Function SetToShared()
    
    Dim varOpenMode As Variant

    varOpenMode = Application.GetOption("Default Open Mode for Databases")

    Select Case varOpenMode
        Case 0
'            If MsgBox("Default Open Mode is currently set to Shared. " & _
'                "Do you want to change this setting to Exclusive?", 
vbYesNo, _
'                    "Default Open Mode Option Setting") = vbYes Then
'                varOpenMode = 1
'            End If
        Case 1
'            If MsgBox("Default Open Mode is currently set to Exclusive." 
& _
'                "Do you want to change this setting to Shared?", vbYesNo, 
_
'                    "Default Open Mode Option Setting") = vbYes Then
                varOpenMode = 0
'            End If
    End Select


    Application.SetOption "Default Open Mode for Databases", varOpenMode
    
End Function

Just add a call to it in your autoexec macro and it should solve your 
problem.

Hope this helps...

Kenny

Kenny,

I tried your function, verified the mode was set to 0, but others are 
still prevented from opening the mdb.

Bob



> I had the same problem before except it was in A2K. The only thing I 
found 
t> hat corrected the problem is the following function:

> Public Function SetToShared()
 >    
 >    Dim varOpenMode As Variant

>     varOpenMode = Application.GetOption("Default Open Mode for 
Databases")

>     Select Case varOpenMode
 >        Case 0
'>             If MsgBox("Default Open Mode is currently set to Shared. " 
& _
'>                 "Do you want to change this setting to Exclusive?", 
v> bYesNo, _
'>                     "Default Open Mode Option Setting") = vbYes Then
'>                 varOpenMode = 1
'>             End If
 >        Case 1
'>             If MsgBox("Default Open Mode is currently set to 
Exclusive." 
&>  _
'>                 "Do you want to change this setting to Shared?", 
vbYesNo, 
_> 
'>                     "Default Open Mode Option Setting") = vbYes Then
 >                varOpenMode = 0
'>             End If
 >    End Select

> 
 >    Application.SetOption "Default Open Mode for Databases", varOpenMode
 >    
E> nd Function

> Just add a call to it in your autoexec macro and it should solve your 
p> roblem.

> Hope this helps...

> Kenny

Worth a try...

Sub OpenDBShared(strDBPath As String)

    ' This procedure attempts to open the specified database
    ' in shared mode.

    Dim cnnDB      As ADODB.Connection
    Dim errCurrent As ADODB.Error

    ' Initialize Connection object.
    Set cnnDB = New ADODB.Connection

    ' Specify a Microsoft Jet 4.0 provider and then
    ' try to open the database specified in the
    ' strDBPath variable in shared mode.
    On Error Resume Next
    With cnnDB
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Mode = adModeShareDenyNone
        .Open strDBPath
    End With

    If Err <> 0 Then
        ' If errors occur, display them.
        For Each errCurrent In cnnDB.Errors
            Debug.Print "Error " & errCurrent.SQLState _
                & ": " & errCurrent.Description
        Next
    Else
        ' No errors: You have shared access.
        Debug.Print "The database is open in shared mode."
    End If
 
    ' Close Connection object and destroy object variable.
    cnnDB.Close
    Set cnnDB = Nothing
End Sub

> Kenny,

> I tried your function, verified the mode was set to 0, but others are 
s> till prevented from opening the mdb.

> Bob

> 

> > I had the same problem before except it was in A2K. The only thing I 
f> ound 
t> > hat corrected the problem is the following function:

> > Public Function SetToShared()
 > >    
 > >    Dim varOpenMode As Variant

> >     varOpenMode = Application.GetOption("Default Open Mode for 
D> atabases")

> >     Select Case varOpenMode
 > >        Case 0
'> >             If MsgBox("Default Open Mode is currently set to 
Shared. " 
&>  _
'> >                 "Do you want to change this setting to Exclusive?", 
v> > bYesNo, _
'> >                     "Default Open Mode Option Setting") = vbYes Then
'> >                 varOpenMode = 1
'> >             End If
 > >        Case 1
'> >             If MsgBox("Default Open Mode is currently set to 
E> xclusive." 
&> >  _
'> >                 "Do you want to change this setting to Shared?", 
v> bYesNo, 
_> > 
'> >                     "Default Open Mode Option Setting") = vbYes Then
 > >                varOpenMode = 0
'> >             End If
 > >    End Select

> > 
 > >    Application.SetOption "Default Open Mode for Databases", 
varOpenMode
 > >    
E> > nd Function

> > Just add a call to it in your autoexec macro and it should solve your 
p> > roblem.

> > Hope this helps...

> > Kenny

Well there was another issue similar to yours that I overcome as well. I had a data entry form that was
locking the main data table restricting access. After trying everything (including the function) I had to branch the data entry off
into it's own DB which resolved the problem. Don't know if that is an issue with you but if so maybe you can try it.

Kenny

----- Original Message -----
From: Bob Orta
Sent: Thursday, December 12, 2002 2:22 PM
To: Access
Subject: [access] Re: Share Problem

Kenny,

I tried your function, verified the mode was set to 0, but others are  
still prevented from opening the mdb.

Bob



> I had the same problem before except it was in A2K. The only thing I  
found  
t> hat corrected the problem is the following function:

> Public Function SetToShared()
>     
>    Dim varOpenMode As Variant

>     varOpenMode = Application.GetOption("Default Open Mode for  
Databases")

>     Select Case varOpenMode
>        Case 0
'>             If MsgBox("Default Open Mode is currently set to Shared. "  
& _
'>                 "Do you want to change this setting to Exclusive?",  
v> bYesNo, _
'>                     "Default Open Mode Option Setting") = vbYes Then
'>                 varOpenMode = 1
'>             End If
>        Case 1
'>             If MsgBox("Default Open Mode is currently set to  
Exclusive."  
&>  _
'>                 "Do you want to change this setting to Shared?",  
vbYesNo,  
_>  
'>                     "Default Open Mode Option Setting") = vbYes Then
>                varOpenMode = 0
'>             End If
>    End Select

>  
>    Application.SetOption "Default Open Mode for Databases", varOpenMode
>     
E> nd Function

> Just add a call to it in your autoexec macro and it should solve your  
p> roblem.

> Hope this helps...

> Kenny
to unsubscribe send a blank email to Get more from the Web.  FREE MSN Explorer download : http://explorer.msn.com

Your problem may be that one of the servers has a system.mdw file not set 
as read-only, which means only the first user of that file can open the 
database. Users who are accessing the database using other system.mdw can 
probably get in if their system.mdw file is set as READ ONLY.

Also, if you are sharing front ends on the server, opportunistic locking 
must be turned off at the server level. You can Update the server's 
registry:

HKEY_LOCAL_MACHINE/System/CurrentControlSet/Services/LanmanServer/Parameter
s
Under this key, create a new DWORD value named EnableOplocks and set it's 
value to 0. Reboot Srvr.At the server, opportunistic locking must be 
turned off. Update the registry;
HKEY_LOCAL_MACHINE/System/CurrentControlSet/Services/LanmanServer/Parameter
s
Under this key, create a new DWORD value named EnableOplocks and set it's 
value to 0. Reboot Srvr.



> We use Access XP, a split back end and never open the front end mdb 
e> xclusively.  The front end seldom goes above 12MB.  We do, however, get 
t> he ~DB Locked/Opened Exclusive by other user~ message.  We do 
c> ompact/repair often, which reduces the sizes to 6MB - but it does not 
s> eem to fix the problem.  We have found that recreating the mdb and 
i> mporting all objects does fix the problem (reduces size to 4MB).  We 
r> ecently started using SourceSafe, but the problem existed before SS.

> Anyone had experience with this problem?

> Bob Orta
A> rizona Health Sciences Center
1> 501 N. Campbell Ave.
P> .O. Box 245018
R> oom 2348
T> ucson, AZ  85724-5018