Message from discussion Access and Office 365 - limitations
Received: by 10.66.86.133 with SMTP id p5mr3519755paz.12.1348968636294;
Sat, 29 Sep 2012 18:30:36 -0700 (PDT)
From: "Albert D. Kallal" <PleaseNOSpamkal...@msn.com>
Subject: Re: Access and Office 365 - limitations
X-Newsreader: Microsoft Windows Live Mail 15.4.3555.308
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3555.308
X-Trace: 1348968635 184.108.40.206 (Sun, 30 Sep 2012 01:30:35 UTC)
NNTP-Posting-Date: Sun, 30 Sep 2012 01:30:35 UTC
Date: Sat, 29 Sep 2012 19:30:33 -0600
"Paul H" wrote in message
>I now have a fairly large project - the Access accdb file is around 100MB
>and there are around 20 tables in the database. We want to migrate the
>tables to SharePoint lists.
>We have run a few tests uploading the data and encountered several errors
>and issues e.g.
>1. Larger tables (50,000+ rows) have all kinds of problems converting to
First of all, I commend you for doing some testing, some gathering of the
landscape, and checking out what kind of works, and what kind of does not.
I have so often seen people assume that they can simply split an access
database, and then use it over a VPN. Boy, do such folks get egg on their
And I even seen people migrate Access back ends to SQL and then stand
in shock and horror that performance did NOT increase!
Now while the above of trying to run a split database over the Internet via
a VPN, or some migration project in which back in tables are pushed up to
SQL server, the outcome of such projects is often the either a really big
surprise, or no surprise at all.
In fact what we're talking about here, is experience!
So at the end of the day, you get high marks and a fantastic high five for
doing some stress testing.
And, you also not afraid to ask and point out tough questions!
You are doing some feeling of the landscape. This means you are asking,
testing, probing. The result here is you will walk away with having some
great NEW experience and knowledge in our industry.
Ok, so, to make are long story short, back in the Access 2003 days which is
about 10 years ago, (and not to wax eloquent about how amazingly fast time
seems to fly - but just wow!, access 2003 is now about 10 years old!)
Any way back then, access 2003 had introduced some new SharePoint features.
And I think few realize back then, this was around the time that office live
small business was introduced, and even more amazing is back then few
realized that office live small business was actually free. And it included
SharePoint features, and you could publish (migrate) your access tables to
I was so excited, that I actually committed to some projects for some
customers to use the cloud for the backend end part. Remember 10 years ago I
don't think we even use the term cloud!
It turns out that performance was not sufficient. So, backed into a corner I
switched the back ends back over to using hosted SQL server. As a result I
been using SQL hosed and been deploying Access front ends to their desktops
that consumes the backend database over the Internet.
So, using SQL server is always a great possibility here. I been doing this
for 10 years now.
And Access 2010 has support baked into the product for SQL Azure (cloud
In a way, I'm am much happy and better off that the older SharePoint setup
Work. As a result I gained a lot of experience and learned a lot of
cool techniques for supporting customers and clients. And it also means I
became experienced with SQL server, and MORE so in deployed access databases
to the desktop, but using SQL server back end over the Internet.
I Point the above story out for two very important reasons, one is SQL
server is always a great possibility here, and secondly the limits and
performance of SharePoint have been long and widely known in this industry
for 10 years, and is not something new.
>My understanding is that the limit refers to how many records I can view in
Actually, to be honest, in reading almost every article in the last 10 years
going well before we ever had some web browser options available for access
developers, the suggestion was to keep those lists well below 5000 records.
This is not any new news here.
So just like the person that discovers it's a great idea to split your
access database, this is just WIDELY known knowledge that one learns over
time. It would be dishonest of me to stand here and state that the first
day I use access, I knew that I was to split the database. But I would not
stand here and tell me golly, this is new news or some big revelation that
were supposed to split!
So with gaining knowledge of SharePoint, it pretty much a given that the
widespread knowledge of limits of SharePoint are widely known and talked
about in our industry, and the same thing goes for Access and learning
simple basic concepts such as normalization, or something like it's a good
idea to split your database.
As always, you have to use the right horse for the right course.
Back in Access 2003 days, with office Live small business, or say tomorrow,
if you have a sales force of 3-4 four people on the road working contacts in
a new city, and say you have a list of perhaps 200 to 300 contacts that they
all must share and update at the same time? Then using a linked table to
SharePoint is an absolutely ideal solution here.
The above allows you to coordinate the contacting of those customers, and
while your sales force is in that area, you won't do the stupid thing of
contacting a customer that you've already contacted, or one that say that
there are ready not interested. So you don't bother customers, and you don't
re-do and waste sales force time doing the same time two times.
Now having said the above, with the advent of Access 2010, and the new data
cache model, then this setup SUBSTANTIALLY changes what is now possible.
The substantial changes open MORE options and a whole bunch of doors here.
And for those learning, I'm going to take a cut and paste from a recent post
of mine here from this newsgroup.
So here a "general" rundown of my experiences for these limits with Access
Access and SharePoint Access 2003 - maybe 2-5% of applications would work
Access 2007 - much better - perhaps 20%, maybe 30% of applications could
work with SharePoint - but we had no relational features.
Access 2010 - 60-75% of applications could now use SharePoint. And WE NOW
have relational database features for SharePoint such as cascade delete.
In other words if we have a list of 500 names of customers and a sales force
working in a new area to go out and contact customers? Then sure even Access
2003 and SharePoint (or back then the FREE SharePoint from Officer Live
Small business) would and could work quite well. (and amazing how few people
realized this service was free!!!).
And note in the above the one big gem stone:
2010 has Referential integrity.
Obviously if you have an application that has a lot of child tables then,
then obviously to utilize any version of SharePoint BEFORE 2010 would
(could) be problematic in those cases where your code or application
requires or relies on cascade deletes.
It would be stupid to stand here and state that every table we have in every
application has related tables. It would be equally stupid to ignore that
such requirements are most often typical.
One can write your own cascade delete routines, but for the most part such
efforts are not likely worth it. The reason is twofold:
If you have related tables, then you likely are starting to have more data,
and if the relation is more than 1 table deep, then you quite much have to
resort to using recursion to solve this â€“ and in fact that is exactly the
code solution I used (else such code become quite messy to code any other
way â€“ Google "recursion" if you don't know this term).
Okay having said the above, let's address a little bit of this 5000 record
issue, and what is going on here.
First up, and important is that of using a hosted provider, or are you using
I should point out, that there is a Mount Everest of a DIFFERENCE between
hosted servers, and that of using cloud computing. This is just one of
those things you have to learn like splitting an access database, or
learning for the first time about the concept of normalizing tables!
So grasping and learning that cloud computing is HUGE and different form
hosted services is something you simply have to learn over time.
And of course also what type of network connection do you have to the server
also going to be REALLY big issue here. So what kind of network are you
dealing with? This is such a HUGE and often LEFT OUT detail that to me this
is worse than someone admitting to me that they never heard or realized that
database normalization is a good thing!
So for anybody that does not grasp and understand the differences in the
type of network connections they are using, or if they're going to utilize
the Internet as opposed to their local network, then they better have the
realization that such connections are approximately 100 times slower. I
really cannot stress except hit you over the head with a frying pan two or
three times and state this again:
The internet is 100 times slower than your local network! Got it?
And if you can't grasp the above statement, read it again 10 times! Then
read the following article of mine in which I explain the spectacularly
simple concept of this network speed.
And for some reason no matter how hard I try to explain this simple issue,
even AFTER people read the above article of mine, the simple statements in
above continues to be spectacularly ignored and spectacularly not gasped by
most people in our industry.
99% the time they come back to me and say, well I have this high speed
Internet? And my answer back is what part about the 100 times slower did you
So, for example on my local network and SharePoint 2010, I can have no
problem pushing up 80,000 rows. It still slower than pushing up to SQL
server, but it not a big deal either.
However doing the same thing over the internet is VERY different matter.
Then there is the issue of using cloud computing such as office 365 and that
again is an ENTIRELY different matter.
>2. I also encountered other 365 "features" like the inability to delete a
>site that has lists in it!! Very tedious if you want to rebuild a site from
>scratch. Likewise we had problems deleting lists that had data in them.
Yes, as noted, there are some big differences between hosted systems (and
this does not only apply to SharePoint, but as noted often is a cloud
So, in the case of 365, yes, unfortunately some of the issues have much to
do with governors put in place to prevent users from placing excessive load
on that utility service.
A really great way to think of this is how your local electric company can
provide electricity for THOUSANDS if not millions of home.
However, if you go to a typical jobsite and watch them do some welding with
electricity you see they use a truck with a diesel generator on the back. So
there is this massive electric company with giga watts of power! - yet they
use a diesel truck with a welding rig mounted on the back!
Of course in many cases such mounted welding and diesel generators are use
since the job site is new, and there is the issue of convenience of not
having to find or even have electricity on site.
However, EVEN in those cases where electricity is all around them, they
still utilize that diesel generator to do that on site welding. The REASON
is the electrical grid in most cases cannot easily supply that kind of power
required for welding. In other words there some pretty significant limits on
how much power you can draw out of electrical grid.
Grasping this computing resource management and concept of utility or so
called cloud computing as opposed to a server based system is quite much the
same paradigm change and reasoning you have to adopt.
And like learning it was a cool idea to split your database, the same thing
occurs when using office 365.
So you need to test, and also learn what works, and what does not. (so
applications that update lots of rows will not work well with 365). So you
want to push up against the walls and probe and prod and find out what kind
of limits you have and see what works and what doesn't.
I suppose this is no different than deciding at what point in time should
you migrate your typical access application up to SQL server because you've
outgrown the limits of access?
I've seen some cases were a person has a table grow to only 1000 records,
and they thought that was too large for access to handle so then they
brought in people to start using SQL server for that data!
>So, can someone shed some light on the 5,000 list limitation and whether I
>should be worried about it in larger systems, or is it just that the 365
>version of SharePoint is poorly implemented or inappropriate for this type
An excellent question!
The first issue of thing to keep in mind, is unfortunately that if you
attempt to load a table larger than 5000 records at one time, then you're
going to put yourself in a bit of a pickle.
The simple reason is there are some hard limit settings in office 365.
(remember the huge, large, massive, big difference between cloud based
systems as opposed to hosted systems).
So, there are some governors in place that will not allow you to set
and delete or remove indexes on tables in excess of 5000 records. This means
if you upload a table of 6000 records, then you CANNOT set indexes after you
upload (and this ALSO means your existing index settings are ignored!).
With this knowledge, then you can get around this issue and limits if your
initial table that you create has less than 5000 records. In other words
setting up the relationships, setting up the indexing, and doing all these
things is possible if you start out with something of less than 5000
With a hosted provider, you can (but not always) change these limits. And if
your company runs SharePoint, then these limits can be changed.
And using the paradigm of utility computing, a great example of this is
probably how long it takes you to fill your swimming pool. You only have a
garden hose, and it doesn't go very fast. In fact they can take many days
to achieve such a task.
Compare that to firefighting equipment in which they have big huge diesel
trucks with built in tanks. And they ALSO have specialized attachments to
the water mains and utility that BYPASSES the typical limits of how much
water you can draw from that system. So what you can draw and use for your
household is LIMITED.
So once again the paradigm of utility computing is a great way to think
about this stuff.
And of course once you do get the pool filled up with water eventually, then
you can enjoy this big pool water without excessive load on the local water
utility. And I suppose the same goes for office 365:
If you do manage to get a big table up there, and correctly set up, then
you can pretty much enjoy that table without an excessive load on their
The above also means that if you have to often or rapidly fill a big pool of
water than your garden hose connection to the water main is not going to
solve this issue for you, and if you're using 365, that's not going to be
your best choice either.
As noted, thus you better had your designs, your table structures, and
everything you need setup in those tables and hopefully start out with well
under 5000 records. And to be honest, I'm sure there was a day when you
started out with your access application that the tables had very few rows
of data in them, and over time they grew.
Or perhaps you're starting a new application, and therefore you're not
having to worry about these data limits and all.
And I suppose having experienced on mainframe computers, or even multi user
small business systems in which they were quite sensitive to the amount of
data, processing, and memory you could use.
The problem here is often due to the desktop having probably in excess of
1000 times the computing power and processing ability that we had about 15
years ago, then many people have a complete warped view of reality of what
type the resources they have available even when using a mainframe or large
The problem is when you start moving back to a mainframe or centralize
server based system, then the amount of processing that can be allocated to
each individual user drops back to what was very much like the early days of
So keep in mind while there are long time historic issues with a 5000 record
that was a "general" recommend over the last 10 years with SharePoint
, and then there is the 5000 record limits in place for office 365, and they
are NOT related to the longer history limits.
So the above longtime historic limit, was never really any real limit. And
even back in the 2003 days, and using a Microsoft's Office live small
business (the successor to office 365), I was able to push Access tables
2003 in the 80,000 record range.
However office 365 is a different matter, and this is a different set of
>If that is the case, does anyone know of any SharePoint hosting companies
>in the UK that do not charge the earth for managed hosting?
The above is a great question. I'm not from the par the world, so I have no
idea. However, there is an incredible lesson here to utilize and make note
of when you mention the high cost of such sub hosting.
You would HAVE TO BE LIVING IN A CAVE if you've not noticed every single
computer magazine, every single industry trade publication, and just about
EVERY CEO from Apple, Microsoft, Amazon, Oracle, Google etc. are ALL SINGING
THE DANCE of this new cloud computing thing!
The REASON WHY these people are so big on could computing is because cloud
computing means VERY LOW COST COMPUTER SERVICES just like utilities offer
for water or electricity.
Although to be honest these days, a lot of cities have jacked up electricity
and water rates not because the cost of water and electricity has increased,
but this being used as a means to increase city revenues without causing a
So I want to exercise some caution here in stating that office 365 can
handle some pretty amazing applications, but like any new technology, some
caution is required. I would not stand here and state that office 365 is
for only simple applications.
Of course such attitudes are nothing new in this newsgroup. I think for more
than 10+ years we've all fought the idea and concept that Access is a tory
and cannot be used for any kind of business applications. So I think we've
all suffered the other side of this calling, and I daresay that often a lot
of people make the same limited statements about office 365 without
knowledge and understanding of what they're doing.
Right now taking an application with say 20 tables and lots of them with
relationships, and tons of VBA code? If most of the tables are below 50,000
records, then such applications will work quite well.
And just like some users can get Access to work well for 10 users, we see
some you're complaining that their application runs too slow with 1 user!
And speaking of using Access on a network with a split front end and a back
in folder file share on the server? (so without SQL server, and without
We had reports in of people managing to run in excess as 100 users! Now
course this is an environment in which everything was absolutely perfect
And today with SQL server having nice free editions, I think a sweet spot
without any kind of server is about 10 users on your local network, and I
think the start hitting about 20 users, then I think while performance can
be adequate, today it makes a lot of sense to utilize SQL server.
And I dare say in those environments in which the application fits into
using office 365 as the backend, then I think again those 10 to 20 users
will have not have any problems at all.
And as pointed out due to the NEW Access/SharePoint local data caching
system, there are a number scenarios in which Access to SharePoint backend
database are FAR BETTER of a choice then SQL server. In other words you run
a report on a table with 20,000 records, and you have a "poor" Internet
connection then with Access and SharePoint you don't suffer any bandwidth
cost or issues â€“ the report runs instantly and locally without any data
transfer required at all. Such report the SQL server can be extremely
problematic when you're running over an Internet connection to the access
Another major reason why the setup works so well, is that access and
SharePoint are designed with the new world of the Internet and web based
types of connections. Those connections are not always stable, and are
often intermittent, and therefore in such environments once again access and
SharePoint runs absolute circles around the same setup with SQL server (SQL
server right now unfortunately does not take well to those breaks and
connections that so often occur when using an Internet type of connection).
In other words this new Access choice is a modern technology that gives you
a lot more leeway and as noted in some instances the performance absolutely
runs circles around the same setup utilizing SQL server.
On the other hand like anything else, this is a question of the right kind
of horse for the right kind of chore.
A person can certainly use cloud based editions of hosted SQL server (SQL
azure), or even pursue low cost web hosting of which a good many providers
allow external connections to the database. (I still do this setup for some
At the end of the day the best scenario for all of these cases is to do some
initial testing and some leg work to find out what works what does not.
As I've pointed out, some people recommend for anything more than two users,
then you'd be crazy to use access on your local network and you have to
adopt SQL server. Well I don't buy that recommendation!
And the same thing goes for office 365. The amount of typical type of Access
applications that can now work with office 365 is quite large. But this is a
So just like an accDB back end or SQL server is not the solution for
everything, the same goes for office 365.
I can remember companies paying about $40,000 dollars for a 10 user multi
user Computer System, and around the capacity to handle about maybe 25,000
rows of data.
Today you can take 10 laptops, send them out the door, and use office 365
for mere $6 per month, and be off to the races in less time than it took me
to write this post out. So for some this might not be a big deal, and for
others at this low cost?
This is nothing short of a miracle.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada