Forum Discussion
reading access databases hosted in sharepoint libraries: works in desktop, fails on service
Super UserIs there any chance in your reality to dump Access and move to an enterprise database like SQL server?
lbendlin wrote:Is there any chance in your reality to dump Access and move to an enterprise database like SQL server?
*grin* we have many sql instances (large mining company) and connect to them in all sorts of interesting ways.
We also use sharepoint lists and libraries, automate, azure and various cloud data storage systems.
But this one (and others) are shared with us as access databases. And it works from PBI desktop. And PBI service to file system via on-premise gateways as long as the gateway server is configured with the accessengine install. I could SSIS it to a SQL instance but that is another process to maintain, and every chance this is just a problem with the way I coded the database access (just via the GUI, no deep dive into the M lang documents yet)
I'm surprised this hasn't been more of an issue previously - there seems to be a big push from microsoft to use sharepoint libraries for all file hosting requirements.
- lbendlin
NSRjecross "the gateway server" made me chuckle. For business continuity I always employ gateway clusters with members distributed geographically (not just a bunch of VMs on the same physical host). But this comes with its own problems, like in your case. Not just do you need the access engine on all cluster members, it also has to be the 64 bit version. Endless fun can be had configuring that, not entirely unlike herding cats.
Though in this case if I could force the connection via one of our on-premise gateways, then I'd be able to ensure the libraries were installed. Wonderr which is easier to fix - oauth2 for the gateway or Access.database() for sharepoint [contents]
I haven't tried running it outside premium capacity though.. I'll give that a go monday
Any other suggestions? Does anyone have this arrangement actually working?
Jen
Herding cats is easy with a can opener. Not sure quite what to feed O365 (apparently cash and souls isn't enough)
Update: as of this morning it appears OAuth2 authentication has been added to the on-premise gateway for Sharepoint connectors. (But you need to upgrade the gateway software before you can define a connection using it).
This will at least let us route the query past somewhere we have installed the required modules.
I also found you can examine the connection string used to query the access database via the xmla interface (provider=microsoft.powerbi.olddb in case anyone was interested)
Getting closer I think
Jennifer