[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 883
  • Last Modified:

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?
0
rick_danger
Asked:
rick_danger
3 Solutions
 
Bill RossCommented:
Hi,

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.

Regards,

Bill
0
 
Dale FyeCommented:
Bill,

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.
0
 
Bill RossCommented:
Hi,

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.

Regards,

Bill
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Richard DanekeCommented:
Rick,
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)
0
 
Richard DanekeCommented:
Here is a link to someone else's pages on Access and SharePoint

http://accessexperts.net/blog/
0
 
rick_dangerAuthor Commented:
All comments were gratefully received, thank you. Sorry for the delay in getting back, I've been on holiday
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now