Access Errors Publishing or Exporting to SharePoint when Attachment File Name is Invalid

Issue:

You receive one of the following errors when you attempt to export a Microsoft Access table to a SharePoint list:

There was an error copying data to a SharePoint list. The Microsoft Access database engine encountered an error while communicating with SharePoint. More detailed information: "

-OR-

There was an error copying data to a SharePoint list. Network I/O error.

You receive the following error when you attempt to publish a Microsoft Access 2010 database to a SharePoint Server:

Publish Failed. Your application has encountered errors while attempting to publish. The publish operation has failed and the target site has not been created.

When you click to view the details for the message, the first error entry is:

"There was an error uploading the data in the tables being created on the server. The Microsoft Access database engine encountered an error while communicating with SharePoint. More detailed information"

Resolution:

1. Open the table in datasheet view in Microsoft Access
2. Open the Attachments window
3. Select the file that contains the invalid characters
4. Click Save As.
5. Save to your desktop
6. Remove the file from the Attachments list
7. Click ok
8. Save the table
9. Rename the file you just exported to your desktop
10. Add the file back to the record

Ref: http://support.microsoft.com/kb/2711562

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.

How to export user information to Excel

SharePoint provides you “Export to Spreadsheet” feature across almost all list, allowing you to export your list content to Excel. But not all lists have such functionality. For example, this option is not available for User list.

Here are the details:

We can achieve this by using two methods:

1. OWSSVR Functionality

2. Open with access

OWSSVR Functionality:

This export functionality is just using owssvr.dll, so you can use the following URL to open your users in Excel

http://[site]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=[LISTID]&View=[VIEWID]&CacheControl=1  

Everything you need is getting the right ID of the list and its view. To get these values follow the next steps:

·         Go to the “People and Groups” page of your site

·         Navigate  to “Settings->List Settings” options via Toolbar

·         Click on “List View” in “View” section to edit the List View.

·         Choose the fields you want to export (need to click OK and open view for edit again)

·         Copy the “List” and “View” IDs from URL in this the page, where you edit List View.

·         Save copy-pasted ID and replace [LISTID] and [VIEWID] in our URL.

·         Open original URL http://[site]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy in browser, with the right values

Open with access method: this is the most simple method to extract the data.

·         Open any custom list by using the option “open with access”

·         When the list will open in access then you will see the option of “user information list” in the left hand side.

·         Copy and paste it in excel and that’s it. You are done.