Microsoft Office 2010 Professional Access 2007 lin

Today’s guest writer is Aaron Hafele from the Access Product Support team. In previous editions of this blog,Microsoft Office Enterprise 2007, Clint has pointed out a couple of things that, as a support engineer, I believe bear repeating. One in particular has to do with performance when working with linked SharePoint lists in Access 2007. Way back in December of 2007,Microsoft Office 2007 Professional, Clint had this to say about SharePoint performance: “The team spent a bunch of time improving SharePoint performance but it still is influenced by the speed of the connection to the server, server performance, and size of the data. I have found working with SharePoint data offline and using the synchronize command a far better experience. This works much faster unless you are working on data that everyone else is changing. The conflict resolver UI gives you the chance to override server changes or discard your own changes. In the future, Access should provide the option for merge changes. Oh well, something for vnext. SharePoint also has a flag called Cache List Data. This stores a copy of the list data in the database file. When the database opens the Access database engine only requests changes from the server. It is a nice performance win for the client and server. One last thing… If your list on SharePoint is getting really large—you might consider linking to a view and not the list. This works well for scenarios where you have archived data in a table that isn’t necessary for your view. There is a trick—you can’t bind to the view using the UI. Use the TransferSharePointList method to create the link table. It has a property for the view.” What we have seen in some cases, though, is that Cache List Data,Microsoft Office Professional Plus 2010, as well as Work Offline, can themselves break down in scenarios where response times from the SharePoint site are too low. This is frequently a transient condition, coming and going throughout the day as the load on SharePoint, and/or the network at large, ebbs and flows. Conveniently enough,Microsoft Office 2010 Professional, Clint has also provided a potential workaround to this problem as well: use a view. I say “workaround” because the real “solution” is to solve the SharePoint performance problem(s). As an Access developer,Microsoft Office Professional 2010, though, this is often beyond your control. When Access 2007 requests list items (rows) from SharePoint, there is a finite amount of time that it will wait for that data to be returned. Specifically, after 30 seconds, if Access doesn’t have the data, it will send another request. If the data is not returned in a sufficient time frame after three attempts, Access gives up all together. This means that your data is not cached, or that one or more lists may not successfully be taken offline. To avoid this situation, then, you can use SharePoint list views to reduce the amount of data that Access will request. An important point to keep in mind here is that Access will only request up to 2,000 rows at a time. This means that a list that is 10,000 items long will require five requests for data, increasing the opportunity for a failure. By using a view to reduce that 10,000 item list down to 500 items, for example, you then greatly increase your chance of success. Ideally the view will have <2,000 rows. This is a SharePoint recommendation, not just an Access one. Also mentioned in a previous blog post, is the use of Fiddler to gain insight in to what’s going on between Access and SharePoint. If you suspect that you may be experiencing the issue I’ve described, try monitoring the traffic with Fiddler and see if there are multiple requests for the same data. If the responses to those requests take longer than 30 seconds, then you’ve identified your problem. Hope this helps, Aaron <div