Office 365 - Using SharePoint as a 'Backend'

I have been playing with SharePoint within Office365 and my initial
bewilderment is slowly being replaced by bouts of confidence and
extreme optimism. I need calming down, so I have a few questions:

1. I keep reading that there is a list limit of 5,000. I don't get
this at all - I have migrated a table with 25,000 rows up to
SharePoint , I have run queries and played with the data from within
Access and had no problems. What is the list limit referring to?

2. Exactly how does the “offline mode” work? Do I need to set it up? I
understand that table data is initially downloaded from SharePoint
lists to my Access db. I presume that when I update a row in Access,
that update is sent up to SharePoint immediately. So if I lose Wi-Fi
signal, does the sync silently fail and then continue to keep trying
at regular intervals? If so how often? Can this be adjusted?

3. Would you trust a large, complex Access application to SharePoint
lists? I.e. lots of tables and relationships and lots of data? I know
you can apply referential integrity to lists, but are they
generally...er...robust? (No doubt any SQL DBAs would laugh at that
question coming from an Access DBA ;-P)

4. Performance should be great even with lots of data right? Because I
no longer need to worry about a FE/BE setup anymore because all of the
data is local within Access. So I could just put a copy of the Access
DB on each user’s desktop and let them sync to SharePoint
independently….right? I just need to Block {F11} and do the usual lock
out stuff to stop them tinkering and I’ve got a lightning fast,
secure, synchronised, desktop app….right?

5. Does all of this sound too good to be true, or is it just me?

6. What’s the catch?


Paul

"Paul H" wrote in message
news:8f8ef769-a440-4031-8a20-6f484e65ef7d@37g2000yqu.googlegroups.com...

>1. I keep reading that there is a list limit of 5,000. I don't get
>this at all - I have migrated a table with 25,000 rows up to
>SharePoint , I have run queries and played with the data from within
>Access and had no problems. What is the list limit referring to?

This issue can get complex. However, I tested table with 80,000 records and
that table worked fine.

Also, what limits exist beyond 5000 records is not the same with hosted
SharePoint, or your local server.

(so keep in mind that cloud computing which office 365 is based on is not
the same as as purchasing hosted SharePoint, or running SharePoint on site).

There are several reasons for these differences but one is computing as an
utility (cloud) has more limitations and more processing governors built
into that system so one user cannot impact that whole system.

So some of the limits based on 5000 records are "defaults" for SharePoint,
but some are changed for office 365. And some of these limits are far more
serious when using Web Applications on 365.

While many of these limits are defaults, and many of them can be changed
with hosted solutions or if you run SharePoint on site, you CAN NOT change
these settings when using office 365.

Now, the 5000 limit?

There two significant limits in regards to 365, and one is "close" to a bug.

If the list is LARGER then 5000 records, then you cannot change (add)
indexes. Worse, if you have a un-published table of 5010 records and you
setup indexing on Company Name and last name and invoice Number, then when
you publish to 365 the data goes up fine, but THEN when Access attempts to
set the indexing it fails and fails without a message.

So ONCE a table grows beyond 5000 rows, you cannot set + add new indexes
with tables on 365 (this is limit that is changeable on hosted or your own
SharePoint server). This means you BETTER get the table and index setup
BEFORE the table grows over 5000 records. And this also means you can get
around this limitation. Create the table first, setup all indexes, then
publish and THEN APPEND the data (so for more then 5000 records you can get
a table up to 365 with indexing). As noted, once published and the table is
> 5000, then you cannot add new indexes (I guess they fell this hurts the
cloud system too much ).

