X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Access Fe - Sp Be, SharePoint 2013    
post Apr 28 2017, 11:00 AM

Posts: 292
Joined: 12-February 13

I currently have a FE file (Access 2013) for my users (about 20 of them) with a BE file on network drive.

Sometimes users need to work from home or off-site, so they bring the work laptop with them. They must use VPN to be able to use the FE so that they can access data on the BE on the company network drive. I get complaints mainly due to performance issues and connectivity but in the office, I get praises.

I have tested (on a very small scale) using SharePoint lists as the "BE". I was pleased to learn that it worked nicely and does not requires VPN. There was no known performance issues during the test trials, but again, this was a smaller scale with few records in the lists (tables) on SP.

My UA friends, I have a couple of questions I hope you can answer:

For Access:

1. Will the FE performance be actually compromised if I actually transfer my existing BE to SP lists. The existing BE is about 3MB large. Not so big in my opinion, which is why I am not recommending upsizing to MYSQL yet.

For SP:

1. Is there a way I can "hide" the lists so that users do not access the list directly on the browser except through the FE app I created for them?

2. Is there a way users can not know/find out the address to the lists on the company's SP site?

Go to the top of the page
post Apr 28 2017, 11:23 AM

Access Wiki and Forums Moderator
Posts: 73,501
Joined: 19-June 07
From: SunnySandyEggo


I'll offer my 2 cents but please remember it's just one person's humble opinion.

Using SP Lists is very convenient because they are accessible almost anywhere, and you don't even need to have Access to use them (some of your FE logics can be converted into Workflows). However, the main issue with SharePoint is it is not a true database system. Each item in a List is actually a part of an entry in a Memo field, so relational performance is not the same as in a true database system (even Access).

The limits for SP Lists do not necessarily depend on the size of your BE but more on the number of records in it. As I said, since each "record" in your BE because a "tag entry" in one column in SP, then trying to work on a particular record from a huge List in SP suffers in performance. Performance degradation is negligible up to a point until you hit some threshold and then your FE will run very sluggish.

A better approach, if your company can accommodate, is to use Remote Desktop (or Citrix). The performance using RDP is comparable to the user at the local office. However, the drawback is the extra cost and setup involved.

With regards to "hiding" the Lists, yes, you can use SP Designer to "hide" the Lists from the browser, but you won't be able to stop the users from finding out the URL (if they knew where to look).

Hope it helps...
Go to the top of the page
post Apr 28 2017, 12:31 PM

Posts: 292
Joined: 12-February 13

Ahh, you mentioned relational performance issues. That could be a deal breaker. Glad you mentioned this.

I will wait for others to chime in and see if relational issue in SP will be affected (even if I already designed it in Relationship Tools in the FE app).

* edit: If this is truly a deal breaker (not being able to have relations set up up), then it looks like Azure is the way to go?
Go to the top of the page
post Apr 28 2017, 07:52 PM

UtterAccess VIP
Posts: 2,692
Joined: 12-April 07
From: Edmonton, Alberta Canada

The performance of SP tables with Access “can” be very good – better then say SQL azure and with less changes.

However, there are limitations.

The basic setup that works well is thus:

All of your relationships are PK + FK (autonumber PK, standard long number FK). Your application MUST be designed around this setup. So no compound PK, and all PK’s must be auto numbers. The related child tables thus are connected via a FK and again this always needs to be a long number (the standard default setup for most relationships in Access).

Next up:
Your table sizes need to be in general less than 5000 records. This is especially the case with office 365 accounts. Tables can grow perhaps to say 8000 or 10,000 rows – but I don’t recommend much beyond that.

Also, your application should not have routines that use temp tables, or routines that “often” update say 1000 rows at a time.

So if your application “fits” within the above limitations, then you indeed see rather nice results with an Access front end, and the backend data residing on SharePoint.

The only other issue I can think of is you need to be using Access 2010 or later – the performance of pre-2010 is too slow for most applications and pre 2010 does NOT support related tables like 2010 and beyond does.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 06:17 AM