MS Access refresh woes: I'm being "placed in a state"!!!

[dolss1]

Getting DataFormat.Error: The database has been placed in a state by user 'Admin' on machine 'xxxxx' that prevents it from being opened or locked.
Details:
   myawesome.accdb

Happens whenever I try to refresh the query to an Access database, which is closed. Can't find anything in trust settings etc..thought maybe record locking issues. Anyone have a solve or workaround?

[Anonymous]

Hi, I've been looking for a solution too. The online refresh was working before and can connect to MS Access. I have changed the privacy, etc. Check if it's the last refresh time, check the DB access permissions. I got mine solved by playing with the datasource settings after a long grueling research. At first it was just set with "Basic", then I clicked "Advanced". I think that this started the issue. When you click "Advanced", it automatically add part that I didn't notice before. Then I played with the Add part button and it added another blank textbox under File path parts. What I did is delete all the blank file path parts from the elipses button beside these textboxes and jus retained the first one. Hit OK, save, and publish again. You may refresh before hitting save and publish. Then tested the online refresh and voila, it completed the refresh.

Hope somebody will find this solution helpful.

Happy Power BI reporting!

[CR303]

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-buff...

[JaridMcKenzie]

Hey All,

I've tried the solution as displayed on Chris's blog. The buffer query is relatively good, but I needed to make some changes to get it to work.

\\myDbBinary
let
    Source = Binary.Buffer(File.Contents("FilePathGoesHere"))
in
    Source

then you can reference any table like this:

let
    Source = Access.Database(myDbBinary, CreateNavigationProperties=true]),
    _TableName= Source{[Schema="",Item="TableName"]}[Data]
in
    _TableName

Hope That Helps!

[bdd9]

I keep having this error as well...

how would I enter your solution code into my code? here is my code:

let
Source = Table.Combine({FScaleAndYardDataEntry, FGrowerStackInventory}),
#"Removed Columns" = Table.RemoveColumns(Source,{"OriginalLot", "CompareLots", "TLocations(LocationDestination)", "TLocations(LocationOrigin)", "TMovementType", "TScale_Row_Type", "TGrower_Stack_Row_Type", "TLots"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"**Need_LocationOrigin",Replacer.ReplaceValue,{"LocationOrigin"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"**Need_LocationDestination",Replacer.ReplaceValue,{"LocationDestination"}),
#"Reordered Columns" = Table.ReorderColumns(#"Replaced Value1",{"ID", "MovementType", "RowType", "Lot", "Date", "Time", "LocationOrigin", "LocationDestination", "Cordinates", "BaleCount", "BalesRejected", "NetBales", "Gross (LB)", "Tare(LB)", "Net(LB)", "ST", "MT", "Field", "Scale Ticket", "Pictures", "Truck", "Comments", "HaulerID", "Truck#", "Crossed_DTH_Scale", "DriverName", "In/Out?", "BOL#", "DataSource", "DateEntered"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"ID", "MovementType", "RowType", "Lot", "Date", "Time", "Cordinates", "BaleCount", "BalesRejected", "NetBales", "Gross (LB)", "Tare(LB)", "Net(LB)", "ST", "MT", "Field", "Scale Ticket", "Pictures", "Truck", "Comments", "HaulerID", "Truck#", "Crossed_DTH_Scale", "DriverName", "In/Out?", "BOL#", "DataSource", "DateEntered"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Origin_Or_Destination"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Origin_Or_Destination", "Origin_Or_Destination - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Origin_Or_Destination - Copy", "In/Out"}}),
#"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns1","LocationOrigin","Out",Replacer.ReplaceText,{"In/Out"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","LocationDestination","In",Replacer.ReplaceText,{"In/Out"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value3", "Net_LBS_PowerQuery", each if[#"In/Out"] = "In" then[#"Net(LB)"] else if[#"In/Out"] = "Out" then -[#"Net(LB)"] else 0),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Net_LBS_PowerQuery", type number}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type",{{"Value", "LocationID"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns2", "BaleCount_PowerQuery", each if [#"In/Out"] = "In" then[#"NetBales"] else if [#"In/Out"] = "Out" then -[#"NetBales"] else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"BaleCount_PowerQuery", type number}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"ST", "MT"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns1",{{"In/Out?", "In/Out?-OLD"}, {"ID", "RowID"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Lot", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type2",{"Lot"},TLots,{"LotPrimaryKey"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"LotID"}, {"M.LotID"}),
#"Trimmed Text" = Table.TransformColumns(#"Expanded NewColumn",{{"M.LotID", Text.Trim}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"M.LotID", Text.Clean}}),
#"Merged Queries1" = Table.NestedJoin(#"Cleaned Text",{"MovementType"},TMovementType,{"MovementTypeID"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"MovementTypeName"}, {"M.MovementTypeName"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn1", each ([LocationID] <> "GrowerField" and [LocationID] <> "GrowerStack")),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Gross (LB)", "Tare(LB)", "Net(LB)", "Field"})
in
#"Removed Columns2"

[jmbenedetto]

I solved it by changing the privacy level of the data source in Power BI to "Private".

[snhoe]

Changing the privacy level to private as suggested in the reply did not fix the issue for me.

Still getting this error.  Any other suggestions?  MS Access is closed, the database not opened by anyone else, and the databse is located on a shared folder on Azure on a corporate account.

[snhoe]

The workaround I came up with today in Power BI Desktop to go to the data tab on the left sidebar, then on the right sidebar right-mouse (context menu) over the idividual tables and choose refresh data.

So it appears to be more of a timeout/bandwidth issue, than a locking issue as suggested by the error.

[jmbenedetto]

Hi,

It seems there's something to do with the last update.

https://community.powerbi.com/t5/Issues/April-2017-2-45-4704-442-DataFormat-Error/idc-p/169877#M6949

[elmazreku]

Did you end up finding any solutions? I am in the same boat.

Thanks!

Elizabeth

[elmazreku]

I too am having this issue. Did you ever find a solution?

Thanks!

Elizabeth