Next issue:
for a web query, the limit is 5000 records "at a time". Now "at a time" has
to be given a thought. If you do a join between two tables, and you return 2
customer records (say for today's invoicing date), and you join this table
to a child table of invoices, without a index on invoice number (child
table), then ALL rows need be scanned - if this number is > 5000, then the
web query will fail. (however, a client query inside of Access will work).

So there are several limits and issues based on this 5000 limit. And prior
to 2010 without the local new cache mode, then 5000 records was a LOT to
drag over the network, and it also a large chunk of records to have to pull
into memory anyway.

>2. Exactly how does the “offline mode” work? Do I need to set it up?

No, you don't have to do anything. However, make sure you do have index's
setup on any of the child tables and FK's. This is normally the case, but if
the Access UI does not show the indexing set in the ribbon (table view - not
design view), then check the indexing box on the ribbon.

> I understand that table data is initially downloaded from SharePoint
lists to my Access db. I presume that when I update a row in Access,
that update is sent up to SharePoint immediately. So if I lose Wi-Fi
signal, does the sync silently fail and then continue to keep trying
at regular intervals? If so how often? Can this be adjusted?

The "lack" of adjusting and control from VBA is a problem - I wish we had a
few additional commands. Once Access "kicks" into off line mode, it stays,
but will give messages and options to the user to go back on line. Another
way is to exit Access, and then re-enter. And there options in back stage
also (click on file = backstage).

For a web front end (or even a web front end that is 100% vba, then we have:

'DoCmd.RunCommand acCmdSyncWebApplication
'DoCmd.RunCommand acCmdSynchronizeNow
'DoCmd.RunCommand acCmdSynchronize
'DoCmd.RunCommand acCmdRefreshData

However, none of the above commands work if you using a non published front
end. The command to trigger a sync in that case is this command:

DoCmd.RunCommand acCmdRefreshSharePointList

And I don't know if above needs the table to be open first - but the above
is what I code has "filled in" for my re-sync routines right now.

>3. Would you trust a large, complex Access application to SharePoint
>lists? I.e. lots of tables and relationships and lots of data?

Lots of data is a realize term. I seen posts here where they consider 4000
records huge.

If the application is a data processing intensive application, then I don't
think this setup will work. And for applications that have to update lots of
rows, then I think the cloud based edition of SQL server is a better choice
(since you can send commands that update large number of rows and have them
run server side without bandwidth issues.

Because in Access and SharePoint that all data updates MUST occur local,
then this knowledge has to be taken into account as to what kind of
applications will work and those that do not. the problem is you cannot
update data server side without incurring bandwidth issues due to the local
cache. So if you call a server side store procedure (a data macro) to
update 1000 rows, that update will occur server side, but THEN the access
client has to sync with that updated data anyway (so as I said, you data
have to reside on both sides). This knowledge means some things with this
setup runs circles around SQL server, but it also means the reverse - some
things are much worse and you cannot update server data without the data
coming down the pipe (sooner or later).

> I know
>you can apply referential integrity to lists, but are they
>generally...er...robust? (No doubt any SQL DBAs would laugh at that
>question coming from an Access DBA ;-P)

There is nothing suggesting that this setup does not work fine. I mean is
possible that when you use a cloud based version of oracle and you delete a
record the data is sent to a zoo in Africa where monkeys read some lights on
a board, make the decision to pull a level to delete the data, the monkey
then gets a grape and then your data is sent back. So how things work behind
the scenes are really not that important in the past and even now.

I mean, I used multi-value databases for years. And having used those
databases means that deleting a record in SharePoint with a multi-value
column IMPLIES a cascade delete in a relational model. (so same effect, but
one has to occur based on the physical nature of how the data is stored.
However, both the xml database and the SQL relational database WILL do a
logical delete of the child records in this case and thus they are math
equivalents).

So I do think for larger databases, and larger data sets, much caution and
testing is required - and based on the above information one can now
logically deduce and figure out what kind of applications will work well and
which ones don't.

>4. Performance should be great even with lots of data right? Because I
>no longer need to worry about a FE/BE setup anymore because all of the
>data is local within Access. So I could just put a copy of the Access
>DB on each user’s desktop and let them sync to SharePoint
>independently….right?

Yes, but then again it depends on the application. If that application has
to update 100,000 rows, with sql server that is one update command that runs
server side without any communication and bandwidth required to the clients
connected to that application. With 10 users and doing the same thing with
this off line "sync" or so called replication model, then you have 1 million
rows of data traffic occur. So how this works and the ups + downs are the
same as an replication model. So I suppose this is common sense if one
understands what is going on under neath.

If the application does not involved processing (updating) lots of rows at a
given time, then such applications are suited to this replication model. So
it not a cure all.

I stared using the free edition of SharePoint (office live small business)
with 2003. I would say this:


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.

So the doors have vastly opened up here since 2003, and 2010 become all of a
sudden rather useful for a lot of applications when in 2007 as such would be
out of the question.


There is still a consider way to go in terms of optimizing the client
setup - some of the local indexing does NOT seem to be utilized with this
setup - I don't have time to inquire and test and ask why this is so - but
it is one of my issues + questions I want to bring up with the Access team.

>5. Does all of this sound too good to be true, or is it just me?

>6. What’s the catch?

It is amazing, it is too good to be true and with office 365 starting at $6,
we been handed a really nice system that opens all kinds of doors.

By the way, a good portion of the above comments are from notes for upcoming
book materials.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
PleaseNoSpam_kallal@msn.com