Deploying Access 2007 Databases with SharePoint 2007

SharePoint 2007 provides, though Excel Services, some really nice ways to share workbooks, but it doesn’t offer the same sorts of integration for Access databases. Evidently, this gets fixed in SharePoint 2010, through i’s new Access Services feature. But I’m stuck with SharePoint 2007 for the time being.

A lot of the databases I create and support are split databases. These consist of two *.accdb files. One is the back end, which contains all the tables. The other is the front end, which contains everything else and only links to the tables. Many developers add a “be” or “BE” to the filename of the back end file. I do this and also add an “fe” to the filename of the front end file. The back end goes on a network drive. A copy of the front end goes on each client’s machine. You use Access’s Linked Table Manger to point the front end to the tables in the back end. For any of this to work, of course, the network drive has to be mapped under the same letter on each client machine.

This splitting is a best practice when using Access databases with multiple, potentially concurrent, users. It helps to stave off database corruption. That’s the up side. On the downside, it makes the job of deploying he database, initially and after you make any changes to the front end, quite a lot more difficult (unless you have some super-fancy deployment tool and admin access to all the boxes in question, which isn’t a luxury I have).

After a little testing yesterday, I discovered that if you upload the front end to a SharePoint document library, leaving the back end on a network drive, they can talk to each other. The trick is to open the front end, counter-intuitively as this may sound, as read-only. You do this in SharePoint simply by clicking on the front end’s file name in the SharePoint document library (just as you would do with any other file in a document library). The database will be read-only with respect to everything except data in linked tables, which is all we care about, in this scenario.

When I first tried this, I thought it would defeat the purpose of splitting the database. But, as far as I can tell, it doesn’t. When you open the database front end from SharePoint (via Internet Explorer 8, at least. I haven’t checked with other web browsers yet), it caches a copy of it locally, in Windows XP’s Temporary Internet Files folder. If the front end is named fooFE.accdb, the cached copy is stored here:

C:\Documents and Settings\<username>\Local Settings\Temporary Internet Files\Content.MSO\<some semi-random eight-characterstring>.accdb\fooFE.accdb

It stores the lock file (e.g. fooFE.laccdb) in the same location. The location of the Temporary Internet Files directory can be changed. Wikipedia has some useful info on where you might find it, if you’re curious, though I’d suggest you don’t go mucking around with IE’s temp files unless you know what you’re doing.

A lot of testing remains to be done, but what this potentially offers me–and anyone else running SharePoint 2007–is a potentially better way to deploy Access 2007 (and, perhaps, Access 2010) databases to small teams, while keeping the data itself separate. If it does work, it will make upgrading the front end a good deal easier, as I’ll only have to publish a new version to the SharePoint document library.