Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2009
    Posts
    8

    Unanswered: ODBC Microsoft Access Login Failed

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

    I have 20 pcs each running a different copy of an excel sheet that does a Microsoft Query refresh once every 15 minutes. They do this 7 days per week and 24 hours per day. Occasionaly we would receive this error and have to manualy click "Cancel" or "OK". If you select OK it then does an ODBC login.

    Recently this is failing much more often.

    We are using office 2003, in Windows XP. The access database is located on a network server. There is no pattern to the error. It happens all times of the day and night.

    Is this because two pcs are performing an ODBC login at the same time? Can I solve the error by changing the way they log in? For example: Is it possible to change them all to read only? Would this let multiple pcs in at the same time without conflicting with each other.

    None of them need to write to the Access database.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Some pieces of information could help here, such as:

    -full version of the O.S. (Service Packs) on the server and on the clients
    -full version of Office 2003 (Service Pack)
    -network protocol and environment (AD, Workgroup...)
    -version of MDAC involved in the ODBC link
    -configuration of the ODBC link and its type (User, System or File)
    -full contents of the error message (if any)
    -SQL text of the query
    -method used to schedule the execution of the query
    ... and probably a few more.

    Have a nice day!

  3. #3
    Join Date
    Apr 2009
    Posts
    8
    Clients are using Windows XP Service pack 3 5.1.2600

    The clients are Access 2003 SP3, Excel Access 2003 SP3 so I'm assuming Office is SP3

    SQL Text of Query: SELECT WorkingFile.AssignedPools, WorkingFile.PreformNum, WorkingFile.CurrentPool, WorkingFile.TotalPreform, WorkingFile.QtyNeededForGreen, WorkingFile.MandrelNum, WorkingFile.CuredHose, WorkingFile.NextRequirement, WorkingFile.AddRemoveSlides, WorkingFile.FinishedGoods, WorkingFile.FinishingRed, WorkingFile.FinishingYellow, WorkingFile.FinishingGreen, WorkingFile.StandardPack, WorkingFile.CuredHoseBuffer, WorkingFile.DateTimeKbLastUpdated, WorkingFile.SlidesForPastAndDay2, WorkingFile.CurrentlSlidesOn, WorkingFile.FormingSortOrder, WorkingFile.TotalCured, WorkingFile.FgOnHand, WorkingFile.QtyPer, WorkingFile.MandrelSlots, WorkingFile.MandrelsPerBar
    FROM `L:\KanBan\Pull Boards\Kanban`.WorkingFile WorkingFile
    WHERE (WorkingFile.AssignedPools='PL13') AND (WorkingFile.CurrentPool In ('11','13','NR')) AND (WorkingFile.AddRemoveSlides>'0') AND (WorkingFile.CurrentlSlidesOn='0') AND (WorkingFile.TotalPreform>'0') OR (WorkingFile.AssignedPools='PL13') AND (WorkingFile.CurrentPool In ('11','13','NR')) AND (WorkingFile.AddRemoveSlides='0') AND (WorkingFile.CurrentlSlidesOn>'0') AND (WorkingFile.TotalPreform>'0') OR (WorkingFile.AssignedPools='PL13') AND (WorkingFile.CurrentPool In ('11','13','NR')) AND (WorkingFile.AddRemoveSlides>'0') AND (WorkingFile.CurrentlSlidesOn>'0') AND (WorkingFile.TotalPreform>'0') OR (WorkingFile.AssignedPools='PL13') AND (WorkingFile.CurrentPool In ('11','13')) AND (WorkingFile.TotalPreform<='0') OR (WorkingFile.AssignedPools<>'PL13') AND (WorkingFile.CurrentPool In ('11','13')) AND (WorkingFile.TotalPreform<='0') OR (WorkingFile.AssignedPools<>'PL13') AND (WorkingFile.CurrentPool In ('11','13')) AND (WorkingFile.AddRemoveSlides>='0') AND (WorkingFile.CurrentlSlidesOn>'0') AND (WorkingFile.TotalPreform>'0')
    ORDER BY WorkingFile.FormingSortOrder

    How do I determine the configuration of the ODBC link?

    I don't have an Network administrator to get the network info right now. I'm assuming I need then for the following questions:

    -full version of the O.S. (Service Packs) on the server and on the clients

    -network protocol and environment (AD, Workgroup...)
    I believe these are all in a workgroup for secutity reasons


    Error Message: ODBC Microsoft Access Login Failed The database has been placed in a state by user 'admin' on machine "BHD020" that prevents it from being opened or locked.

    I'm using a program called Automation Anywhere to call the following Excel Macro:

    Sub Refresh_Data()
    '
    ' Refresh_Data Macro
    ' Macro recorded 7/11/2008 by Keith A Reinke
    '

    '
    With Application
    .Calculation = xlAutomatic
    .MaxChange = 0.001
    End With
    Sheets("Messages").Select
    Range("A3").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Data").Select
    Range("C4").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Calculate
    Sheets("Pull Board").Select
    Range("A2:B3").Select
    End Sub

    Does this revial anything while I'm trying to get the Network info?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Kareinke,

    Thank you for your answers.

    I was asking about O.S. and Office version because MDAC (Microsoft Data Access Components - used in ODBC) are embedded in the operating system and are sometimes updated when you update the O.S. and sometimes when you load a service pack for an application such as Office or .NET. In the case you describe everything seems to be patched to the top so the problem should not come from there (some versions of MDAC caused problems in the past).

    If "BHD020" is a client computer that uses the query, does it have a different configuration than the other computers? Could you verify that it is not opening the database in exclusive mode (which seems to be the case according the the error message). The configuration of the ODBC link could help here.

    Also is it always the same machine that causes this error? Is it running on a special account?

    Could you compare the ODBC link on this machine with the same ODBC link on another machine that does not cause the error?

    To access to the ODBC manager open the control pannel, select Administration Tools, Data Sources (ODBC).

    When the problem occurs, how is it solved? Does someone has to manually do something or does the problem just "go away" by itself?

    Can you find any trace of the problem in the Log Event files of the clients or of the server? Is your server a "true" server or just another computer running Windows XP? (there is a maximum number of connections that Windows XP can accept simultaneously).

    Sorry if I come back with more questions than answers but there could be many reasons that could explain what happens and the fact that it does not happen systematically does not help either.

    I'll try to think a little more to the problem and come back to you if I can figure out something helpful.

    Have a nice day!

  5. #5
    Join Date
    Apr 2009
    Posts
    8
    Clients are using Windows XP Service pack 3 5.1.2600

    The clients are Access 2003 SP3, Excel Access 2003 SP3 so I'm assuming Office is SP3

    SQL Text of Query: SELECT WorkingFile.AssignedPools, WorkingFile.PreformNum, WorkingFile.CurrentPool, WorkingFile.TotalPreform, WorkingFile.QtyNeededForGreen, WorkingFile.MandrelNum, WorkingFile.CuredHose, WorkingFile.NextRequirement, WorkingFile.AddRemoveSlides, WorkingFile.FinishedGoods, WorkingFile.FinishingRed, WorkingFile.FinishingYellow, WorkingFile.FinishingGreen, WorkingFile.StandardPack, WorkingFile.CuredHoseBuffer, WorkingFile.DateTimeKbLastUpdated, WorkingFile.SlidesForPastAndDay2, WorkingFile.CurrentlSlidesOn, WorkingFile.FormingSortOrder, WorkingFile.TotalCured, WorkingFile.FgOnHand, WorkingFile.QtyPer, WorkingFile.MandrelSlots, WorkingFile.MandrelsPerBar
    FROM `L:\KanBan\Pull Boards\Kanban`.WorkingFile WorkingFile
    WHERE (WorkingFile.AssignedPools='PL13') AND (WorkingFile.CurrentPool In ('11','13','NR')) AND (WorkingFile.AddRemoveSlides>'0') AND (WorkingFile.CurrentlSlidesOn='0') AND (WorkingFile.TotalPreform>'0') OR (WorkingFile.AssignedPools='PL13') AND (WorkingFile.CurrentPool In ('11','13','NR')) AND (WorkingFile.AddRemoveSlides='0') AND (WorkingFile.CurrentlSlidesOn>'0') AND (WorkingFile.TotalPreform>'0') OR (WorkingFile.AssignedPools='PL13') AND (WorkingFile.CurrentPool In ('11','13','NR')) AND (WorkingFile.AddRemoveSlides>'0') AND (WorkingFile.CurrentlSlidesOn>'0') AND (WorkingFile.TotalPreform>'0') OR (WorkingFile.AssignedPools='PL13') AND (WorkingFile.CurrentPool In ('11','13')) AND (WorkingFile.TotalPreform<='0') OR (WorkingFile.AssignedPools<>'PL13') AND (WorkingFile.CurrentPool In ('11','13')) AND (WorkingFile.TotalPreform<='0') OR (WorkingFile.AssignedPools<>'PL13') AND (WorkingFile.CurrentPool In ('11','13')) AND (WorkingFile.AddRemoveSlides>='0') AND (WorkingFile.CurrentlSlidesOn>'0') AND (WorkingFile.TotalPreform>'0')
    ORDER BY WorkingFile.FormingSortOrder

    How do I determine the configuration of the ODBC link?

    I don't have an Network administrator to get the network info right now. I'm assuming I need then for the following questions:

    -full version of the O.S. (Service Packs) on the server and on the clients

    -network protocol and environment (AD, Workgroup...)
    I believe these are all in a workgroup for secutity reasons


    Error Message: ODBC Microsoft Access Login Failed The database has been placed in a state by user 'admin' on machine "BHD020" that prevents it from being opened or locked.

    I'm using a program called Automation Anywhere to call the following Excel Macro:

    Sub Refresh_Data()
    '
    ' Refresh_Data Macro
    ' Macro recorded 7/11/2008 by Keith A Reinke
    '

    '
    With Application
    .Calculation = xlAutomatic
    .MaxChange = 0.001
    End With
    Sheets("Messages").Select
    Range("A3").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Data").Select
    Range("C4").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Calculate
    Sheets("Pull Board").Select
    Range("A2:B3").Select
    End Sub

    Does this revial anything while I'm trying to get the Network info?

  6. #6
    Join Date
    Apr 2009
    Posts
    8
    "BHD020" is a specific client. However, it's always a different one.

    "Could you verify that it is not opening the database in exclusive mode"

    I realy think this is the issue. But I don't understand what the options are or how to control this.

    How can I configure the clients or even better the query so that it allow multiple logins? Is this even possible?

    Since they aren't writing, it shouldn't hurt anything.

    I do have a client which does update this file so it might need to be different.

  7. #7
    Join Date
    Apr 2009
    Posts
    8
    The problem doesn't go away until you answer the prompt. You can cancel or select "OK". If you select "OK", you get a second window which defaults with a login of "admin". You have to either cancel this one or select "OK" again.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    "The database has been placed in a state by user 'admin' on machine 'BHD020' that prevents it from being opened or locked."

    Here's how I resolved this issue (permanently!) - http://www.dbforums.com/6274786-post19.html

    It's the mdb file itself which is causing this error. This error can happen if someone opens the mdb file and then leaves it sitting open without any activity for a few hours. To resolve it, either put timer events in your forms which closes/exits the mdb or "clone" a "source" mdb file (with a vb script as in the link above) so no one is in the same mdb file. You can then have hundreds of users, each in their own mdb file which the vb script automatically creates. I gaurantee if you use the vb script, you'll never get this error (unless someone doesn't use the script and opens the source mdb file.)
    Last edited by pkstormy; 04-24-09 at 21:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Simple and brilliant!

    Have a nice day!

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Cheers


    Thanks.
    Last edited by pkstormy; 04-24-09 at 21:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Apr 2009
    Posts
    8
    This sounds like the right idea to me. This is exactly what I think is
    happening but didn't know how to handle it.

    I have one PC that actualy updates the database. I write a file called "Access On Hold.xls" to the network and before refreshing the Microsoft Query I check for this file. When the various clients see this file they wait.

    Meanwhile the Master PC clears all the records in the access file then copies a worksheet from Excel (loaded with math and additional queries to pull info from our business systems) into access. Erases the "Access On Hold.xls" file then closes the table involved.

    After I introduced the "Close" of the table, the Master PC stopped showing up in these errors. So your suggestion makes a lot of sense to me.

    So, how can I do the same thing with a microsoft query? Or is the VB the only way?

  12. #12
    Join Date
    Apr 2009
    Posts
    8
    This sounds like the right idea to me. This is exactly what I think is
    happening but didn't know how to handle it.

    I have one PC that actualy updates the database. I write a file called "Access On Hold.xls" to the network and before refreshing the Microsoft Query I check for this file. When the various clients see this file they wait.

    Meanwhile the Master PC clears all the records in the access file then copies a worksheet from Excel (loaded with math and additional queries to pull info from our business systems) into access. Erases the "Access On Hold.xls" file then closes the table involved.

    After I introduced the "Close" of the table, the Master PC stopped showing up in these errors. So you suggestion makes a lot of sense to me.

    So, how can I do the same thing with a microsoft query? Or is the VB the only way?

  13. #13
    Join Date
    Apr 2009
    Posts
    8
    This sounds like the right idea to me. This is exactly what I think is
    happening but didn't know how to handle it.

    I have one PC that actualy updates the database. I write a file called "Access On Hold.xls" to the network and before refreshing the Microsoft Query I check for this file. When the various clients see this file they wait.

    Meanwhile the Master PC clears all the records in the access file then copies a worksheet from Excel (loaded with math and additional queries to pull info from our business systems) into access. Erases the "Access On Hold.xls" file then closes the table involved.

    After I introduced the "Close" of the table, the Master PC stopped showing up in these errors. So your suggestion makes a lot of sense to me.

    So, how can I do the same thing with a microsoft query? Or is the VB the only way?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •