Things To Know When Using Sharepoint Lists In Access

I just found out an interesting thing to remember when uploading data from Access (or Excel) into Sharepoint Lists. 

If the tables were originally exported from Sharepoint lists into Excel or Access, Sharepoint exports ALL FIELDS in the source including the fields created by SharePoint at creation-time of the original list.  These fields show up on the end of the Field lists in Sharepoint (or when attached in Access) and are internal to the functioning of SharePoint in ways that are mostly obvious from their names.

They include (not exclusively): 

Content Type

File Type

Workflow Instance ID

Modified

Created

Created By

Modified By

URL Path

Path

Item Type

Encoded Absolute URL

and are not visible when in a List View in SharePoint unless you administer them to show up.

The fact is that you will probably never use these fields (but then again you may) but, if you re-upload a table into another Sharepoint list REMOVE ANY FIELDS YOU DID NOT CREATE FIRST. 

If you do not remove the fields then Sharepoint will get somehow confused when you subsequently access the data (inconsistently).  I have noticed that in Access linked Lists I sometimes (no particular rule as far as I can see) get an error that the table is not found.  Subsequent attempts to get at the table succeed though.  But in the meantime your error trapping has kicked in and in a perfect world the application exits with a trapped error.  The Lists seem to work but there is a definite problem.

Remove those extra fields so that Sharepoint can recreate them and you will be fine.

Another important reality is – do not have a field named “ID” in your table.  Sharepoint creates an autonumber field called ID when you create a table.  Because this is an Autonumber it becomes a very useful field for your table joins in your application.  But remember, as in all autonumber fields, if you create new List Tables and rely on the ID field for Foreign Key assignments you will have to match them up using an Update query later.  Therefore before I do such an upload I always create an OldPK field and put the old ID number in there, thus allowing me to match things up again later t new new Autonumbered ID key.  This is frustrating but necessary in my experience and only takes a few minutes and a few Action queries.

I have TRIED to copy Lists between sites in Sharepoint 2007, which would eliminate the need for this kind of kludgy process but I hope in 2010 that function will work (??)  We’ll see I guess.

Dick

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s