Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
dolss1
Advocate I
Advocate I

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

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?

 

10 REPLIES 10
Anonymous
Not applicable

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
Regular Visitor

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

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
Frequent Visitor

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
New Member

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

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
Frequent Visitor

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
New Member

elmazreku
Frequent Visitor

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

 

Thanks!

Elizabeth

elmazreku
Frequent Visitor

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

 

Thanks!

Elizabeth

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.