any benefit to Access with Sharepoint? 2020 update...

Question

about 4 years past I experimented with Access and sharepoint….  primarily focusing on linking to sharepoint lists...

in the end I concluded there was zero benefit to it - - except perhaps if the sharepoint lists existed for another reason using sharepoint…...and you wanted that same data involved in a new Access app...    since then I've never seen any requirement for it.

Basically I haven't seen any reason for classic Access to involve sharepoint at all. 

Anyone out there with an Access/sharepoint arrangement that has a unique benefit/advantage? 

Answer 1

…...and you wanted that same data involved in a new Access app...

Hi msdn…,

For that purpose you don't need SharePoint lists, you can "late-link" to any external table.

Imb.

Answer 2

don't disagree - I was just saying I could see an organization already having/using a sharepoint list.....  and want that data as a table also in a new Access application....

...as the only reason I can see so far to link to - or use sharepoint/Access together...

the reason for this post is - I see periodically new Job/ new Project description as wanting 'Access on Sharepoint' - - - - and I don't think there is any reason / benefit to this.....   Am assuming the author simply doesn't know the technology and really wants a multi user Access database for their organization probably best deployed via Remote Desktop Services.   but thought I would sanity check this point with the community....

Answer 3

I don’t see this issue any different then say asking if there is any benefit to using SQL server.

I mean, if you don’t need SQL server, then you not going to see any benefit.

However, Access with SharePoint can result in some really fantastic abilities – one that would normally take a team of IT people to setup.

Say, you have a sales force. And in a particular region this group is out on the road. So, say all 8 users can have an Access application on their desktop. They all can update the same application.

And even better if one of them is traveling in some remote area without internet connection? Well that whole cool application on your laptop will continue to run without any internet connection. How does that grab you?

When you finally get back to some coffee shop with Wi-Fi, then any of the records and any updates you done will now sync like magic to all of the other 7 users. (And anything they done or added will been seen by you!)

So, you mean a team that can run a connected “on-line” database for what, $8 per month (total cost) for all of the 8-15 users is not a huge deal?

I would ask how would you deploy a sales force, or even deploy your access application to 15 people all working all over the country for a grand total of $8 per month.

How would you setup your access application to run anyplace and anytime with 8-15 people all working all over the place?

With SharePoint + Access, then you can do this, and do this without having to build a web site, without having to setup SQL server, and in fact all you need is plane jane Access skills. You don’t need much of any other skills then just that of Access. If you can link a table, then you’re off to the races in terms of your skill set.

If you can explain to everyone here how you would deploy a connected application that runs on each desktop, runs even when they don’t have internet, and allows all users to sync the data at all times?

Such a setup before Access + SharePoint was a HUGE undertaking.

With Access? I can do the above in near less time than it takes me to write this post.

The automatic off-line mode, and “sync” of the data is beyond amazing how well this works.

And now that SharePoint (since 2010) supports relational data, then even applications with forms + sub forms etc. will work flawless.

And, as noted, any of those users can work even without an internet connection. All of your VBA code (even recodsets), forms, reports etc. will work, and will work without any real changes as to how you would build an Access application.

So, you mean that deploying 15 users with Access that can work anytime, anyplace, and all share the same database, and do so for $8 per month is not fantastic?

You can do all of the above without a huge team of IT consultants, without having to setup and deploy a web site, and you can do this for near peanuts in cost.

And best of yet, you get to build and design the whole appcation in good old Access. That includes your forms, your code, and your reports. It all the same as before!

I can’t think of any tool that allows the above to be achieved better and with greater ease then using Access + SharePoint.

Now, it possible you never needed to have a mutli-user application that works any place, and any time, and allows those users to even use your application when they don’t even have internet. They will not be bound to their workplace, nor bound to their company network. Anyplace, anytime – that application will just work and run.

Yet, when they do have internet, then all of the records, data and including relational data like magic just automatic syncs up for you?

I can recall companies spending 30 grand for such setups for a mobile force, and you can do this in a matter of minutes, and your total on-line cost will be about $10 per month (not per users, but total $10 per month for all 15 users).

This setup is a gazillion times less effort, less work, and less learning then say having to adopt SQL server.

I would say the above ability of Access and SharePoint when used this way is nothing short of Amazing.

Just try and build a desktop application that automatic syncs data between all users, and to boot an application that even continues to work off-line?

I think this ability of Access and SharePoint is nothing short of stunning – absolute stunning.

But then again, it only my opining that the ability to deploy a multi-user desktop database system that is synced in the cloud without needing any more skills then Access is something that I could only dream about – even for Access before SharePoint came along.

