UtterAccess.com
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
> Backend = Sharepoint / Draw Backs, SharePoint Foundation 2010    
 
   
Brepea
post Oct 24 2017, 10:33 AM
Post#1



Posts: 515
Joined: 11-January 09
From: UK


Hi All

I've created posts here in the past about getting help migrating data over to SharePoint (SP) from Access - and that's been most helpful. I have eventually managed to get everything to SharePoint and the response times on form-load times and reports is significantly faster - so a real solution there. I'm quite happy with the SharePoint BE (for now).

I would like to find out before spending anymore time on this whether there are any real draw-backs of using SP as BE over using Access as BE? I mean in my context - SP seems like the only solution so far (other than RDP/ Citrix) - but for now let's assume SP and Access BE is my only option - i've not created a SP BE Access FE hybrid application like this before - so should i be worried / concerned about anything? My current application's data (BE in Access) is around 5MB and the FE is around 10MB (when converting to SP BE my Access FE increases to 25MB - not entirely sure why - has this got something to do with caching?). I have a table which currently has 1,300 rows (max rows in any table) - but this is likely to grow to around 5,000 rows within 6 months.

My FE works just fine as it did with my Access BE - but for one particular form - when opening the form up in ADD MODE - it doesn't auto-increase the long integer to the next number (so i'm trying to work out why this is happening) - but other than that it loads much, much quicker. I am wondering how you keep the BE updated; for example in the previous setup (Access BE and Access FE) - when my data in my main version of the BE updated (so while my application is being tested, the old legacy system continues to be used so updates to that system need to be updated to my system accordingly)- so did a complete dump of the legacy system into my Access BE and then copied that BE to the network folder (5 minute job). I'm not too sure how to do the same with the SP BE. I'd appreciate any advice on doing this.
Go to the top of the page
 
DanielPineault
post Oct 24 2017, 10:55 AM
Post#2


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



My understanding is simply that (i) SharePoint uses lists, not tables and so you loose the relational aspect of Access' RDMS, (ii) SharePoint lists can only house 5000 items (someone else can correct me on this) so for small dbs this is all good, but for enterprise solutions this simply does not suffice.

Did you explore using SQL Azure as a backend or a local SQL Server instance (even the free Express edition)?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
Brepea
post Oct 24 2017, 11:08 AM
Post#3



Posts: 515
Joined: 11-January 09
From: UK


QUOTE
(i) SharePoint uses lists, not tables and so you loose the relational aspect of Access' RDMS


Hi Daniel - thanks for the response; can you expand a little on the lists vs tables (drawbacks)? I realise they're lists and not exactly tables, however they have the ability to maintain referential integrity...is there anything else to it? I really don't like the 5k limit on rows (but i suppose on the few lists that begin to grow close to 5k rows - I can simply create a new list and say take everything before a certain date and update a history list 1, history list 2 and so on....Do you have any advice on the other bits i raised that could help?

Really appreciate your time...
Go to the top of the page
 
DanielPineault
post Oct 24 2017, 11:57 AM
Post#4


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



From my understanding, I haven't worked with SharePoint in years (it simply was not up to the task back in the day when I seriously look into the matter - couldn't even handle document revisions, forget addendums, ...), but that SharePoint did not have the ability to enforce referential integrity and thus it then became a developer problem rather than a fundamental functionality. At the end of the day, it is a minor point, I myself have had to work around this type of issue in the past and it isn't too hard to code in the necessary rules.

To me the killer is the 5k limit. For that alone, I wouldn't turn toward SharePoint in any real manner. I'd invest in Azure at this point in time or port it all way from MS completely and go and use a proper web technology.

I'll be interested to see what other people contribute. Hopefully someone like Albert Kallal might be around and share his experience as he has a lot of it!

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
theDBguy
post Oct 24 2017, 12:14 PM
Post#5


Access Wiki and Forums Moderator
Posts: 71,225
Joined: 19-June 07
From: SunnySandyEggo


Hello,

It's been a while for me too, but I remember BananaRepublic mentioning in the past where he dove deep into the inner workings of SharePoint Lists only to find out all those items we all think as "records" were actually just a continuous list of text in a Memo field.

I may be remembering this wrong or it has changed since then, but what I recall about SharePoint is it is simply an application - not a database engine. It does use a SQL Server to store the data, but a List, which is what's exposed to us, is merely a record in a table. Therefore, all the items in the list is contained in one field in that one record in the SQL Server table.

I think this is partly why working with SP Lists is slower than using actual tables because the application (not engine) has to parse the one field to display the items in a table format.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
jleach
post Oct 24 2017, 12:23 PM
Post#6


UtterAccess Editor
Posts: 9,806
Joined: 7-December 09
From: Staten Island, NY, USA


As I understand (not much firsthand experience), the nature of the SP "Lists" has a strong tendency to slow things down quite badly when the amount of data starts growing.

