Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Mark Wolven]

I am trying to import 5 small tables from MS Access into a new BI report. Yesterday, the import didn’t work at all. Today, I requested, downloaded and installed the 64 bit version of Office 365 – matching the bit version of PBI. 

The good news – 4 of the 5 tables imported successfully. On the 5th table, I get this error:

The table that won’t load is 21 rows and 14 columns. There’s no special characters in the table name. And there are no special characters or anything out of the ordinary in the column names.

This is a screen shot from MS Access. Isn’t the version below (16.0.11929.20946) greater than the minimum required version (16.0.7124.1000) where 11929 > 7124?

Can anyone offer any advice or things to try?

 

——————————
Mark Wolven
Lead Informatics Consultant
Mark Wolven
8602733171
——————————

[Lutz Bendlin]

Export the Access data to CSV?

——————————
Lutz
——————————
——————————————-

[Mark Wolven]

Short term – that’s a fine workaround to get the data into BI.

But the Access database is used to generate and record progress and item status on issues as they arise. So, I want to link to the tables that are already being maintained – and not have to maintain the same data in 2 places.

——————————
Mark Wolven
Lead Informatics Consultant
Mark Wolven
8602733171
——————————
——————————————-

[Elena Schott]

For the smaller tables we keep them in sharepoint and use access for bulk updates to the linked sharepoint table.   For the larger tables we created an excel worksheet that links to the access table, formats the data with power query, and is then stored in sharepoint or onedrive to feed power bi.   The power bi side can be set to automatically refresh multiple times a day.  The sharepoint excel workbook has to be manually refreshed but the larger tables there don’t need daily updates.

None of this solves your problem, but is the workarounds I’ve been forced to, not having an enterprise gateway available as I’m not “IT”.

For your specific table, are any of the columns calculated or memo (long text) columns?   That might be an issue.

——————————
Elena Schott
Sr. Business Analyst
Aon – San Francisco, CA
——————————
——————————————-

[Mark Wolven]

Thanks Elena, 

There are no long text/memo columns  just number, date/time, short text and 1 calculated.
The calculated column is used to address a date that come in as short text (like 04JUL2020).
It’s converted with the formula: Mid([DISP_DT],3,3) & “/” & Left([DISP_DT],2) & “/” & Right([DISP_DT],4) which doesn’t use anything fancy.

——————————
Mark Wolven
Lead Informatics Consultant
Mark Wolven
8602733171
——————————
——————————————-

[Ka Ly]

I saw this awhile back, but have had many complaints about exporting tables from ACCESS to Power BI.  Hopefully this might help get you in the right direction even tho it doesn’t seem to be the problem you are having.
Guy in a cube
https://www.youtube.com/watch?v=jNQUUGmJ7hs

——————————
Ka Ly
Clinical Informatics Program Manager
——————————
——————————————-

[Jeremy Dennis]

Maybe this will help.. 

Troubleshoot importing Access and Excel .xls files in Power BI Desktop

Kind regards, 

-J

——————————
Jeremy Dennis
Director of Analytics
——————————
——————————————-