none
The database has been placed in a state by user 'Admin' on machine 'Somename' that prevents it from being opened or locked.

    Question

  • I have one FE that does not open correctly. Four identical computers running Access 2003 and Windows XP SP3. When the problem FE opens it connects to BE and begins initializing FE tables. It eventually hangs with the message that the database has been placed in a state.... All code prior to the point at which the failure occurs is by nature of UPDATEs to local tables. The point of failure is at first INSERT statement, when customer table is copied from BE to FE table. The same code runs properly on each of the other 3 FEs. So I believe there must be something different about the FE on the particular desktop where the problem occurs. I have looked for a straggling lock file (.ldb file), both on the local desktop and on the BE, but do not find a file in either the folder where the BE exists or where the troubled FE resides. Someone suggested that it might be the advanced setting in options for opening "with record-level locking". I have tried this both ways, and no change. The problem occurs every time this FE is opened and is not dependent on other users who might be connected to the BE, at least as far as I can tell. The failure occurs when the only FE active is the one with the problem. If anyone has a suggestion I'd be interested to learn about it.

    code below:

     Dim cnn As New ADODB.Connection
     Dim cmd As New ADODB.Command
     Dim strSQL As String
     cnn.ConnectionString = BEConnect 'declared in another module
     cnn.Open

    strSQL = "INSERT INTO tblLCustomers ( CustID, Cust, HmPhone, CustAddress, wkPhone, AltPhone, Map, CitySZ, BillToAddress, BTCSZ, CustType )" & _
        " IN '" & CurrentDb.Name & "' SELECT tblCustomers.CustomerID, tblCustomers.LName & ', ' & tblCustomers.FName AS Cust," & _
        " tblCustomers.HmPhonePrefix & tblCustomers.HmPhone AS HmPhone, tblCustomers.SvcAddress AS CustAddress," & _
        " tblCustomers.WkPhonePrefix & tblCustomers.WkPhone AS wkPhone, tblCustomers.AltPhonePrefix & tblCustomers.AltPhone AS AltPhone," & _
        " tblCustomers.MapCoordinates AS Map, tblCustomers.SvcCity & ', ' & tblCustomers.SvcState & ' ' & tblCustomers.SvcZip AS CitySZ," & _
        " tblCustomers.BillToAddress, tblCustomers.BillToCity & ', ' & tblCustomers.BillToState & ' ' & tblCustomers.BillToZip AS BTCSZ," & _
        " tblCustomers.CustType FROM tblCustomers ORDER BY tblCustomers.LName, tblCustomers.FName;"


    With cmd
            .ActiveConnection = cnn
            .CommandText = strSQL
            .Execute
    End With

    Thursday, May 26, 2011 10:48 PM

Answers

  • It sounds like Access thinks something in the database is open in design mode.  Since replacing the FE does not fix it it must be something outside the database -- i.e.  in the registry.

     

    • Marked as answer by AlwaysNtrbl Saturday, June 04, 2011 5:44 PM
    Thursday, June 02, 2011 10:12 PM

