MS Access 2007 & SharePoint Performance

[Glynn]

Access 2007 does fine connecting as a client to SharePoint linked lists
(WSS3) remotely in certain situations but perhaps someone could help improve
my understanding on performance issues.

There seems to be a performance degradation (to say the least) the larger
the linked list. For example, if I use a Delete all query in Access on a
SharePoint linked list, it takes FOREVER, even if the list is about 100 rows.


Does anyone know what goes on underneath the processing of a list on
SharePoint? How does Access and Sharepoint actually communicate with each
other?

Is it true that SharePoint operates on one row at a time?

Is the processing of a SharePoint list somehow done locally on the client
machine, despite the data residing remotely on a SharePoint list, i.e. does
it download a copy of the table, perform the operation, and re-sync with
SharePoint?

Would a VB.Net application with a remote web reference to SharePoint
encounter the same performance problems on larger lists?

Is there a more efficient method for list/table processing between Access
and Sharepoint? It seems that if Access displays a larger list the
performance is acceptable - just the delete function is my biggest problem.

Anyway, if anyone has some time I would appreciate any help or guidance.

 

[S.Clark]

If you have the permissions, setup a Trace on the SharePoint SQL Server
machine, and you can watch what happens. Report back what you find.

Alternatively, try using a PassThrough query? (untested by me.)

 

[Glynn]

Hi,

I do not have the ability to setup a trace on the Sharepoint SQL Server. My
enviornment is the following:

An Access 2007 client linked (via broadband cable, Roadrunner) to a remote
SharePoint site hosted on Microsoft SharePoint Online.

I looked at performance tips to speed up Access performance and one
suggestion was to have a persistent connection to the linked database by
opening an Access database variable in VBA using the DAO OpenDatabase method
and to keep this variable open as long as my application is running. However,
this approach describes opening a back-end .mdb database. Is there an
equivalent for a remote SharePoint link?

The delete query I am using is a delete all query on a table that yields the
SQL: "DELETE ReportOpenInvoices.* FROM ReportOpenInvoices;" where
ReportOpenInvoices is a table with an autonumber primary key (ID). Is there
a more efficient method to write the query? There are about 100 rows and it
takes about 45 seconds.

It is my understanding that SharePoint is built on top of SQL Server. Is
that true? If so, it would seem that a bulk delete would not encounter a
performance problem unless there was single row processing/acknowledgement,
but I am at a loss here.

The pass-through query seems to want a DSN name. If the SharePoint site is
hosted by Microsoft Sharepoint Online, how is the SQL Server name determined?
(That question may be a totally inappropriate considering my expertise, or
lack thereof).

Again, I so appreciate your help and guidance on this subject, as I am
trying to use Access as a front-end connection to Microsoft SharePoint
Online.

If Access 2007 is not an appropriate tool for this strategy, then please let
me know.

 

[S.Clark]

Access should be the right tool for the job, at least that's what they've
strived to do with it.

To maintain the open connection, open a global variable to one of the linked
tables.

[Put this in a public dim area]
dim grs as dao.recordset

[Put within some code when the database starts]
set grs = currentdb.openrecordset("tablename")

I assume a DSN can be made to hit the SHPTOL site. Contact their help
directly to see what they have to offer.