Problem connecting exsisting SharePoint list with Microsoft Office access 2007 database

Error message:

You do not have the necessary permissions to use the ‘<ListName>‘ object. Have your system administrator or the person who created this object establishes the appropriate permissions for you.

Problem description:

When you try to add an existing SharePoint list to a Microsoft Office Access 2007 database then you receive an error message that resembles the following:

Resolution:

·         Checked the number of groups which are present on the site and user permissions on the site as well as for the specific list that we trying to connect

·         User has Full control on the site as well as for the list also so problem is not from SharePoint end

·         Checked the office application installed on the system and found multiple version which consist of office 2003 as well as office 2007

·         Checked the access version and found out it belongs to Office 2007 suite

·         Checked on my machine to reproduced the same and we are able to reproduced it so the problem is not from the Office suite

·         User conveyed that it’s working on one of the SharePoint site and compare both the sites and found out some differences in terms of columns and characters but to make sure did research and found the Microsoft KB

Microsoft KB states that it’s a known issue. Details as follows:

Cause:

This problem occurs because the Access 2007 database engine disallows columns that have names that contain more than 64 characters.

Workaround:

To work around this problem, use only column names that contain less than 64 characters in SharePoint lists.

Resolution for this issue:

There was one column which has more than 64 characters, we shorten the column name and tried connecting the list to access and it works without any issues.

How to reproduce the problem:

·         In a SharePoint Web site, click Lists in the View All Site Content pane.

·         Click Create, and then click Custom List in the Custom Lists column.

·         In the Name box, type Test List, and then click Create.

·         Click Settings, and then click Create Column.

·         In the Column name box, type NewColumnWithColumnHeaderLengthOfMoreThan64Characters1111111111111111111111, and then click OK.

·         Click New, type First Value in the Title box, type Test Value in the NewColumnWithColumnHeaderLengthOfMoreThan64Characters1111111111111111111111 box, and then click OK.

·         Start Access 2007.

·         Click Blank Database in the New Blank Database section, and then click Create.

·         Click the Create tab, click SharePoint Lists, and then click Existing SharePoint List.

·         In the Site Address list, select the Windows SharePoint Services Web site in which you created the Test List, and then click Next.

·         Click to select the check box for the Test List, and then click OK.