Locked by user 'Admin' in single user mode 1

[garibal]

I have just moved to Access 2003. I am designing a database as a single user.
All the problems described below occur when I run code from a module.

Problem 1:
Opening the database by double clicking the file in Explorer works fine until I start my code. It stops on the ‘Set dbs = OpenDatabase(“DbName”)’ statement because it could not find the file. I can modify forms, reports, tables, no problem there.

Problems 2 to n:
Opening the file from Access gives me a great choice of different errors and warnings !

Run-time error ‘3734’
‘The database has been placed in a state by user ‘Admin’ on machine ‘XXXXXX’ that prevents it from being opened or locked.'

Which means I cannot run my code.
This one is generally cured by closing and reopening the database (not always).

So now I can modify and test the code. I try to save it.
Message:
‘Microsoft Office Access can’t save design changes or save to a new database object because another user has the file open. To save your design changes or to save to a new object, you must have exclusive access to the file.’

Which means I cannot save my work.

Or:
‘You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later.’

More help on this one:
‘When attempting to open a database object in Design view, you may encounter this error message. This occurs because Access requires an exclusive lock to the database for some object types since other users may attempt to use the objects. If you change the name of a table, or its definition of fields while someone has the table open, this will result in a serious error for the other user. Therefore, you must acquire sole access to the database while you attempt to update the object. When you release control of the object, other users will be allowed to use the database (release your exclusive lock).’

I am the sole user. So I reopen the database in exclusive mode. No problem modifying the design of objects. Back to running the code:

‘Run-time error ‘3045’:
Could not use ‘DbName’; file already in use’

Next paragraph on the same warning:

'It is recommended you implement source code control for development efforts by using the Microsoft Visual SourceSafe Add-in for Microsoft Office Access. As an alternative you could distribute local working copies of the database to each developer.'

I have installed the add-in, but it requires the full Visual SourceSafe package (in the $500 price range). Anyhow, programmatically, it is beyond my knowledge.



The occurring of the errors is erratic, there is no pattern. It looks like that at some stage, Access decides to add a new user and give him all the rights.
In ‘Tools/Security/Users and Groups Accounts’, there is only one user (Admin), ironically, the one who throws me out!


A few things I have tried

- Imported all the objects and modules to a blank database

- Split the database with the tables file in the same directory as the front end (eventually, the goal is to put the data on the network)

- Installed Access 2003 on another machine, ran it from there

- In ‘Tools/Security, created a workgroup ID file (.mdw) with the User-Level Security wizard, in the same directory and with the same name as the ‘.mdb’ file. Option default workgroup file. Desecured all the objects. Included ‘Full permissions’ group in the workgroup. Granted ALL the permissions to the Users group. It made things worse. However, after I deleted the ‘.mdw’ file and rebooted my machine, Access worked fine. OUFFF, until I quit and restarted. Back to square one.

- Started the database from the ‘Samples’ directory in \Program files\Microsoft Office. After all, the Northwind database is running smoothly (you have to be really desperate to try that sort of thing).

- Converted the database to Access 2000 format.


All that to no avail.
I have ran out of ideas (and of steam). I am afraid to loose several weeks of work. If someone had THE solution, he would deserve 4 stars, at least.

 

[ThomasLafferty]

Just a wild guess, but...

Opening the database by [!]double clicking[/!] the file in Explorer

If your folders are set to function as a web browser, is there a chance that you are opening more than one instance by double clicking instead of single clicking? As for users and permissions, until you establish security, the creator of the db is its Admin...

Hope this helps.

Tom

Born once die twice; born twice die once.

 

[Remou]

Are you using ADO?

 

[garibal]

From Remou

Are you using ADO?

No, I am using DAO. After your question, I am now looking into migrating to ADO.
What would be the advantage?

 

[Remou]

The reason I asked was that some of your errors seemed a little ADO-ish, but I was not sure. It may be best to post some of your code.

Some notes:
ADO vs. DAO
thread701-1171537