Just out of curiosity, why not something like SQL Azure? I don't think I've never seen a remote database better suited to SP than SQL.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Oct 24 2017, 12:47 PM
Post#7


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


My understanding of SharePoint lists aligns with all of the technical stuff mentioned.

However, it is possible to define and enforce the functional equivalent of Referential Integrity in SharePoint 2010 by using the Lookup Field mechanism. So, in a practical way, yes, you can do that, as we saw the other day.

Also, the 5,000 record limit is not on the number of records. Rather Office 365 limits the number of records that can be INDEXED at one time to 5,000. That has the effect of preventing import of large recordsets that would exceed that limit. On the other hand, I've seen lists in excess of 27,000 records in SharePoint lists, accumulated over a period of time. This was also on an on-premises SharePoint site, configured by the organization itself, not by Microsoft. On-Premises SharePoint sites can be configured by their admins to vary from that limitation on indexing, and others.

The main advantage SharePoint has over many other remote database solutions is the ability to work in off-line mode. SharePoint appears to be quite good at resynching locally cached data when an Access database has been used in a disconnected environment and then reconnected.

--------------------
Go to the top of the page
 
Brepea
post Oct 24 2017, 04:02 PM
Post#8



Posts: 515
Joined: 11-January 09
From: UK


Thanks everyone - it's made me feel a little more cautious about continuing down this path...I've set it all up in SQL Azure - so will test that approach too. The main reason behind using SP was the ability to control user access easily (without IT involvement), trying to work in an environment where users are going to be using wifi/WAN and LAN (whenever you remove the LAN a network interruption error occurs) and it's apparent that the network server was slower than expected; now with the comparison of SP to the standard Access BE & FE - the SP FE seems miles faster (which seems contrary to what others say about it). So in an SQL (local instance) - do you know if one will experience the same network interruption issue? I'm sure the SQL Azure method will be fine as this is web-based so the network interruption will not come into the equation (but of course there is an added costs to SQL Azure).

Does anyone know how i can deal with my current issue with the SP BE and Access FE setup (assuming I go ahead with this)? I'm not sure this is normal behaviour or a design floor on my part, but the issue is as follows: In old std Access FE and BE approach - you know when you have an autonumber as primary key - say StaffID - when you create a new staff member with a form using cmdButton on-click event handler - docmd.openform "f_Staff", , , , acFormAdd - the PK shows the next PK# in the field when form loads. Great! However with the SP BE this PK doesn't show - so the StaffID is blank (and I had a lot of code referencing this StaffID for certain actions or tasks associated to the record) - so now my before-update (audit table process) and other code fails as it doesn't know the PK value.
Go to the top of the page
 
Brepea
post Oct 24 2017, 07:37 PM
Post#9



Posts: 515
Joined: 11-January 09
From: UK


ok - i've seen some posts in the past which suggest i need to work around that as SP lists are based on SQL which don't auto-increment until "SAVE" is pressed. So i have various places in one form that rely on that ID - so i have inserted a new record to the able as user clicks a button to add new staff...for other places i've just used DMAX looking up the ID value on a totals query which does Max of StaffID...approach is working so far....
Go to the top of the page
 
Brepea
post Oct 25 2017, 06:31 AM
Post#10



Posts: 515
Joined: 11-January 09
From: UK


George - this section - can you help me understand this more:

QUOTE
Also, the 5,000 record limit is not on the number of records. Rather Office 365 limits the number of records that can be INDEXED at one time to 5,000. That has the effect of preventing import of large recordsets that would exceed that limit.


If i have 2,000 risks in a query result - say each risk has 5 indexed fields; are you saying the 'indexed' results returned ordinarily would be 2,000x5=10,000 - is that what you mean? If so - what happens when you try return this amount?
Go to the top of the page
 
GroverParkGeorge
post Oct 25 2017, 07:23 AM
Post#11


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


That's something I've not tested.

However, my understanding is that it would refer to the total number of records.

Keep in mind that this is only going to be a significant problem if you are appending records in bulk. I.e. on initial loading of the SharePoint lists. And only on Office 365. If your IT has imposed this as a throttle in their configuration of SharePoint internally it will apply. They may or may not have done so. Probably so, but you can check.

--------------------
Go to the top of the page
 
Brepea
post Oct 25 2017, 07:30 AM
Post#12



Posts: 515
Joined: 11-January 09
From: UK


Ok - so if you are merely viewing 10,000 records - that'll give you no issues, but if you attempt to do an append to 6k of those rows you're likely to get a threshold or some error...

I can imagine my audit table exceeding 5k rows within 6 months for sure - so i was worried this will prevent record 5,001 from being updated (when the 5k limit was reached) - or having a reminder set for each table where rows are nearing 5k...but it seems this is never going to be an issue if this limit only applies to appends - have i got that right?

Thanks again George...
Go to the top of the page
 
GroverParkGeorge
post Oct 25 2017, 07:42 AM
Post#13


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


