none
The database has been placed in a state by user ‘Admin’ on machine… that prevents it from being opened or locked RRS feed

  • Question

  • Hi,

    Thanks in advance for your reply.

    For the past two months, I have been struggling with an error that can fixed manually but for which I would like to find a more permanent solution. 

    Here is the thing. I have an Excel file that is connected to several queries from an Access database by using Power Query (both reside in a local server C:). The issue is that when I try to refresh connections with "Refresh all" the first one usually works but the rest don't. An error message appears with the text "The database has-been placed in a state by user 'Admin' on machine... that prevents it from being opened or locked".

    I understand that the first connection left the database open though I do not know how to close it and advance without having to close the Excel and start over with the next query. At time I even have to reboot the computer so that i can refresh the rest.

    I have read a couple of posts but with Power Query being so new wouldn't think the same solutions can be applied here. Specially when the created connection differs from the one you can do with Data /Connections ...

    Any help is greatly appreciated.

    Kind Regards,

    Leo

    Tuesday, August 18, 2015 12:56 AM

Answers

  • The problem is that Power Query data refresh is multi-threaded and it sounds like Access can't handle it. You will need to use a data source that can handle concurrent connections, like SQL Server.

    Make sure that Access is set to open in shared mode. This is the default setting, but you should check to be sure — if a user opens the database in exclusive mode, it will interfere with data availability. Complete the following procedure:

    1. Start Access and under File, click Options.

    2. In the Access Options box, click Client Settings.

    3. In the Advanced section, under Default open mode, select Shared, click OK, and exit Access.

    If that doesn't work and if these are tables, you could create "shadow" Access databases, one for each query and link the Access table that you are trying to retrieve. *Might* get you around this problem but no guarantee. Yes, it is ugly, but this is Access.

    Tuesday, August 18, 2015 11:35 AM

All replies

  • The problem is that Power Query data refresh is multi-threaded and it sounds like Access can't handle it. You will need to use a data source that can handle concurrent connections, like SQL Server.

    Make sure that Access is set to open in shared mode. This is the default setting, but you should check to be sure — if a user opens the database in exclusive mode, it will interfere with data availability. Complete the following procedure:

    1. Start Access and under File, click Options.

    2. In the Access Options box, click Client Settings.

    3. In the Advanced section, under Default open mode, select Shared, click OK, and exit Access.

    If that doesn't work and if these are tables, you could create "shadow" Access databases, one for each query and link the Access table that you are trying to retrieve. *Might* get you around this problem but no guarantee. Yes, it is ugly, but this is Access.

    Tuesday, August 18, 2015 11:35 AM
  • One additional caveat -- Access gets much less sticky about the timing on tables load if the directory is in the trusted locations list.
    Saturday, July 9, 2016 1:19 AM
  • It appears we have to lower our standards to Microsoft's level nowadays. One table or query per report. That's a new low for a reporting tool. Back to PowerPivot, I guess, or programming reports on Open Source languages...
    Wednesday, August 2, 2017 8:40 PM
  • It seems that you can fix this issue by buffering the Access binary. Use the Binary.Buffer function in a query that defines your Access database, then reference that query in order to use the binary in a query that pulls each table. Note: I also define parameters for my folder path and file names.


    For example:

    //myDbBinary
    let
        Source = Binary.Buffer(File.Contents(DataFolder_param & FileName_param), [CreateNavigationProperties=true]))
    in
        Source
    
    // Table1 Query
    let
        Source = Access.Database(myDbBinary, [CreateNavigationProperties=true]),
        _Table1 = Source{[Schema="",Item="Table1"]}[Data]
    in
        _Table1

    I got the idea from Chris Webb's blog: https://blog.crossjoin.co.uk/2015/05/05/improving-power-query-calculation-performance-with-list-buffer/

    • Proposed as answer by CR303 Friday, August 4, 2017 8:53 PM
    Friday, August 4, 2017 8:53 PM
  • was hoping to find an answer here. unfortunately i get a "Token Eof expected" error ! on the second let. 

    My code : 

    //myDbBinary
    let
        Source = Binary.Buffer(File.Contents("\\161.90.94.37\prod\TiReDat.accdb"), [CreateNavigationProperties=true])
    in
        Source
    //NessieHours query
    let
        Source = Access.Database(File.Contents("\\161.90.94.37\prod\TiReDat.accdb"), [CreateNavigationProperties=true]),
        _tblNessieHours = Source{[Schema="",Item="tblNessieHours"]}[Data],
        #"Renamed Columns" = Table.RenameColumns(_tblNessieHours,{{"dblHours", "Hours"}})
    in
        #"Renamed Columns"


    • Edited by mnijskens Thursday, November 16, 2017 1:26 PM
    • Proposed as answer by Vedette34 Monday, January 28, 2019 1:04 PM
    Monday, November 13, 2017 1:24 PM
  • I think that the second "let" statement is the issue.  The following worked for me:

    let
        MyDbBinary = Binary.Buffer(File.Contents(DataFolder_param & FileName_param)),
        Source = Access.Database(myDbBinary, [CreateNavigationProperties=true]),
        _Table1 = Source{[Schema="",Item="Table1"]}[Data]
    in
        _Table1



    • Edited by MHerzog01 Friday, September 27, 2019 4:54 PM Syntax error
    Friday, September 27, 2019 4:54 PM