MS 2013 Access DB via Sharepoint. Resources?

In My Humble Opinion
1

I recently directed my company to trust me to build a “dashboard” for our everyday operations…

I chose MS Access because it’s a staple of database design, and basically infinitely customizable.

Furthermore, the sales people at MS told me I could use Office 2013 (on my desktop) to publish any database to the web for my company to utilize via web browser with Sharepoint.

That was entirely false, however, after the trial, I now have many hours into the build.

I just bought an 800 page book on the subject, but what is the best resource or forum for Access 2013 Web Apps via Sharepoint?

2

You need to publish the Access DB to an SP site in order to have integration.

There are limits to how deep you can get with this integration primarily because SP lists are not a proper relational dbs.

3

Stay AWAY from anything Microsoft. Custom programming will NOT WORK on later versions of their products. Everything needs to be reprogrammed! And it seems to me Microsoft could care less!

Try an “open source product” like Open Office (or what other people here may recommend)…
https://www.openoffice.org/

4

If you have particular questions, the MSDN and StackOverflow forums are great resources. Generally you can Google your question in natural language and find answers there. I believe the MSDN forums require you to have an MSDN membership to post, but anyone can view previous posts.

I agree with Quicksilver on the limitations of publishing a large-scale relational DB to SharePoint (SP). SP treats tables as lists and relationships as “lookup” fields within a list. You are limited to the number of lookup fields you can have per list. If you have just a few tables, with limited relationships, SP can serve as a good web front end for your database.

My CV: I’ve been a SP Admin (2007 through SP Online) for almost 10 years.

5

Does SharePoint work well with MS Reporting Services in your experience? Or can RS do better without? Talking in terms of interactive web pages, data gathering.

6

There is nothing wrong with MS Access for small to medium db’s and concurrent users.

But why Sharepoint? Having the data in SP lists will be a relational nightmare and drive you insane.

If you implement MS Access, be sure to split Front-End and Back-End. The FE should be copied from a master copy on the network to the User’s C: drives when the User launches the app. Robocopy should be used: it knows when a copy is required (because the master copy FE file is newer than the C: version)

I have used this many times and it works well.

7

Not terribly well. Microsoft wants to sell you their SP-integrated Business Intelligence modules, which are licensed separately for non-cloud customers, so they don’t have a good API for integrating with RS.

I suspect that dirty1 doesn’t have much of an option about the front-end dashboard location. If they’re using SP for their intranet, the execs likely want a one-stop-shop within the intranet for their dashboard. It’s unfortunate, otherwise RS or K364’s app implementation would be interesting options to explore for front-ending the existing database.

8

You know what’s under-valued with respect to senior management dashboards?.. SP Excel Services.

Before adding BI to your SP app server, it’s worth exploring building dashboard reports in Excel and exposing them on a SP site using Excel Services, which is already included.

9

Very good point. I wish we had enabled Excel Services in our on-prem environment before migrating to SP Online… everyone in IS leadership is now all gung-ho about Power BI without looking at some of those simpler tools we can enable for general users using tools they already understand like Excel.

I don’t know enough about the tool to know if the OP can make use of Excel Services as a front-end for his existing Access DB. It’s definitely a gap in my skill-set that doesn’t look to be filled any time soon.

10

Some years ago, we piloted BI on an on-prem 2010 farm. Can’t say I was hugely impressed. It was slightly better than the KPI web part solution. I’m sure the more current version 2013/2016/O365 is better. I should look into it.

I had another thought, for external databases, perhaps BDC (Business Data Connectivity) would work? But that’s more a reporting solution and involves managed meta-data, not so much a pretty dashboard with graphics solution.

Finally, it’s worth checking out what’s available in the O365 (SharePoint) App Store. There are hundreds of various solutions, most of which come with a trial version. Usually they are very inexpensive.