MICROSOFT ACCESS DATABASE - Load error; file already in use.

[samye228]

Hello,

I have an Access db that is an mdb file. It is our backend and contains only tables. The database was created in 1997. 

In order to use with Power BI, I saved it as a accdb type and changed the trusted location. I was able to connect and selected a couple of tables to load. Because there were relationships set in my Access db, the tables loaded with table columns. I deleted these columns. I was able to create a couple of visuals. I loaded some additional table from the Access db and when I Applied the changes, I received an error on a table:

The biFCA.accdb file does no have a lock file created for it. If I have the directory open where the database and power bi report are, i can see it locking and unlocking. If I delete the Building table, reload the connection and then add additional tables, it works. Then if I add tables after that I get the same error on a different table.

Are there options in Access I should set up before loading into Power BI?

Are there options in Power BI to set up for Access?

My intention is to copy our production database to this Access db for using with Power BI. It should be READ only access. Unfortunately, I do not have Admin rights so I can not adjust anything except for within the application.

Please advise.

Thank you,

Carol 

[jdbuchanan71]

Sure, take a look at this article that talks about how to create views in SQL.
https://www.w3schools.com/sql/sql_view.asp

[jdbuchanan71 · Accepted solution]

Hi Carol,

I wanted to reply specifically about this part of your post "My intention is to copy our production database to this Access db for using with Power BI."

I can't recommend against this course of action strongly enough.  You are far better off working with your database team to have views created in the production database that you can pull into your model.  They can setup a service account that has read only access to the database that you can use to connect in PowerBI desktop.

Pulling the data into an access database then into Power BI is just adding a transport step for no benefit.  

Also, if the views exist in the production database, your database team will have visibility to how data is being pulled into power bi and if there are changes in the data, the views can be updated as well.

[samye228]

Unfortunately, it doesn't seem like I'll be able to do the views. The database is an mdb format and I don't want to upgrade yet to accdb. The only option I have is to save the database as an accdb and work with it in a different folder.

The problem is Power BI is not able to read the mdb tables. I could change to another type of connection but I don't have admin rights to my PC and it's too much of a hassle to try and get that done. Also, using SELECT VIEW per the URL mentioned will not work in Access unles an option is changed, which could cause issues with queries. Although, I don't think that would be a big issue in this database.

I like the view suggestion and once the db is upgraded I will employ, so I've accepted it as the solution. Thank you.

[samye228]

Perhaps you could point me in the direction for creating the views. I created a query and set the recordset type to Snapshot so it would be read-only. I connected to the database and the query was not listed. Also, not all the queries were listed.  

[samye228]

Hi JD,

Thank you for your reply and suggestion.

The database was created in 1997, it's a mess but it's the only thing we have right now. If we do get a new system, we will need to extract the data from it so it remains what it is. 

There is no database team, there is me and two others but noone else is familiar with databases. I'm used to a mainframe, so working with Access is a challenge. 

The views sound like a great idea, I'll ChatGPT to see how to do that. I have no Admin rights to anything on my PC or the server the database is housed on. That's why I was copying it.

Let me know if you have any other suggestions. Again, thank you.