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.
Hi,
Thanks for your post. Have you tried the opposite: upload the back end (_be) file to SharePoint 2007 and link to it with the front end copy of the Access 2007 database on your machine? I get the error message “Not a valid filename” when I try to do so. I for sure can access the _be file on SharePoint by double-clicking it. I tried to different methods using the Linked Table Manager in Access 2007: (1) browse/navigate to the file over the network manually via “My Network Places” and (2) right-click the file on SharePoint site, select “Copy Shortcut”, and paste the shortcut link into the “File Name” prompt. No luck either way! (Same error message). Any idea why this may be happening?
Cheers,
Derek
Derek,
I’ve tried putting the back-end and the front end in the same document library, and that works. I think your method could also work so long as you use UNC path names rather than drive-letter based names (which is what you’ll get if you browse). In Internet Explorer, if you’re viewing the document library that contains your back-end file (and that document library has the full toolbar displayed), you can choose Actions and then Open with Windows Explorer. Assuming that, in Windows XP, you have Folders Options, View, Display the full path in the address bar enabled, you should be able to read the UNC path from the address bar (e.g. \\theservername\path\to\your\doc\library\). Then you can tack the name of the “_be” file onto the end (e.g. \\theservername\path\to\your\doc\library\foo_be.accdb).
Let me know if it works. I’m curious.
I have a split BE/FE database on our network, and wanted to try your method described above. I was extremely happy to find out that it worked!
Here is a twist…we can access our sharepoint offsite, so I assumed that it would work the same as it does within our network. When trying to open Access in read only, an error message was received that stated the DB’s location was not a valid path. “You cannot link to a database that is store on an Internet location. You should first save the database to a location that uses a Universal Naming Convention (UNC), such as a server share, and then link to it.”
It will not allow us to upload to sharepoint and use that. When linking to another spot on a shared server, it says the path is wrong because it shows UNC location. (Circular problems here.)
Does anyone have any ideas? Thanks in advance for any thoughts you might share.
Kathy,
I’ve only used SharePoint from offsite via VPN + Remote Desktop Connection, so I can’t speak from experience on this one. Are the links in the linked table manager by drive letter or UNC path? I think it won’t matter, because (unless there’s an existing VPN connection) there won’t be anything at the path in either case (from the offsite computer’s perspective). Of course, if you have VPN/RDC or can use LogMeIn or something, that might be a workaround.
Hi James,
Have you done any additional testing? I thought about this and my search returned your page. Now wondering if I should go down this path or not. My users – about 25 people are global and all have access to SharePoint 2007. Have you done any testing with other browsers (Firefox, Chrome, Safari)?
Any other dos and donts you have discovered?
We’re using it with a few databases, but it’s a very homogenous user base: mostly WinXP or Win7 running some flavor of IE. SharePoint 2007 is pretty lack-luster in Chrome, though I sometimes run in via the IE Tab extension (on Windows).
Biggest problem we’ve had with it is people checking out the front-end file (rather than just opening it) as all our doc libs require checkout. I think putting the front-end in a doc lib that doesn’t require checkout would be preferable. Otherwise, you just have to train around it.
Thanks for the quick reply. How did you put both the backend and frontend on the document library. Putting the front end on the document library works so far but it’s linking to backend sitting on a server. Did you split the database on the document library?
Sorry one last thing FYI. Although the SharePoint I am using the versioning doesn’t require checkout but user still gets prompted to open the file as read only or edit.
I hope my question isn’t too basic. I’ve just returned to industry after working in an academic environment for the last 5 years. (As a lecturer) The company is small and as I don’t know mysql and php, but do have experience with Access; I thought the best way to go would be to develop the system using forms and databases in Access and move this on to a hosted sharepoint site so that my users in the 3 locations can have access to the latest data and I will also be able to have access to the newly entered/updated information for my mailshots and decision making and payments.
Is what I am envisaging feasible in your opinion? Or have I missed the point of what sharepoint can do for me.
It really depends upon what version of SharePoint you’re running. SharePoint 2010 has a new feature (only in the Enterprise editions, I think) called Access Services. And it really does get at what you (and I) would like to do: leverage Access skills to create web-hosted database applications. There are some caveats, of course. SharePoint handles forms, reports, and queries differently. So developing an Access database to be deployed via Access Services involves some extra knowledge and work. The biggest perk of Access Services in SharePoint 2010, over any method of using Access in SharePoint 2007, is that Access doesn’t have to be installed on the user’s machines.
If you just want users to be able to consume information, Excel Services, a feature of SharePoint 2007 and 2010. It lets you host an Excel spreadsheet (which you, as the author, continue to edit with Excel). To everyone else, through some JavaScript and CSS magic, it just looks like a spreadsheet embedded in a web page. You can also define which bits of the spreadsheet are visible. You can even create parameters so end-users can try out your sheet with different values (without affecting the real sheet). Essentially, it gives you a way to share information via URL rather than shuttling spreadsheets around. You can use it to build informational dashboards with graphs and key performance indicators (KPIs) and such. It’s a good tool.
But you might have to bite the bullet on this and either higher a developer to create a custom web app for you or skill up on it and build it yourself. If you go the build-it-yourself way, and are a fan of PHP, I recommend using the CodeIgniter framework (http://codeigniter.com/). I used it recently on a project and it sped up development considerably.
If memory serves, I developed the DB locally and split it there. Then I put the back end on a shared drive and updated links in the front-end so the front end pointed to the back end. Once I confirmed that was working, I uploaded a copy of the front end to the SharePoint document library, and then edited the data connections to point at the back end (still on the shared drive). And then I tested that from a few different locations to make sure it was all working well.
I have also run one where the front end and back end were in the same SharePoint document library and it worked, but I haven’t tested it to see if it holds up when multiple people are using it that way.
My biggest problem, so far, has been that people tend to check out the front end from the document library and leave it checked out. There’s no need to check out the front end, unless you’re a developer making a change to the front end. Paradoxically, opening the front end “read only” allows you to edit the data in the back end. The “read only” only applies to design changes, but this is hard to get across to end users.
Thank you for sharing your expertise. I have till Thursday to play and think and make a decision.
Thanks for the article. A co-worker created an Access database that was connected to SharePoint lists. But once the lists accumulated lots of user data, the system became unbearably slow.
I’d like to avoid that by splitting the database and placing both ends in SharePoint. Thanks again for the info.
Glad to help. Can you tell me which version of SharePoint your co-worker was using? I hope you get better performance out of the split db method. I haven’t encountered any performance problems with it so far.
He was using SharePoint server 2007. At the beginning everything was fine. But, as the data accumulated in the lists, it would literally take minutes for the system to a) load b) save a new record c) update. We ultimately moved to commercial of-the-shelf software (COTS).
i have one access table 2010 i have connect to sharepoint 2010
after connecting to share point 2010 its showing this error
The ‘Table1’ is not available.
Click here to try again.
my access database is in local computer.
can you tell me the solution for this
From the details you’ve provided, I can’t tell exactly what’s going on. I haven’t tested this solution with SharePoint 2010. It sounds like your Access 2010 front end can’t find your back end. Following my solution, only the front end goes in SharePoint. The back end should be on a network share. And the best way to avoid problems is to specify it by UNC path rather than drive letter, as, on Windows, drive letters are arbitrary and vary from one machine to the next. UNC paths, on the other hand, are stable.
So, my guess is the problem is one of these: 1) the drive where the back end lives isn’t mapped on the computer from which you are launching the front end, or 2) the path to the back end is specified incorrectly.
Wheat…
I am setting up my first split db. We have a team of about 10 that will use it, and not knowing much about how all this works, my idea is to set up the BE on a shared drive that all can access (lots of remote workers that connect via VPN), and put the FE onto SharePoint.
First question is simple. Will this be ok? Second question is whether or not having the FE read only on the SharePoint is as good or better than distributing versions to their individual desktops. Can multi-users open and use the FE version at the same time? Dumb question…but I am the king of the dumb questions. Seems to have worked for me so far…
Appreciate your help very much.
RB
Ok, so I have split it, and it is now on the Sharepoint (FE), and shared drive (BE). It is REALLY slow to load forms. What should I do? Locate them both in the same place, like both on the Sharepoint?
The main problem I had hoped to solve by placing the front end in SharePoint was one you mention, having to push out new copies of the front end to desktops whenever I update the GUI. Assuming you have any easy way to push out such changes, I would opt for keeping the backend on a file share and installing the front ends on each desktop.
An issue I ran into with keeping the front end in SharePoint is, training be damned, users would check out the front end and leave it checked out.
I’m not sure why that would be happening. I’d suggest bypassing SharePoint and placing the front ends on the desktops and leaving the backend on the fileshare, as it seems something SharePoint-specific is causing your performance issues.