Converted Large Access App to Hybrid

I haven’t been here for a while – busy, busy and since we are in that gray area between the announcement of a new version and the actual shipping of it there isn’t much reason to spend a lot of energy yet on the issue of Office 2010 (sorry MS).

BUT ….. I have just completed the conversion of a large Access application from one that used SharePoint Tables as a data source in SP 3.0 to one using Access 2010 and Access Services in SP 2010 with great results.

There are so many little things that I had to change because of the conpromises I had to make in A2007 using SharePoint that completely fail in A2010 and Access Services.  But there is one issue you have to deal with and that’s Action queries and the use of TEMP Tables.

In order to cut down on roundtrips to the SP server in my 2007 app I had primary keys of selected recordsets written to a local Temp table inside my ACCDB file.  While there were probably more “Pretty” ways to accomplish this, it did work for me and was easy to implement. 

If however you are using Access 2010 with Access Services you have to totally avoid this technique because Append queries of more than a few records will cost you in performance – especially across a VPN.  The solution is of course to use either Filters on the Form or is necessary your can redefine the Recordsource using code – no problem.  

This is a perfect example of how things that have “just worked” for years sometimes get broken in new versions.  But in this case the benefits of Access Services are so huge that it’s totally worth the effort.

Dick