Well, "never" is a big word we probably don't want to rely on, but otherwise you're right.

And again, you can verify this limitation with your IT who configured your site.

--------------------
Go to the top of the page
 
jleach
post Oct 25 2017, 01:48 PM
Post#14


UtterAccess Editor
Posts: 9,806
Joined: 7-December 09
From: Staten Island, NY, USA


Any reasonably solid application (and even throwaway ones) should be written with a core intent being that we should NOT have to change things if a certain limit is hit (this is a bit subjective, but we aim for 10+ years and have no issues with this when following general architecture best practices). This is especially true in database design. Especially, especially true if the subject is record counts within a database.

That this conversation is even taking place should be a strong indicator that SP as a backend is unsuitable for anything but a cheap toy.

In my humble opinion, of course smile.gif Cheers,

--------------------
Go to the top of the page
 
AlbertKallal
post Oct 25 2017, 02:42 PM
Post#15


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


George is “spot” on the 5000 index limit. So if you upload a table less than 5000 records (with all the indexes you need), then such a table can then grow beyond 5000 rows. And this limitation only applies to hosted/office 365.

If you are on-prem (your own SharePoint server), then such limitations don’t exist.

So the 5000 row limit on an index not a problem if you can avoid it. (And this limit has really nothing to do when pulling data).

As you well note, in “many” cases you find the performance of SharePoint back end tables FAR EXCEED the speed of SQL back end tables – especially when working over a wan. And I find this especially so for reports.

The reason of course is that you have a local cache of the data and tables – so your performance is like a local un-split database.

Where SharePoint will really slow down is if you have to update lots of rows. So if you have a “update” intensive application, SharePoint tables are really slow. So those munch and crutch type of data applications in which you create temp tables etc., and have to update/process lots of records with updates – then that is where SP tables are REALLY slow.

The other issue is that NO server side updates or PT type of query can be used. Even if a data macro is run server side, this will start a “sync” with the client database to update and thus again poor speed.

I have pushed tables up to 80,000 rows with SharePoint – but this was a local net instance of SharePoint. The hosted one likely would not fare much better.

The other “sad” limitation is that the local data indexes are ONLY used for the PK, not the other fields. I wish this was not the case, but since Access is so fast on local tables, that even a sort + group by on local tables tends to still be faster than SQL server “over” an internet connection. Access local can sort and group 100,000 rows of data without an index in about 1 second.

So the posters comments about how fast these tables work, and even much faster than using SQL server is a correct observation.

Since the tables are local, then many a query and data pull will be far faster than SQL server (over that limited connection).

Where SQL server will win is when doing updates of lots of rows and data – and this can be done server side without any real network cost.

All updates on SharePoint will case data flow both ways. So you update local, it has to sync to server. And any server code update will cause data to flow to client.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
Brepea
post Oct 25 2017, 05:58 PM
Post#16



Posts: 515
Joined: 11-January 09
From: UK


Thanks Albert - i appreciate the time.

To clarify - so the 5k limitation is more about loading the data into SharePoint; i.e. when you do so - ensure that the rows are <5k and make sure that before you exceed 5k rows that all indexes are set properly...? Other than that I can't see any massive drawbacks - only positives about using SharePoint lists (well, in context of course) - as I'd much rather use Access FE and Access BE as a solution - but as explained - it's just not working out. I really like the speed of SP.

My other consideration is that people are beginning to move over from SharePoint 2010 to Office 365 (so i guess SP 2016) - would this be an easy enough move? Anything I should be careful about?
Go to the top of the page
 
AlbertKallal
post Oct 25 2017, 06:41 PM
Post#17


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


The other nasty limitation you can hit with 365 is I think 50 column limit. (and again no such limit if on-premises)

R
Albert
Go to the top of the page
 
Brepea
post Oct 26 2017, 03:41 AM
Post#18



Posts: 515
Joined: 11-January 09
From: UK


Thanks Albert...

I have these 3 links which was causing me confusion about the pulling/querying of data over 5k records:

(1) Link 1
(2) Link 2
(3) Link 3

They though seem to be mainly talking about data extracted / views pulled from SharePoint directly - not from Access...
Go to the top of the page
 
Brepea
post Oct 27 2017, 06:34 AM
Post#19



Posts: 515
Joined: 11-January 09
From: UK


Sorry All - this is one last (i doubt it) request for advice: - i'm quoting myself here: In context - this applies to BE in SharePoint (SP) - FE=Access...

QUOTE
I am wondering how you keep the BE updated; for example in the previous setup (Access BE and Access FE) - when my data in my main version of the BE updated (so while my application is being tested, the old legacy system continues to be used so updates to that system need to be updated to my system accordingly)- so did a complete dump of the legacy system into my Access BE and then copied that BE to the network folder (5 minute job). I'm not too sure how to do the same with the SP BE. I'd appreciate any advice on doing this.


Anyone able / willing to help steer me...?
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 03:56 PM