INFO: Issues Migrating from DAO/Jet to ADO/Jet

http://support.microsoft.com/kb/225048/

 

[garibal]

I have read a couple of threads, including the one you mention.
I do not think ADO is for me. My database is not big enough to make the move. I have to manage 1500 items at the most. And there will be a maximum of 5 users. (I am working on a seismic vessel).

Some code. In my latest attempt, I have created a Workgroup ID file with only one user (denis). Checked the SystemDB key in the registry.
I can run, modify and save my code after the .mdw file is created. When I close and restart, I receive

Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

Which looks basically the same as when I am locked by the default user 'Admin' without the security file.
I rerun the security wizard (option modify) without modifying anything, and I am back in business, until next restart.

Option Compare Database
Option Explicit


Sub Build_Streamer()
     
Dim wrkJet As Workspace
Dim dbsInWater As Database
     
    Dim HESESet As Recordset
    Dim TESSet As Recordset
    Dim LAUMSet As Recordset
    Dim ALSSet As Recordset
    Dim SingleSet As Recordset
    Dim SingleSN As String
    Dim BirdSet As Recordset
    Dim RetrSet As Recordset
    
    Dim ALSSn()
    Dim HESEPos, HESESn()
    Dim TESPos, TESSn()
    Dim LAUMPos(), LAUMSec(), LAUMSn()
    Dim j, n, LAUMn, ALSn, HESEn, TESn As Integer
    Dim SQL As String
    Dim BirdIndex, ind As Integer
    Dim BirdPos(), BirdSN(), BirdSec()
    Dim BSN, BNum, Birdn, i As Integer
    Dim Bird As Boolean
    Dim RetrPos(), RetrSN(), RetrSec()
    Dim RSN, RNum, Retrn As Integer
    
 [b]   Set wrkJet = CreateWorkspace("", "denis", "", dbUseJet)
    Set dbsInWater = wrkJet.OpenDatabase("InWater.mdb")
    [/b]
    
' Get HESEs number and Serial number
    
    SQL = "SELECT * FROM Main2 WHERE [Type]=('HESE') AND " & _
          "[Status]=('In streamer') ORDER BY [ItemPos]"
    Set HESESet = dbsInWater.OpenRecordset(SQL, dbOpenSnapshot)
    HESESet.MoveLast
    HESEn = HESESet.RecordCount
    ReDim HESESn(HESEn)
    ReDim HESEPos(HESEn)
    With HESESet
        .MoveFirst
        For i = 1 To HESEn
            HESESn(i) = !SN
            HESEPos(i) = !ItemPos
            .MoveNext
        Next
    End With
    HESESet.Close
    
    
' Get TESs number and Serial number
    
    SQL = "SELECT * FROM Main1 WHERE [Type]=('TES') AND " & _
          "[Status]=('In streamer') ORDER BY [ItemPos]"
    Set TESSet = dbsInWater.OpenRecordset(SQL, dbOpenSnapshot)

 

[Remou]

Are you opening the database twice? That is, does this code refer to the current database?
Set wrkJet = CreateWorkspace("", "denis", "", dbUseJet)
Set dbsInWater = wrkJet.OpenDatabase("InWater.mdb")

If so, try:
Set dbsInWater = CurrentDb

The end of this sub is not showing. It should contain code to close everything.

 

[garibal]

OHHH, me stupid. I forgot to close the database at the end of my modules. Days and days trying useless things, when it was so obvious.
I believe I have been a little too long away from Access.

Anyhow, many thanks and four stars as promised.

 

[Pizarro]

Hi Guys.. I ran into the same error and I found the cause to be an empty toolbar that I had setup in the prior version of MS Access. The toolbar was named Custom1 and was set up in the Tools\Startup as as a Menu Bar:Custom1, but not in the opening form; I did this so that the form would open up clean with no tool bars. I guess MS Access does not like empty toolbars so I added a Blank choice and voila. It works now.
Apparently, MS Access treats empty objects as is they were in design mode. Go figure ...The wacky world of Microsoft!