Power BI Desktop with Access (1 Viewer)

[samye228]

Hello,

I have been trying unsuccessfully to make a connection from Power BI to Access. My Access database is an mdb backend that is linked to a accdb. I tried using the Access connector in BI and have selected both file types. It makes a connection using accdb and mdb but when I click on a table it says the table is empty. I googled and found several that said to use the ODBC or OLE DB. When I tried OLE DB and use the connection string: provider=Microsoft.ACE.OLEDB.16.0;data source="S:\Access Applications\BackEnd_Tables\Facilities_v07.accdb" I get a connection but only to the tables that were created in the front end.

For ODBC, I created a data source 64-bit pointing to the folder that houses both Access files. If I select the mdb, and a table I get a message: DatSource.Error: The table has no visible columns and cannot be queried.

I'm really at a loss as how to connect the db. Googling also mentions 32-bit driver but I know I have pointed to the 64-bit. Unfortunately, I do not have Admin access to my PC. If someone could suggest the best method and what needs to be done, I can put a ticket in to our IT department. Thank you.

 

[Minty]

The MDB will be 32-bit, and that's what you need to link to so I son't think a 64-bit driver would work.
A possible alternative: Create a query in your Accdb FE that simply queries the BE table you want to and point your Power BI connector to the query in the FE accde not the BE table.

 

[samye228]

I'll try that but what I also read when I googled is that BI is 64-bit so that's why the 32-bit isn't working.

 

[tmyers]

I manage several Power BI reports that are connected to both SQL Server and Access but in my case the Access files are accdb not mdb so I am not sure if that would cause the difference.

When you select the tables are you doing them one at a time or multiple? And is it Power Query telling you that the table is empty or the preview isnt loading? Have you tried selecting the table it claims is empty and loading it anyway?

Edit:
One work around you can also try is to make a query in Access of the table(s) you are after and try loading that instead of the table itself.

Edit 2:
Just realized Minty already suggested the Query route.

 

[samye228]

BI is telling me the table is empty and if I try to load it, I get:

 

[tmyers]

Interesting, I have never run into that error before. One more question for you, are the Access files on the same network as the machine you are working in BI from?

 

[samye228]

The MDB will be 32-bit, and that's what you need to link to so I son't think a 64-bit driver would work.
A possible alternative: Create a query in your Accdb FE that simply queries the BE table you want to and point your Power BI connector to the query in the FE accde not the BE table.

I created a query in my accdb for one table. I tried connecting to the accdb table using OLE DB, the query was not available or the back-end tables. If I try ODBC, I get this error:

 

[samye228]

Interesting, I have never run into that error before. One more question for you, are the Access files on the same network as the machine you are working in BI from?

I'm working in BI desktop which is installed on my PC. We have a license for O365 but it does not include BI. The access file is located on a server.

 

[tmyers]

I created a query in my accdb for one table. I tried connecting to the accdb table using OLE DB, the query was not available or the back-end tables. If I try ODBC, I get this error:
View attachment 108071

Sorry to pester, but did you try using the Access specific connector to see if it can see the query rather than OLE DB and the ODBC? I personally have only ever had problems trying any other method to connect to Access from PBI.

 

[samye228]

When I try to use the Access connector it only provides tables, it does not provide the query I created. That is true for both the mdb and accdb dbs.

 

[tmyers]

Unfortunately then with my limited knowledge I don't know how to help. I have never personally seen this particular problem before but I do know if push comes to shove, the people over at the Power BI Reddit page are typically pretty helpful as knowledge on PBI here isn't in as much abundance as Access knowledge is . Others here may know if it is a problem on the Access side however.

Hope you get it fixed! Making BI reports is fun!

 

[samye228]

ok, i will try that. thank you.

 

[GPGeorge]

When I try to use the Access connector it only provides tables, it does not provide the query I created. That is true for both the mdb and accdb dbs.

"When I try to use the Access connector it only provides tables, it does not provide the query I created. "

By any chance, is that query parameterized? If so, that may be the problem. PowerBI would have no idea how to resolve the criteria in a parameter, so it might no even try.

 

[samye228]

i did have a where clause in the SQL for city. i removed it and it still isn't working. thank you for your reply.

 

[Minty]

I have just connected Power BI Desktop to an Access 365 FE that has mostly Azure SQL backend tables and few local tables and quite a lot of queries.
I can see all the queries, and local tables. If I click on any of the objects listed I get a preview after a few seconds.

To connect to it I simply clicked get data, selected More... and then chose Access Database. That was it nothing complicated.

A where clause in the query won't matter unless it references an Access form.

 

[raziel3]

Are your trying to connect to the access table or the query?

BI should see all tables. If your query has "Nz" in it BI will not see or process it.

There are a few Ms Access functions not compatible with BI or Power Query, not too familiar with all of them but I know for sure Nz is one of them. You may need to re-write your queries to substitute the incompatible functions.

 

[samye228]

Are your trying to connect to the access table or the query?

BI should see all tables. If your query has "Nz" in it BI will not see or process it.

There are a few Ms Access functions not compatible with BI or Power Query, not too familiar with all of them but I know for sure Nz is one of them. You may need to re-write your queries to substitute the incompatible functions.

Hi, I am trying to connect via a table but it was suggested to use a query so I tried that. My access setup is an mdb file with the backend tables and an accb with the fe forms, etc. so the mdb tables are linked to the accdb. nothing works, to connect to BI, as mentioned above. I've googled. One idea was that the BI is trying to use 32-bit but I have 64-bit and 32-bit on my PC. I don't have admin privileges, I have to put a help desk ticket in for that. I was hoping someone could tell me what to tell them but I guess I'll google again and see if I can cobble something together.

Thank you.

 

[Isaac]

"When I try to use the Access connector it only provides tables, it does not provide the query I created. "

By any chance, is that query parameterized? If so, that may be the problem. PowerBI would have no idea how to resolve the criteria in a parameter, so it might no even try.

I gave up on the whole using excel to pull data from access thing long ago. No matter what MS does it is SO fraught with limitations it's not worth it because even if you experience success on an isolated level in one project, all you'll end up doing is building your dependency on something that eventually will totally stop working for a variety of known reasons - thus incurring a lot of technology debt.

Excel can't read queries from Access for a number of reasons - all reasons so common to making Access queries that if you tried to accomodate those rules by avoiding those things in your Access queries, it would be about as useful as Web Projects were in Access without vba.

My personal opinion from my experience and pain: Just don't do it. It wasn't meant to be.

Create Access stuff that exports into Excel, even into well designed Excel templates and automation if you want.
If you want to pull from Excel, do it by pulling from SQL Server views, or use ADO to execute and fetch SQL Server stored procedures, all of this works beautifully. If you want to pull from Excel, then in my opinion, leave Access out of it. It's like 2 people trying to stay married that hate each other.