Forum Discussion
reading access databases hosted in sharepoint libraries: works in desktop, fails on service
Super UserAh, I totally missed the point that you are still using Access. (Why, though?)
Remember that Access creates an .ldb file (locking semaphore) when opening the database. That's not something the service can do on a sharepoint.
Hi lbendlin ...
Users are supplied data in access format (complex database dump from remote system).
Again, desktop power BI is able to open the file so I would assume sharepoint authorised by the oauth2 credentials should do the same for any connection (that can establish the connection to the database). We have had a similar issue in the past with access databases hosted in a local file system. For those we needed to install the accessengine components on the server where the on-premise gateway was running. In this case it doesn't use a gateway that we can control (cloud to cloud) and we can't force an access via our gateway since the gateway sources don't support the oauth2 authentication that sharepoint appears to require. (I can create an on-premise gateway connection, but without oauth2, it always fails authentication on the connection. If I tell it to ignore the conenction test and try to map anyway, it fails when it trys to connect later).
In the thread I accidently necro'ed the poster had the same issue but worked around by exporting to a sharepoint list (our data is both too large/complex for that and used by other business processes in that format)
If Access.Database() isn't supported for sharepoint content we should get it documented (or better, fixed). It does pass the folder reference into the functions so - maybe a missing protocol, or given our experience with the file system hsoted version, it possibly passes the responsibility for connection to the source server (sharepoint host) which doesn't have the MDAC/accessengine modules available to it).
No one else uses this type of connection? I was hoping it was just me missing a step or something (works for desktop, but for the service you have to add xyz step as well).
Our IT group are pushing all teams to move all files to sharepoint libraries for better version control and tracking hence the requirement.
Thanks for time and attention. And any other suggestions any one might be willing to share!
Jen
- lbendlin
Is 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.