Integrating Access 2007 with SharePoint

greenprg used Ask the Experts™
We have an Access 2007 database that we want to "integrate" with SharePoint.  I know very little about SharePoint but our SharePoint administrator has been describing how SharePoint can work with our Access database (a SharePoint server was installed about 6 months ago).  

To make a long story short, I only heard what we could do with SharePoint and after months of talking about it, our administrator/SharePoint guy was unable to get much of SharePoint to work with our Access database.  So now I have an Access database that was programmed around what he described and is pretty much complete but the interaction between Access and SharePoint is still a mystery to me because I haven't seen any of it.

So my question is
- All the tables, forms, queries, and reports have already been coded in Access 2007.  How complicated is it to use SharePoint as the entry and exit doors to the Access database to keep track of the workflow tasks and when each assigned person has completed their updates in the Access database?

Our workflow is as follows:
Configuration Manager -> Engineering Lead -> Tool Designer -> 6 departments in parallel for labor estimates
Once all 6 departments have completed their department's labor estimate then the engineering lead and program manager are notified of the finished estimate.

I've also heard the database can be imported into SharePoint as Lists and EVERYTHING could be done through SharePoint.  Depending on the development/programming time for SharePoint development, I don't want to duplicate the development time that has already been invested in Access unless there is an overwhelming reason to do so.  

We received an estimate from a consulting firm to do the SharePoint programming and they came back with an estimate of over 200 hours.  For 200 hours, it seems like it could be completely re-written in SharePoint.  Is it more complicated to use SharePoint with Access than it is to just use SharePoint?

Your recommendations are greatly appreciated.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Sharepoint with Access 2007 was never a viable platform, at least to me. I tried it with several clients and while we always got it to function, it never really worked to the extent that it would be a line-of-business application.

SP programming is much more complex than is standard drag-and-drop Access programming. It would not surprise me at all to find that a moderately complex application would take 200 hrs (2 men * 2.5 work weeks, basically).

I'm not sure how to answer your specific question:

"How complicated is it to use SharePoint as the entry and exit doors to the Access database to keep track of the workflow tasks and when each assigned person has completed their updates in the Access database?"

It's as complicated as it needs to be. While I know that doesn't really answer your question, it's really the only answer we can give.

Note also that Access 2010 is much easier to work with in the SP environment. The Access team has made very strong advancements in this area for 2010, and the improvements will only continue in newer versions.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
I have several Access 2007 / MOSS 2007 applications that use SharePoint as the backend and Access as the front-end.  Although there are some issues with using SharePoint as your backend (the biggest being no referential integrity).  Although I don't recommend using an Access database as the backend over a WAN, these applications are running at about a dozen sites from Arizona/Colorado to Virginia, with the SharePoint server sitting in Virginia.  Only one of the sites has significant lag times for data retrieval, and that site has a really narrow bandwidth and is slow for all internet traffic.

If that is all the functionality you need, then you can export your Access tables to SharePoint using the MoveToSharepont option in the External Data -> SharePoint Lists ribbon.

Before you do this, you need to make sure that if you have an autonumber column in any of your tables, that that column is identified as your primary key.  Otherwise, when you move your data to sharepoint, it will create a new primarykey autonumberID field.

And always, before you do anything, make a backup of your application and data before attempting anything along these lines.  BACKUP! BACKUP! BACKUP!


Thanks for sharing your insights and experiences.  I guess the jury is still out.  I know it was a big question but I was hoping to hear from someone who had gone through this already to lead the way.

Thank you for your responses.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial