[AccessD] Dubious value

MartyConnelly martyconnelly at shaw.ca
Wed Feb 15 14:55:52 CST 2006


Well I can get both error messages to occur

    * 3811 -- Attempt to open the datasbase with the requested record
      level locking failed; page level locking will be used
    * 3812 -- Attempt to open the datasbase with the requested page
      level locking failed; record level locking will be used

if I put this line after the open statement
 Debug.Print con.Errors(0).NativeError
If I get no error at all on this line, the line  itself will error with 
a 3265 error Item cannot be found in the collection

You have to also set Tools --> Options --> Advanced and select page 
locking by toggle locking all records
or full record locking  checkbox Open database for record locking.

Just remember once the mdb is opened with either page or record locing  
setting, it stays that way until everyone is out of the pool and
the mdb is closed.


John Colby wrote:

>I have not even figured out what the code does.  It appears to try and
>switch the database to a given mode, but then there is code that reads the
>mode but it doesn't reflect these changes.  Perhaps the read code reads the
>properties of the FE and this is the properties of the BE, though how they
>discover which BE is beyond me.  All rather confusing. 
>
>
>John W. Colby
>www.ColbyConsulting.com 
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
>Sent: Wednesday, February 15, 2006 1:44 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Dubious value
>
>I think what is happening is you are immediately switching into Exclusive
>mode as soon as you do any edit with a module open To prove it just open
>your mdb and then immediately Ctl-G and run your test routine from the
>immediate debug window.
>You might find it easier to debug by opening an external mdb file rather
>than using the current one I seem to remember in 2003 by doing a complete
>file save it may switch back to Shared mode.
>
>Also After and before the Open statement
>
>Try Debug.Print con.Mode
>For Shared open should be something like = 16 or
>
>= adModeShareDenyNone
>
>John Colby wrote:
>
>  
>
>>>From the following blog:
>>
>>http://blogs.msdn.com/michkap/archive/2005/10/19/482694.aspx
>>
>>There is the Jet 4.0 feature of record level locking that people love, 
>>so I will talk about that.
>>
>>Now the rule is that once the database is opened (by anybody), that is 
>>the mode of the datasbase until all user are out of it; this makes 
>>sense since locking schemes have to apply to all concurrent users to have
>>    
>>
>any meaning.
>  
>
>>So what this means is that the Access setting in the Tools|Options 
>>Advanced tab (the chekbox "Open databases using record-level locking") 
>>is a request, not a demand. If the database is already opened in the 
>>other mode, your open will not fail, and with the current design you 
>>are not even warned that your preference was not heeded since your request
>>    
>>
>could not be honored.
>  
>
>>But two additional warnings that you my find useful here exist:
>>
>>   * 3811 -- Attempt to open the datasbase with the requested record 
>>level locking failed; page level locking will be used
>>   * 3812 -- Attempt to open the datasbase with the requested page 
>>level locking failed; record level locking will be used
>>
>>What this means is that if in your database you run the following code 
>>after it is open:
>>
>>   Set con = New ADODB.Connection
>>   con.Provider = "Microsoft.JET.OLEDB.4.0"
>>   con.Properties("Data Source") = CurrentDb.Name
>>   con.Properties("Jet OLEDB:Database Locking Mode") = 1 ' use 0 for 
>>page level check
>>   con.CursorLocation = adUseServer
>>   con.Open
>>
>>and the database was not in the mode you asked for tht you will see one 
>>of those warnings in the NativeError property, right in 
>>con.Errors(0).NativeError! You will see one of the following values 
>>(depending on whether you set the locking mode to 0 or 1 in the code) 
>>if there is something in that collection (on 100% success it will be
>>    
>>
>empty):
>  
>
>>   * 249761507 -- Warning 3811 above (in hex it is 0x0ee30ee3, and 
>>0x0ee3 is indeed 3811)
>>   * 249827044 -- Warning 3812 above (in hex it is 0x0ee40ee4, and 
>>0x0ee4 is indeed 3812)
>>
>>So it is as simple as that. You can obviously combine this with an 
>>Application.GetOption("Use Row Level Locking") to see if the user 
>>running the code is the cause of the problem so you can kick them out or
>>    
>>
>whatever.
>  
>
>>This becomes a much cooler way to solve problems such as those 
>>documented in KB article 237333, which just tells people to change the 
>>setting in Access and does not help people to fix the problem of the 
>>fact that it is not always that user's settings which are causing the
>>    
>>
>problem....
>  
>
>>************************
>>
>>As always happens however... When I try and use that code, none of the 
>>above errors occur, rather I get a message:
>>
>>"the database has been placed in a state that prevents it from being 
>>opened or locked"
>>
>>I thought I would use this code to troubleshoot locking issues since I 
>>have several clients where locking is an issue.  Can anyone look at the 
>>code and comment on why I would not get the indicated error?
>>
>>I am (for now) just running the code inside of my framework, standalone.
>>
>>John W. Colby
>>www.ColbyConsulting.com
>>
>>
>> 
>>
>>    
>>
>
>--
>Marty Connelly
>Victoria, B.C.
>Canada
>
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada






More information about the AccessD mailing list