SharePoint gets you a mutli-user application that runs on the desktop, but a team of 20 users any place and anytime can use and run that desktop application – the data will be synced at all times, and you can even continue to work without an internet connection. Yes, that is rather useful and amazing for a number of types of applications.

This Access ability appeared in 2010 -- that is 10 years ago, and this setup still works and is still a 100% viable choice now. All you need is one el-cheapo bottom feeder office 365 account, and you off to the races. I don't even have a office subscription, and my el-cheap 365 account works perfect for such a multi-user shared application that syncs and saves the data in the cloud - all without a team of IT people to set this up for you.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada

Answer 4

...well...okay then... thank you AK, so to parse this info...   and for the moment keeping the price issue out of it: in a citrix/rds deployment the user can be anywhere - so that aspect is equivalent.

but the point on working locally unconnected - and then syncing later is very unique/advantageous - it was not an aspect that I was aware of.  In this case then, by definition, the FE is on the local PC which puts the WAN in-between FE & BE?  I never found that to work well - lags, hangs, suitable only for small record sets...

how does the switch occur between the BEs when changing from offline to online?  does one have to prepare for being offline by pre-downloading the BE data or is this occurring continuously behind the scenes?

Answer 5

>>in a citrix/rds deployment the user can be anywhere - so that aspect is equivalent.

Not really. You have to setup a server, and chew up some serious IT support dollars and time to set that system up. Then of course you need to setup a secure VPN for the users  to connect to the company network before you can launch remote desktop. Don't get me wrong - remote desktop is one of the best choices for remote use of Access "any place" and any time.

>>the FE is on the local PC which puts the WAN in-between FE & BE?  I

Well, yes, but you don't have to setup or have that WAN, do you? As noted, it is  few mouse clicks - and you are up and running with say 15 users. You don't have to setup any kind of network. And you don't have to learn SQL server. In fact, you really don't have to learn and know about SharePoint either - since all of your application design - including that of tables is done 100% in Access.

>>how does the switch occur between the BEs when changing from offline to online?  

Fully automatic. You don't sync the whole database. If you modify one reocod, then if you are connected, then it syncs in real time. If you are off line then the updates are local.

You always have a full local copy of the data. As a result this setup can and will often run absolute circles around SQL server. And you can work off line. If you have no internet/network, then you can add or edit records. All edits occur on a local copy of the data. Only updates are synced. And it is a bi-directional sync. 

>>is this occurring continuously behind the scenes?

Correct. It occurs in real time and continuously.

If you are connected - on line you see this:

If you are off line, then you see this:

