import multiple access queries from network locationns

[darrylwilson]

Hi

The company i work for have (for each user) an Access database application that is stored on their desktops (which is stored in a cluster storage location).

How can I import the queries from over 300 people's access databases ? And then have them categorised (for example) , and have indivdual permissions on a particular query (for a particular person)??? Is there a way to Bulk import from a database and reiterate this to multiple network locations?

Also (kinda similar) how can i import into Power BI (in a centralised location ) the reports that people have created in their access databases?

I really need to know so I can advise the company how to move forward, any help will be massively appreciated.

[Charlemi]

Hi All,

I have been trying to import Access database into Power BI and have been unsesscceful with the import.  I keep getting the error message " Unable to Connet'' we encountered an error wist trying to connect.

Details: "Microsoft Access: The 'Microsoft.ACE.OLEDB. 12.0' provider is not registered on the local machine. The 64-bit version of the Access database Engin 2010 Access Databes Engine OLEDB provider may be required to read 'Mecologix Access Live - Source - HK.accdb'. To download the client software, visit the following site:

https://go.microsoft.com/fwlink/?LinkID-285987."

I have downgraded from 64bit to 32bit but still having the same error message pop-up.

Can you advise me what to do moving forward?

[CahabaData]

Neither Access Queries nor Reports can be imported into PBI.  Different technologies totally.

You don't state - but I will assume - that the Access users actually have just the front end file and all are linking to a common back end.......and if there are really 300 then one would presume the back end is SQL Server rather than Access......but that doesn't really matter at this point....

In the end you will be linking PBI to the data itself - and then building new reports from scratch....

[ImkeF]

Everything you do in the query editor of Power BI can be turned into a function and thereby be applied multiple times. So if you create a table containing all the source paths of your databases, you can perform (the same) import-procedures on them.

For an example see: http://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/

I have never worked with Access-import, but very much doubt that reports can be imported.

[v-micsh-msft]

Hi darrylwilson,

We may write some Power Query (M) language to get the data from multiple data sources, but it is not available to assign permission on particular query in M language. Well currently I am not very good at M query, maybe @ImkeF could help in your scenario.

The available way that I could think out currently is:

All users access the same Access Database stored in a File Share, then enable ABE in the File share, thus users could only access his own database file;

Then every user access the database file using Power BI Desktop to generate reports, if those reports need to share with others, publish them into Power BI Service, and share the dashboard, or create content Pack.

How should I share Power BI dashboards and reports?

For Access reports, currently this is not supported to have it import into Power BI, importing Access report into SSRS is possible:

Import Reports from Microsoft Access (Reporting Services)

And we could then pin SSRS reports into Power BI Dashboard:

Pin Reporting Services items to Power BI Dashboards

Regards