Using Sharepoint for Access back end

I'm starting a Access 2010 project where they have informed me that they wish to use Sharepoint for holding the shared tables.

Is there an easy way of doing this, or is there a good source of information on this?
Who is Participating?
Dale FyeConnect With a Mentor Commented:

Don't know where you are getting your info about SharePoint lists not being indexed and no PK.  Although there are issues (several listed below), PK and indexes are not among them

- Performance has historically gone down significantly when a table contains more than 2000 records
- No referential integrity
- No relationships

I have been using SharePoint lists as backends for Access applications for some time.  I generally build the tables in Access and migrate them to SharePoint.  If you fail to create an autonumber PK in your Access table before migrating it, SharePoint will automatically add one (Field name : ID) and if you already had an [ID] field, it will change that to [_ID].

Indexes you create in Access are not migrated when you migrate the tables to SharePoint, but you can open the list in SharePoint and assign indices for the table.

I have not used Access 2010 with SharePoint 2010, so I don't know whether there are changes in those two products that make the process easier.

If I had my druthers, I would use SQL Server or SQL Server Express over SharePoint, but again, I don't have any 2010/2010 experience, so if the marketing hype is to be believed, it is easier in 2010.
Bill RossCommented:

I've used SharePoint as a back end and using the inked table manager you can connect Access to a list.  Beware there is a huge performance issue!  SharePoint lists are not indexed nor is there a PK.  In my experience it's a very bad idea.  There are also data type conversion issues.

If you want to have a SharePoint list you should use the SharePoint designer to build the user interface and to update and manage the list.  You can use Access to do the reporting.


Bill RossConnect With a Mentor Commented:

fyed is correct - SharePoint does assign PKs you just can't use them to link tables.  I have used SharePoint for years, including 2010, and must tell you that linking to lists is very slow and development is cumbersome.

I would still suggest you explore using MS Access or SQL.


Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Richard DanekeConnect With a Mentor TrainerCommented:
There are design issues to move to SharePoint for storage.  You did not mention, how many active users?  or, how many tables? or, how many Access users?

Access 2010 will let you generate Access web services.  The browser-based application can reside in your SharePoint.  Access is not required to run the apps.  Calculated fields and table macros can be designed to reduce code and query requirements.   Web-type navigation bars can be placed on the Access web forms.

But, Access Services has to be part of your SharePoint installation.   You can create a Web-based Access template to test your installation.  (Access 2013 will be marketed to create SharePoint systems)
Richard DanekeTrainerCommented:
Here is a link to someone else's pages on Access and SharePoint
rick_dangerAuthor Commented:
All comments were gratefully received, thank you. Sorry for the delay in getting back, I've been on holiday
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.