SharePoint Lists and Microsoft Access in depth
Note: This is the second part of a three part series, you can find part one here and the third part here.
In a recent post, I discussed about how SharePoint and Access address similar audiences and provide easy solutions to different problems. Now we’ll examine the main object you’ll interact with SharePoint within Access: Lists. If you’re using the new web database with Access 2010, the “web tables” are in fact SharePoint Lists by a different name. Therefore, if you know something about them, you’ll also know something about web tables. There are some key differences between the two we will discuss later in the article.
As linked tables go, SharePoint Lists are definitely a horse of different color. I’ve said it before and I’ll say it again: SharePoint is anything but a true relational database. The lists totally reflect this. Therefore many techniques you may have picked up in optimizing your operations with linked tables may be inapplicable and we need to consider new ones.
Is it *gasp* de-normalized?
In a way, lists are denormalized. If you cared to peek inside the SQL Server database that SharePoint uses to store Lists, you would realize that all list items are stored in one giant sized table with columns names int1, int2, int3 and so forth. There’s another large table that describe the definition of the list and maps its columns to those wildcard columns. In a way, you could say SharePoint Lists are based on the Entity-Attribute-Value data model. After all, there is a reason why they’re called “Lists” and not “Tables”. Historically, they were just that – a simple list of values. In fact, up to SharePoint 2010, referential integrity wasn’t supported, a sore point that could very well have been the #1 blocking reason for adoption of SharePoint as a data source among Access developers. In prior versions you could relate a list to other lists in a loose way. The lists had no formal structure, since on average, they were created by non developers who uses SharePoint as a means to get the job done rather than developing comprehensive and robust solution for others. We’ll examine the implications this has for us when we use SharePoint Lists.
SharePoint Lists operates on ISAM model
Whenever we link to a ODBC data source, Access only needs to submit a SQL statement which is then executed on the server and sent back for Access’ consumption. However, when we query a SharePoint List for the first time, Access has to fetch all records and download them to a local cache in order to process the query which is then executed locally. In 2007, the local cache was stored in a XML format but with 2010, Access will use actual local Access tables (hidden from the UI) to work as a cache.
What SharePoint lists offers that linked tables don’t
This enables at least four things that we do not normally enjoy with ODBC linked tables:
1) Offline data access
A persistent connection to the backend is not required, the tables have more tolerance to disconnections & network interruptions than with a regular ODBC linked table.
2) Built-in replication
Even better, when users have a connection restored, they can sync their changes without any special configuration as was the case with old Jet Replication.
3) Design changes
With most linked tables, you probably had to shuttle between Access and the server’s management software (e.g. SQL Server Management Studio for SQL Server tables/views) if you wanted to make design changes. Even a simple thing such as adding a new field usually meant you had to go over there and issue the ALTER TABLE command, come back to Access and refresh the links to get that new field. With SharePoint Lists, whether as a web table or linked table, you get to make design changes without leaving Access.
4) Automatic Auditing & Versioning
SharePoint is designed from grounds up to provide auditing and data recovery. Whenever you delete a list item from the list, it’s not actually deleted but rather moved into a recycle bin which gives the user an opportunity to undo the accidental deletion. Furthermore, there’s second-tier Recycle Bin accessible to the administrators so data recovery is always possible. SharePoint also supports versioning of data out of the box and has tools to track all changes made to the data saved within the list. All of this great functionality is free; no configuration is required for recycle bin and you simply need to flick the switch for versioning.
Server-side filtering
There are no server-side operations other than fetching all the records and keeping the cache synchronized, which means your queries are always executed locally using Access database engine, unlike ODBC linked tables. If you want to have true server-side filtering consider using SharePoint Views which are analogous but not identical to SQL View. Unfortunately, there is no simple UI process to link to a SharePoint view; it has to be done in code:
DoCmd.TransferSharePointList _
acLinkSharePointList, _
"http:\\server\site", _
"MyList", _
"{7042e1d4-c929-4a7e-8d34-b5d93b9495fd}", _
"tblMyList", _
True
You may be wondering how we came up with GUID “{7042e1d4-c929-4a7e-8d34-b5d93b9495fd}”. SharePoint assigns a GUID to each object, you can’t use the View’s name as you could with a List’s name to select the object. To find out the GUID for your View, you’d have to open your SharePoint site in your web browser, navigate to the List and on the ribbon select Modify View. The GUID will then appear in the URL. Depending on which web browser you are using, it may be encoded so you’ll have to replace “%2D” with “-”, “%7B” with “{” and %7D” with “}”. Two things to note –
1) You can link to the same list multiple times, using different views so you are not necessarily restricted to choosing one view out of all views available for a given list.
2) Views only work as a linked SharePoint list – views can be created with web tables but are ignored in the web database, unless you create a separate linked table object which would not be accessible in the web browser, only in Access (this is what they call “hybrid application”, which we’ll talk about later).
You can use the View to provide additional filtering. This can be helpful particularly in cases where you may have a large amount of items but only need a handful of active, current, pending or open records. However, there’s one major difference between a SharePoint View and a SQL View. As you know, a SQL View has its own set of permissions and you can use Views to manage security. That’s not the case with SharePoint Views. You can prevent people from editing the view’s definition but you can’t prevent people from linking to the underlying SharePoint List directly.
How many columns can I really have?
As those familiar with SQL Server know, we can have a large table with many fields which while quite rare for normalized relational design may be desirable for a OLAP data model. SharePoint also allows a large number of fields. I already mentioned earlier that the List basically has a number of columns of certain data type. How does it handle the scenario when we need more than a given number of a certain data types? The answer is that SharePoint LIst uses Row-Wrapping; a single SharePoint List Item may be actually stored on more than one SQL Server row. By default, SharePoint allows you to row-wrap up to six SQL rows. Let’s take the example of Date and Time data types. The article linked above says we can have 48 Date and Time data type in a single List. If we divide 48 with 6, that actually means we only have 8 Date and Time data types that will fit a single SQL row. So, if we create a SharePoint List that had 20 columns and it happened that there was 9 Date and Time, then we’d have one SQL row populated with data from 19 fields and second SQL row populated with only one excess Date and Time data type. If your list then has 100 list items, we’d need to read 200 SQL rows to gather data from 20 columns including the 9th Date and Time data type. If performance is a important consideration in your design and you expect to store large amount of data, you should be mindful of how many columns and what data types you plan to have so you can minimize row-wrapping.
Conclusions
We’ve only scratched the surface when it comes to SharePoint but I’m confident you’ll find the information much more relevant to Access development and we’ll explore more about web database in my next post.
Written by Ben Clothier, Senior Access Developer at IT Impact Inc. To reach Ben, please use our contact page.
28 Comments
The article is very interesting and very good. However, I was not able to find the GUID based on the guide provided.
Luis – Thanks for the feedback.
The hard part is that URLs for lists can vary depending on whether you are using a company’s SharePoint server or a hosted SharePoint, how it is placed in the overall structure and other factors. Also, consider that you have sufficient permissions to actually view the required page. If you are not an administrator, you may need to ask whoever administrate the site to tell you the GUID for the list.
Assuming you have sufficient permissions, here’s a way to find out:
1) Open your web browser
2) Use the following URL:
replacing the with your SharePoint’s domain and with the complete path to the site that contains the list you need. As an example, suppose you have a Office 365 site, it may look something like this:
https://mycompanyname.sharepoint.com/sites/mysite/_layouts/viewlsts.aspx
On this page, you should see a list of all lists (as well other objects). Select the list you want.
The page should then take you to the list. If you want to get a specific view, the view may not be loaded by default. In order to ensure you have the correct view GUID, go to the ribbon. Select “List Tools”, “List Settings” then select the View you want to modify and click “Modify View”
You should be now on a page that administrate the view. The URL may now look something like this:
http://mycompanyname.sharepoint.com/_layouts/ViewEdit.aspx?List=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx&View={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}&Source=….
The List GUID is after “List=” while View GUID is after the “View=”
Notice further that the actual string may look different in different browsers. In IE, you need to manually convert escape characters (which is always in this format: %## where ## are a valid hexadecimal symbol) to the {, } and -.
I hope this was enough to get you the GUID you need to find.
One more tip – if you’re not sure where to start looking and you already have your SharePoint LIst linked in your Access database, you can use VBA to tell you where it’s located:
?CurrentDb.TableDefs(“MySharePointListName”).Connect
I hope this helps.
I have been trying to house my Access database on SharePoint and have been able to migrate all tables to SharePoint Lists except one (too large to handle). My issue comes up with my queries. As long as I do not use subtotals in my queries all data is fine. However, as soon as I enable subtotals some of the data comes out as random symbols (??, ??, ?×, etc.). The actual data used to be simple text fields with names or descriptions. The funny part is that it doesn’t happen to all fields, only select ones. And again, only if subtotals are enabled.
I have verified all data in the SharePoint Lists and they are all single lines of text, so nothing strange there.
Have you encountered this issue before or know how to fix it?
Bryan,
I’m sorry to hear about the odd behavior you’ve been encountering.
I believe there are additional factors at the play that requires a deeper look at how your table & queries are structured in order to ascribe the cause of the strange characters in select columns and when you enable subtotals. We also need to look at what aggregating function we’re using for the subtotals.
The fact that you only have this happening in select columns suggests to me that there’s a difference between those group of affected columns from other single line of text columns. For example, are they indexed? Are they actually lookup fields? Or maybe they have “Required” set? I’d try to find out what difference, if any, there are as it may be a clue toward a resolution.
And what happens when you try and use a different aggregation for the subtotal?
If you need help with finding the solution, please do feel free to contact us to get a great quote.
Thank you for the explanation. I was able to find the GUID and VIEW.
Hi Ben,
I have finished my first Access Services project and finally got the customer using it. There was a learning curve and some issues to clear up to make it a reasonable tool (speed optimization and reporting flexibility) but I believe I’m there. I’ll also give some users with more intense needs a hybrid option.
I blogged about a reporting solution I finally came up with.
http://www.workathomeencouragement.blogspot.com/
I don’t see this as an Access replacement but an added ability. I hope Microsoft will continue to improve and expand on it.
Bonnie,
That’s an awesome solution. I quite like that you used tables & queries to manage your filtering so you also abstracted away the need to update the form every time you need a new filter option. Thanks for sharing!
Ben
Hi Ben,
Nice article. After reading the article I found you to be the best person to answer my query. So requestin you to please look into my query below:-
I have a SharePoint (2010) Linked table in Access 2007. I want to rename some column name and also want to add some new columns in this linked table. But it is not allowing me to do saying “Table is linked table whose design can’t be modified”. Pls suggest some ways to do this..
Thnx in advance….
Because your SharePoint is newer than your Access, it’s possible that you cannot do designing within Access. In this circumstance, you would do the designing via SharePoint at viewlsts.aspx or use a free tool, SharePoint 2010 Designer to manipulate the list design.
I am in between an end user and someone who understands *all* the computer science. I have a SharePoint site but not the administration of it. I have written numerous databases and understand SQL – I am a manufacturing engineer who had some computer science training in the 90′s. When I read an article like this, I almost need a demo to understand what you are saying. That being said, this has been the most helpful information to me in my web searching thus far!!
Shelli -
Thanks for the feedback. I can totally appreciate your request for wanting a demo. We’ll continue to write more articles on this subject.
If there’s any topics you feel needs more coverage or extrapolation, by all means, let us know about it!
Thanks again,
Ben
Hi Ben,
Great article. I do have a question for you. I am trying to use sharepoint to house data based off of a front end Access interface. The issue I am running into is that I must give all the end users “contribute” access to the Sharepoint List (where the data will be housed). This creates a risk of someone going in to delete all of the stored data. I can’t find a way to secure my table so that the data cannot be modified once entered. I cannot seem to find anything on the internet related to this issue. If you could help out at all, i’d be eternally grateful.
Kris, great question!
Contribute is one of default permission level that you assign to a group.
In your scenario, you need to create a custom permission level so you can grant permission to read & edit a list but without rights to modify and if desired, preventing deleting.
The steps to create a custom permission level is same as one that AccessHosting.com shows to prevent users from downloading a copy of Access database off the web as shown in their video tutorial here:
http://vimeo.com/17328798
Hopefully that helps! If you still need assistance, feel free to contact us via our main page.
Hi Ben,
Excellent article. We are working with SharePoint 2007 and Access 2003. We’re currently using an Access solution for initiating and processing work orders. We’d like to continue using our Access DB for processing work orders but have users initiate new requests through SharePoint. Not all users have Access on their PCs but all have intranet/SP. Any good solutions for bringing new items from a SP list into an existing Access table? I’m thinking maybe a link to the SP list with some append query to bring selected new requests in. Your thoughts?
I’d look into using SharePoint Designer (a free download, BTW) to create a Data View Page bound to the same SharePoint list your Access database will be reading from. You can then use that Data View Page to make a simple data entry form without having to write HTML (you can write if you want, just that it’s not necessarily required). You can then have your users visit a certain URL on your SharePoint site to get to that form.
Note that, though, the Data View form must reside on the same site level as your SharePoint list if you want to keep it simple.
You can certainly import the new data from SharePoint list into your Access table but be sure to consider the case when someone edits an already inserted row. If my memory serves, you may be able to specify that data view form be insert-only and disallow updates which would be one way to ensure you get latest data without the added complication of figuring which version of data is right and so forth.
If there’s more questions or anything we can help, please feel free to contact us either via our accessexperts.net link or phone: 773.809.5456.
Hi Ben: Have you ever tried to link to a SharePoint list that contains multiple content types, and then try to add records of a specific content type through MS Access? I cannot find any way to do it, as when I link to a SP list from Access, it marks the Content Type field as read-only…
Jim -
I don’t believe there is a way control the content type that Access will use. It’s determined by which content type is the default for that list. Furthermore, one biggest issue with Content Types is that when a SharePoint List is allowed to have multiple content types, Access sees it as {Fields for Content Types 1} + {Fields for Content Types 2}, which runs against the grain of relational database modeling. When you build a list that is primarily going to be used via Access, it is usually best that the list only have one content type and there be a separate list for each content type.
If you need assistance with developing an Access solution using multiple content types, feel free to contact us.
Does this ariticle applies to Sharepoint 2007 and Access 2007?
Very useful article. Thanks
Pedie -
Good questions. The answer is partially. In Access 2007, the linked SharePoint list are cached as a XML file whereas 2010 stores them as actual Jet tables internally. As a consequence, if you use a large enough list, Access 2007 has to wade through a big XML file to serve your requests. You still can use the server-side filtering to keep the volume small enough. Another notable difference is the fact that you can manually toggle offline work with 2007 but you must toggle offline before you go actually offline. 2010 can automatically toggle offline if connection is lost but otherwise has no way of being toggled offline. The last crucial difference is that SharePoint 2010 is also the version where referential integrity is supposed. SharePoint 2007 has no way of enforcing relationships so that is a consideration where you are concerned about ensuring data integrity.
I hope that helps. Thanks for reading!
Hi There – I glad there’s someone out there that understands the connection between SharePoint and Access! I’ve inherited an Access database / SharePoint environment and am not sure where to go from here. We recently upgraded to SharePoint 2010 and have replaced some of the original lists with new lists. We’ve reconnected the Access database to the new lists, but continue to get an I/O error in Access unless we refresh one of the new list connections. We’re not seeing any errors on the Windows logs or on the Servers. We’re on Access 2007 – could that be part of the problem?
Any help would be greatly appreciated.
Thanks,
Libby Luft
I’m sorry to hear you’re having problems.
One thing you could do to help rule out if it’s an issue with Access 2007 is to download the free Access 2010 runtime and install into a TEST computer then copy your Access database and try to run it there using the Access 2010 runtime. If you get the same I/O errors, it may mean it’s an issue with networking, SharePoint or the data content.
If it’s an issue with data content (e.g. you have large amount of data, or several lists being pulled in), then one way to rule this out is to test with a form that only uses one list at a time and has no dropboxes, listboxes or subforms. See if this work reliably and whether for all or only certain lists. Sometime it may be just one list that’s actually causing the issues so this mock forms may help you narrow the scope.
But if it’s networking or SharePoint issue, please feel free to contact us so we can offer assistance in resolving the issue as this is usually too complex to resolve in a single blog post.
Best of luck!
I have linked a list view from SharePoint 2007 to Access 2007 but one of the columns ‘PrintedPassdown’ is read only. This is the code in my module:
Sub linkedViewNoneArchived()
DoCmd.TransferSharePointList _
acLinkSharePointList, _
“http://moss.mava.micron.com/MFG/f6pe/dryetch”, _
“ShiftEquipmentPassdown”, _
“{08BD6B4E-51CE-45EF-BA9D-6517D50EB103}”, _
“NoneArchived”, _
True
End Sub
Just as you had above. I can edit all other columns. This column is set to multiple lines of text, and Enhanced Rich Text. (This is a required field.)
My code to add a new entry always fails at .Update in the below code:
Set oAcc = CreateObject(“Access.Application”)
‘Open Database in Microsoft Access window
oAcc.OpenCurrentDatabase “C:\Documents and Settings\ecook\My Documents\NoneArchived.accdb”, True
oAcc.Visible = False
‘Create a Recordset based on
Set rstTable = oAcc.CurrentDb.OpenRecordset(“NoneArchived”)
With rstTable
.AddNew
![PrintedPassdown] = “Verbatim Text from PMQ”
![ToolSet] = ToolSet
![Tool] = Chamber
.Update
End With
Any suggestions???
Do you know if you have permission to update this one column? Can you update the column when you open it directly in SharePoint web?
Hi Ben,
Thanks for the article. I am trying to import a SharePoint list into SQL Server using DoCmd.TransferSharePointList. The TableName parameter will not allow us to use the SQL Server schema attached to the table name. It keeps giving us the Transfer Type is not valid argument. If we don’t put a schema in fromt of the TableName parameter, it imports and creates a new table.
How do we use the TableName parameter so that it imports to the table we want?
Thanks for your help!
Chris
If you’re trying to import data from SharePoint List into SQL Server table, you wouldn’t use a TransferSharePointList for this job. You may be able to use TransferDatabase but I think I’d prefer to create the appropriate table in SQL Server first with fields that’s need. Then create an append query in Access to insert into linked SQL Server table selecting from linked SharePoint List.
Hi Ben,
Thank you for your quick response! We will resort to our end user refreshing the Excel file with the SharePoint data and then import the Excel data into SQL Server. We were so close! It’s funny the Microsoft didn’t give us the ability to import SharePoint lists directly into SQL Server like you can with other data sources. Maybe in the future…
Thanks again Ben.
Good web site! I truly love how it is simple on my eyes and the data are well written. I’m wondering how I could be notified when a new post has been made. I have subscribed to your RSS which must do the trick! Have a nice day!
Thank you, Sharyn for commenting and visting! Yes, RSS feed will work for keeping you updated.