Access will go off line, and on-line for you. However, it can under some cases stay off line. (you can ignore the buttons and the rest of the above screen shots, but the above is what you see in the access status bar.

There are some limitations of this setup, but in many ways, it is a true killer feature of Access.

Remember, support for SharePoint appeared in access 2003 (17 years ago).

And here we are 17 years later, and that support of Access and SharePoint is STILL going strong.

However, back then? Well it was rather slow, and only good for simple "list" of names to share.

So, only a small % of applications would really benefit from SharePoint tables

Then in 2007? Well, they upped performance by a HUGE amount. All of a sudden, this huge increase in performance meant that a lot more Access applications could benefit from this setup. However, no support for relational data existed. So, now, maybe 5% or 10% of Access applications would and could benefit from this setup.

Then 2010 occurred. Two significant things changed:

SharePoint gained support for relational data.

Access gets support of SharePoint related tables

Access gets a local cached copy of data (2007 started this, but 2010 REALY worked well).

Now all of a sudden a huge increase in the number of Access applications could benefit from this setup. 

In fact, this setup often works even better then a split database on a company network. The reason being is that your data is all local - no network between you and your data. So, as I stated, this setup can often run absolute circles around Access and SQL setups.

There are however significant downsides and performance issues you have to be aware of.  And a significant number of the performance increase features were the result of Access getting a HUGE investment in web applications. While Microsoft has depreciated Access web applications, the nuts and bolts parts that work with SharePoint remain in place. So, the high speed local cache, and support of related sharePoint tables remains a Access + SharePoint feature and is NOT part of the web stuff that Access received in 2010.

So to be fair, a SIGNIFICANT amount of confusing in regards to Access and SharePoint exists since Access dropped the Access web applications part that also required SharePoint. 

However, the local data sync, the on-line mode, off line mode, and support of related tables all remain in place.

So, in less time it takes me to write this post? I can have some shared tables and a country wide multi-user database deployed - and I not had to do anything more then send my tables up to SharePoint - and supply my front end to other users. It works well, and works like magic.

I want to keep this post short - its already too long but updates of large numbers of rows is VERY slow. So applications that have to update lots of rows, and create + delete temp tables etc. don't work well. So certain types of applications will not work well, but other types? It is a beyond perfect setup. So, this setup is not good for all types of applications. But compared to 2003, and 2007, it is a huge leap.

In this video I show how I moved some data tables to SharePoint. While I used the now de-predicated web publishing options, the process is still the same.

https://www.youtube.com/watch?v=3wdjYIby_b0

And you really don't even need to do the above. There are better ways now.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada

Answer 6

Thank you that was very helpful to me, and to the community I'm sure.

May we zoom in on one particular scenario as it is very key: user online

with the FE local and the BE remote (sharepoint) - - it was this that held me back from the sharepoint deployment...  my experience may be out of date.  It worked, but it did not work well.  It would hang and clearly had trouble with record sets of 15k+

I have always avoided the WAN being in-between Front/Back and so am very interested in your more up-to-date experience of this key scenario as the majority of users will be in this mode and expect the same experience as if on-campus LAN.

Answer 7

Well, in effect, your application is not split. So there is no wan between your forms and the tables. You have a local copy of the data. So this is why I note that performance "can" (I stress the word "can") often run circles around a setup that results in your updates traveling over the network. 

Your updates occur local and direct to the table(s). And access is then syncing the data. So, in effect this works quite different then a typical split database in which the backend is over a network, or the back end sql server.

Now, to be fair, we see this forum full of posts that after a migration to sql server, things run slower. Like all things, this is not a all or nothing solution here.

I don't recommend letting the row counts in tables go much over 10,000 rows. I have pushed some SharePoint tables to 80,000 rows. But this was not a cloud hosted 365, but a SharePoint server we had control over (this is important, since there are long list of "governor" settings that exist on the SharePoint side, and you can't change these settings with a low cost 365 account.

So, ***if*** the application is suitable for SharePoint tables, then I would say that the user experience is as good if not better then even a split database on a network.  As noted, your not really running a split database anymore anyway. There is no network between you and your data. However, there are a long list of issues here as to how this setup will work well, or not work well.

In some ways, this is like adopting SQL server. However, I can say that the learning curve (compared to SQL server) is next to nothing. This is good, and bad. The good part is you don't have to do much. The bad part is if you have a performance problem, then you can't do much either! With SQL server, you can adopt views, and adopt things like pass-though query, or start to use store procedures. You don't have any of these options with SharePoint. 

Worse, is some things are counter-intuitive. Say you could and did get some server side code to update a table of 4000 rows. Well, with SQL server, that can be one simple update statement, and you ONLY network cost is sending that one sql update statement to the servver. Next to zero network cost.

Now, with SharePoint, the above idea does NOT work EVEN if you could send the update to run 100% server side. The reason of course is now the 4,000 rows have to come down and update your local table! So, you might as well just updated local, and let the rows sync up to the server.  So, over time, you will learn what works well and what does not (not really any different then say SQL server). The only major differance is as I noted - that being you don't really have any server options here (this is good from a learning point of view - but bad from a ability to increase performance point of view).

As I stated, there is no one single bit of trick or advice here. I can only suggest "general" concepts. 

So, I stated that maybe 2% of access 2003 setups would work with SP

And then I stated that 2007 was much better. Maybe 10%

And then Access 2010/2010 SharePoint. This was a huge leap. Maybe 40%, or even 60% of access applications would work well. But every application is so vast different, it like me telling you how long it will to make a great painting - the answer is it depends on a zillion reasons and circumstances here.

However this SharePoint options is simply a fantastic choice we have for Access, and it is a welcome choice.

Like all things in life?

Well, such things are never one size fits all - but then again, for applications Access is not always the right choice either. 

Not all applications will work well, but for those that fit within the limits of Access and SharePoint? I find it a better choice even then SQL server in some cases. But it going to be a case by case evaluation as to how suitable this setup is for a given application. 

I been using this choice and setup since Access 2003 - so it not a new choice, but it is a often overlooked choice.

Like I stated, there is nothing new here in the last 10 years, so any of this narrative I speak of is well, nothing new. The great thing about Access is have choices ranging from a single desktop, to split on a network. And with SQL, we now even can use SQL Azure (cloud based). And then another choice is SharePoint. 

All of these choices are just choices, but I think Access having all these choices is just another feather in Access's cap and bag of tricks. Access as a result remains an amazing tool - from desktop only all the way up to distributed solutions, and even ones with a off-line and on-line mode.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada