Received: by 10.66.86.133 with SMTP id p5mr3519755paz.12.1348968636294;
        Sat, 29 Sep 2012 18:30:36 -0700 (PDT)
Path: t10ni23595808pbh.0!nntp.google.com!npeer02.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!post01.iad.highwinds-media.com!newsfe09.iad.POSTED!not-for-mail
From: "Albert D. Kallal" <PleaseNOSpamkal...@msn.com>
Newsgroups: comp.databases.ms-access
References: <d7beb33f-8a71-4052-bcf2-709d992a55c0@googlegroups.com>
In-Reply-To: <d7beb33f-8a71-4052-bcf2-709d992a55c0@googlegroups.com>
Subject: Re: Access and Office 365 - limitations
Lines: 32
MIME-Version: 1.0
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 15.4.3555.308
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3555.308
Message-ID: <%gN9s.1132$Jw4.1021@newsfe09.iad>
NNTP-Posting-Host: 68.151.51.125
X-Complaints-To: internet.abuse@sjrb.ca
X-Trace: 1348968635 68.151.51.125 (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
X-Received-Bytes: 24276
Content-Type: text/plain;
	format=flowed;
	charset="utf-8";
	reply-type=original
Content-Transfer-Encoding: 8bit

"Paul H"  wrote in message
news:d7beb33f-8a71-4052-bcf2-709d992a55c0@googlegroups.com...

>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
>lists.

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
face!

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 
share
point!

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 
edition).

In a way, I'm am much happy and better off that the older SharePoint setup 
did not
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
>a browser

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 
and SharePoint:

<quote>
Access and SharePoint Access 2003 - maybe 2-5% of applications would work 
with SharePoint
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.
</quote>

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 
cloud computing?

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.

http://www.kallal.ca//Wan/Wans.html

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 
not grasp?

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 
issue).

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!

Why?

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
>of use?

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 
records.

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 
servers.

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 
system.

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 
PC desktops.

So keep in mind while there are long time historic issues with a 5000 record 
limit
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 
from
2003 in the 80,000 record range.

However office 365 is a different matter, and this is a different set of
issues here.

>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 
tax revolt.

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 
SharePoint)

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 
setup.

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 
front end.

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 
clients).

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 
relative term.

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
PleaseNoSpam_kal...@msn.com