All replies

  • Some things you may want to try, if you haven't already, include copying one of the working FE databases to the machine that is not working and see if there is any change in behavior.  Also, you didn't say anything about user permissions.  I would want to make sure the user who is using the troubled FE has the appropriate permissons on the BE.  For example, you could login as a different user (preferrably a user who is using one of the working FEs) on the troubled FE machine and see if the issue persists.

    I am assuming machine "Somename" is the machine holding the BE, however, you may want to confirm this.

    David Lloyd
    Lemington Consulting
    http://LemingtonIT.com

     


    David Lloyd Lemington Consulting http://LemingtonIT.com
    Monday, May 30, 2011 2:15 AM
  • David,

    I doubt that "Somename" is a BE machine. I think it is the name of a problem FE machine. I faced this issue with the following conditions:

    user make some sort of an action query (for example, updates a table) and his FE hangs. He opens Task Manager and terminates an Access process. So, he is 'still in BE' and simultaneously he is still locking this table. Other users may face this error message trying to access this table, but they work fine with other tables (of course, I mean with other forms based on these tables).

    Another situation I met is exactly using ADO. While testing some ADO code on my local not splitted db I noticed:

    if we open a connection then have an unhandled error before closing it - we see either the same error message or smth like 'You have no exclusive rights for this database at the moment'. But I'm not sure about the last one, it may be related to disk/network issues.

    So, I think there should be deeper exploring of the circumstances.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Monday, May 30, 2011 6:24 AM
  • Thanks for considering this David. "Somename" was used for illustration purposes. The computername of the Front End (FE) local machine is the name that actually appears in the error. FE has been copied from a working desktop to the troubled desktop with no effect. User has admin rights on both FE and BE (for evaluation of problem). And in this case the message says the failure is because user "Admin" on FE machine has placed the database in a state where it cannot be opened or locked. The BE is not actually locked by this failure, as users on working FEs are able to access BE without difficulty. The FE looks for any failure to update tables at login and terminates if tables aren't updated.The user on troubled FE is simply unable to log in because the FE fails to update local tables during sign in.

    Monday, May 30, 2011 4:01 PM
  • Thanks for looking at this Andrey. You think it might be an unhandled error from someplace earlier in the code? A connection opened...an unhandled error...the connection closed...leading to the problem with "The Database has been put into a state by "...error? I will definitely look for this. Don't know why the other FEs don't also manifest the same error though.
    Monday, May 30, 2011 4:08 PM
  • Hi sir,

    Are you using Access Runtime or a full install of access or ms office?

    Replace the said FE, it might be corrupted. If the same problems occurs then there are some Office/ OS files involved here. Im assuming that the same FE is working on other machines.

    Download virtual BOX, Create VM and try it there using the same OS.

    thanks

    rene

    Monday, May 30, 2011 9:02 PM
  • Move the code into a Public Function.  Then put the call to the function at the begining of your initialization code.  If it works move the call lower down in the initialization code until it fails. 

    Also, I assume you left out the code that closes the connection and sets it to Nothing.

    Monday, May 30, 2011 9:14 PM
  • Since you have tried a new copy of the FE from a working machine, and since you have looked at the permissions issues both related to the System database and the Windows file permissions, this would lead me to believe that issue may not be Access related.  Some additional things I would look at include replacing the System.mdw file or whatever you are using for a system database (preferrably using a copy from a working machine, or create a new one depending on your circumstances).  I would turn off/disable any antivirus software.  We have found in the past that this can affect Access databases.  I would make sure you are running the latest service pack for Office 2003 on the troubled machine.

    I would also start shutting down any other software running on the machine.  You can do this from Task Manager or use MSCONFIG from the Run... prompt.  You haven't mentioned it so I will also suggest reinstalling Access 2003 or Office 2003 on the machine in question if none of the other solutions work and if you have not done so already.

    These types of issues can be difficult to pin down, so you will have try and eliminate possible causes.

    David Lloyd
    Lemington Consulting
    http://LemingtonIT.com

     


    David Lloyd Lemington Consulting http://LemingtonIT.com
    Tuesday, May 31, 2011 1:52 AM
  • Thanks for the suggestions David. I tried uninstalling/reinstalling Office 2003. Also made sure to install all the updates and service packs after reinstalling Office. The problem remains. Also tried turning off the anti-virus software. These do not seem to be related to the problem. I have taken this workstation out of service until I can afford the time it is going to cost me to nail this problem down.
    AlwaysNtrbl
    Thursday, June 02, 2011 1:31 PM
  • Thank you for considering this problem. The point at which the code fails appears in the example above. It is the first time an INSERT statement occurs during the startup of the FE. I have bypassed the particular statement above and next INSERT statement (which is the next code in startup sequence anyway) produces identical error. And 'YES' , in the example above I left out the lines that CLOSE the connection and set to NOTHING. There is a sequence of "UPDATE" s that occur prior to this and they flow along without a hitch. But on this particular FE the error occurs at the "INSERT". 3 other FE's running same code do not manifest any error. All FE's are operating on XP SP3, with OFFICE 2003 and latest updates.
    AlwaysNtrbl
    Thursday, June 02, 2011 1:50 PM
  • It sounds like Access thinks something in the database is open in design mode.  Since replacing the FE does not fix it it must be something outside the database -- i.e.  in the registry.

     

    • Marked as answer by AlwaysNtrbl Saturday, June 04, 2011 5:44 PM
    Thursday, June 02, 2011 10:12 PM
  • Another test would be to start the computer in Safe Mode with Networking Support (F8 during startup) and see if the problem persists.   This will allow you to test the software with a minimum amount of software and services running on the machine.  If it works in Safe Mode, this means there is another software program or driver causing the issue.

    You didn't say anything about what type of System database (.mdw ) you are using and if there are any special permissions.  The System database controls locking, etc., so it is certainly a possible cause.  Please detail more about the System database, and whether you have tried to replace it.  You can access the System database from the Tools menu, Security..., Workgroup Adminstrator.

     

    David Lloyd
    Lemington Consulting
    http://LemingtonIT.com

     


    David Lloyd Lemington Consulting http://LemingtonIT.com
    Friday, June 03, 2011 2:25 AM
  • If you manually delete all the linked tables and manually link them again, does the error still persist?
    Jeanette Cunningham
    Friday, June 03, 2011 3:33 AM
  • Tried this. BUt it did not affect the problem.
    AlwaysNtrbl
    Saturday, June 04, 2011 5:38 PM
  • I did check the system database, but this did not make a difference. I also tried starting the unit in SAFE mode. This also made no difference. I concluded, after all these approaches failed that about the only remaining possibility was a Windows registry problem. In the end I wiped the drive. Reinstalled Windows XP, Office and all the updates. This finally resolved the problem.


    AlwaysNtrbl
    Saturday, June 04, 2011 5:43 PM
  • I appreciate everyone's input on the problem. In the end the problem does appear to have been a registry error. The solution that finally resolved the problem was to reinstall XP and Office and all the updates.
    AlwaysNtrbl
    Saturday, June 04, 2011 5:46 PM