Go to Top
  • No products in the cart.

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 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 the 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:

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 number 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 a 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 an 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.


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.

87 Responses to "SharePoint Lists and Microsoft Access in depth"

  • Patricia Marchand
    June 21, 2016 - 2:15 pm Reply

    Seems sharepoint creates more problems than solving them.
    May be it should not be called sharepoint but nosharingpoint!

    Why do we need so much but and may be, the world of computing is simpler than that.

    Make it simple: access on the web? use vb asp.net with your own security, intranet use vb.net with access works perfectly.

    Ah microsoft I used to really love you but now I am afraid you have gone beyond repair. We need something like what microsoft used to be popping up from the darkness of nowadays IT.

    Above is my personal point of views.

  • Sakshi Grover
    June 16, 2016 - 7:49 pm Reply

    Hi Ben! Great article.

    I have a question regarding Access and SharePoint Lists please.

    I have linked a sharepoint list to an Access Database to query for new projects that are not already in SharePoint. So basically I link Excel to SharePoint with Access in between. The query contains a ‘search’ action, ‘append’ action and an ‘update’ action. It has been working great for 5 months but suddenly past few weeks, whenever I run the query in Access, existing data in the SharePoint list gets deleted automatically. I am guessing its the ‘update’ query thats creating the issue cause it takes ages to complete but I can’t seem to point out the issue. Why would it suddenly start overwriting data in SharePoint and what can I do to fix that please?

    Many Thanks

  • Elvira
    March 29, 2016 - 1:25 pm Reply

    I am trying to create a two-way sync between an Excel file and a Sharepoint table. Previously it was only one way; Excel took the data down from SharePoint. However, I found out, if I put an Access DB in between with linked tables to Sharepoint, it allows updating Sharepoint from Access. This way I was hoping I could both take the Sharepoint data into Excel and update from Excel via Acces the sharepoint records.

    However, I cannot even update the sharepoint table rows, with a reliable response from the table view in Access. I sometimes get an error Field Does not exist, or Cannot update, object is read only. I can always update any fields of a record that I myself created. But records that others created I can almost never, but sometimes. I could not figure out what caused the ability when I could update a field or more on a particular row created by another user. I am a site owner with full access to all except those perky admin things that only administrators can do.

    A few weeks ago I could not update because I was not a site owner yet, but when I was added to that group, I could update everything I tried. I could not work on it for a few weeks, and here I am, back to squere one not being able to update any fields on most records. Only a few records at the end of the table, where the last record is the one that I myself created.

    I tried unchecking the use cache compatible with 2010 as it was widely reported as a solution of this type of issues, but when I uncheck it, after that it cannot update anything with the error “you cannot reference rows created when you are disconnected from the server…..”.

    I have not found a solution by searching so I am reaching out this way, maybe you or somebody reading this message will know the answer.

    Just to make sure, the fields that cannot be updated are not all based on drop downs, but some are. So this is not the issue.

    Thank you so much,


  • CM-Fan
    January 7, 2016 - 10:24 pm Reply

    I import a SharePoint 2013 list into Access 2010 following these steps:

    Open Microsoft SharePoint Foundation
    Prompt asks for the source, which I provide the URL for the SP list
    I chose ‘Import the source data into a new table in the current database’

    Now I look at the table and on two of the fields, I get html tags with the field value. For example, for the description field it shows the blah blah blah….

    Is there any way to eliminate this markup language junk before importing? It would save a lot of time.


  • Lucas de Wal
    September 7, 2015 - 3:30 pm Reply

    Hi Ben,

    Good article and easy to read. It is remarkable to see how a post in 2011 is still so relevant. I have a question that I hope you can help me with. I’ve read your post, watched several videos on how to use SharePoint lists as a datasource for an Access Web App table, which all work basically, but I’m seeing very slow performance on my end.

    Here’s my setup:

    1. We use Office 365 and Access Web App.
    2. I’ve created a small list with our employees. (48)
    3. I’ve created an Access Web App using the tamplate “Assets”
    4. I’ve removed the internal Employee list and replaced it with the linked table from the SharePoint list.

    I’ve tried different browsers, different test scenario’s (also created a list with importing an Excel sheet with our customers (900) and It is always the same slow performance. It takes about 20-30 seconds to load the list view in the browser.

    Thanks in advance for your reply,

    Kind regards,

    Lucas de Wal

    • Emilio Rinaudo
      December 30, 2015 - 3:09 pm Reply

      Hi Lucas,
      I have your same Issue. I’d like to use all of the access potential by linking sharepoint lists but it is so slow that is unusable. I have a list with over 16000 elements and 16 columns…
      Did you solve your problem?

  • bobA
    August 27, 2015 - 7:18 am Reply

    I read the article and have your book also. I still have concerns about security. If I have an Access FE and sharepoint lists as the BE as far as I can tell, any user would be able to access the sharepoint lists directly and make any changes they want. They would also be able to create a new Access db and link to the tables and do anything they want. When a user first connects from an access front end which connects to sharepoint lists, they must enter their ID and password. At that time the name the sharepoint list is presented so it is not a secret.
    Is there anything I am missing regarding being able to better security the link to the data?


    • Ben Clothier
      September 1, 2015 - 8:11 pm Reply


      It’s probably best that you create a separate SharePoint site that will store only the data you need, and then create groups and assign the permissions accordingly. If you do that, then that should block access from users who are not authorized. This has to be managed in SharePoint, not Access as Access is simply the client and only can access what it is permitted to access under that user’s credential and its permissions. By default SharePoint is quite ‘open’ and all users has lot of rights. Creating a separate subsite and customizing the permissions will help.

      This work well at object level. But if you need more granular level (e.g. item list) then the first thing I recommend is to determine whether you can work with SharePoint list’s built-in feature to segregate data based on who created, meaning that they can only see their own data but not other (or you can permit them to see all data but only edit their own data). But if it’s not going to work, then you can consider using folders within the SharePoint lists to segregate permissions but understand that for each custom permission you create, the performance can be hurt.

  • Bill Benson
    July 1, 2015 - 9:56 pm Reply

    Hi Ben, great article. One thing I was looking for a better understanding of, as an Access developer, is the fact that when I run queries, the field names show up as they are in the List – for example, ID – despite the fact that I have aliased them with a new name.

    So something like Select ManagerInfo.ID as ManagerID from [Users] As ManagerInfo will give me the result



    Also, I have a *lot* of trouble filtering a table that is really a linked Sharepoint List. We have a transaction list for example, where the manager’s name is the field presented to the interface, but I have to actually filter using a numeric. Access will not natively allow me to do this, and even if it could, it is awkward. I am much more likely to know the name = Smith (the List does present the information when the table is opened, and in queries, as “Name” after all) – than Name = 25.

    When I add the List to a query I also have to use “Where Manager = 25”, even though when the query results come back, I see Smith.

    Is this a problem with how the List was created, or a versioning problem (say, Ac2013 and an older version of Sharepoint) – or something else?

    Thank for giving us so much info!

    • Ben Clothier
      July 24, 2015 - 8:11 pm Reply

      It sounds to me that the alias is not working because you might have something in the caption property that overrides the alias you defined in the query. Remove the caption from both table or the query (they are in property sheet) then see if it does it.

      As for filtering, you need to join to the other table that has the name you want. So you would have a query that includes the transaction list, and the other one with managers’ name. There should be a line automatically added connecting two table in your query. You can then use name from the 2nd table to then filter the entries for transactions.


  • Warren
    March 3, 2015 - 10:55 pm Reply


    If I linked SP Lists to Access tables.. is it true that I can only modify the data from the SP List? Are the access tables read only from access when I connect them to SP?

    If this is the case.. why is it that a “Sync to SP” button comes up in Access — allowing me to sync my changes back to SP, if I decide to make changes from the Access end?

    Anyone know how this works? I am using 2013 in both SP and Access. Someone told me that if I link SP lists to Access tables, the access stuff becomes read only.. But I would like to make changes from BOTH instances.. and have them synchronize.

    Thanks much!

    • Paul Harrold
      March 11, 2015 - 5:38 pm Reply

      Hi Warren,

      If you link to SP lists via MS Access you will be able to create and edit data as if you were using standard Access tables. Whoever told you that the data would become read only was misinformed. You could set the lists as read only in SharePoint, but that would take fair degree of knowledge to do so; it is by no means the ‘default’.

      If you need help building an Access/SharePoint system then come along to http://www.comcraft.co.uk and maybe we can help you out.

      Good luck,


    • rgusaas
      March 16, 2015 - 6:39 pm Reply

      I have MS Access databases that contain linked SharePoint lists and local tables which are read write.

  • Mithun Dhar
    January 19, 2015 - 10:27 pm Reply

    Hi, I am new to Access. Can anyone pls help me out on this issue. I come accross with a situation where the sharepoint table is updated through access by an user and he downloaded the the updated information. But after a few days, it is seen that those changes weren’t happended and it is showing the data just before the update. Is there any connection issue?

  • seandale
    December 24, 2014 - 8:56 am Reply

    have created earlier sharepoint list through excel.

    Users generally give data in excel which need to upload at sharepoint list. Now I got new excel with additional columns and records & want to update already created sharepoint list with this data.

    I have sharepoint linked table created in MS access database which has been used for reporting purpose.

    Using MS 2010 version for excel/access database.

    Please suggest how i can update existing sharepoint list with new data which has additional columns/records which is provided in excel. I am not looking for third part tools.

    Anyway with MS access database/sharepoint?

    draw barcode in Access

  • Nicole Huron
    November 20, 2014 - 10:17 pm Reply

    Background: I have SharePoint 2010 Lists that I open in Access 2010 as Linked Tables.

    Issue: The first person can open the database file no problem but the second person gets the error message that the database is locked (Error 3474). We would like multiple users to be able to edit the data in Access.

    What can I do to resolve this issue?

    Thanks in advance for taking the time to read this post!

    • Juan Soto
      November 21, 2014 - 8:41 am Reply

      Hi Nicole,

      Make sure each user has a copy of the database and they are not all using the same file. Of course that will lead to other issues, such as being able to update all the users of the app when there are new features or bug fixes.

      Kind Regards,

      • Nicole Huron
        November 24, 2014 - 8:01 pm Reply

        Thank you Juan! That did the trick! Much appreciated!

  • md arshad munir
    November 8, 2014 - 11:25 am Reply

    what is share point list in ms access

  • Jim Corder
    November 5, 2014 - 7:40 am Reply

    Thanks for the article! I have a Accessweb database on sharepoint 2010 for tracking tool inventory data. I need other sites on the portal to be able to access this data in lists. How do I set up a column in another site to show the data in the accessweb table?

  • Ronn Gusaas
    October 9, 2014 - 8:43 pm Reply

    Hi Ben,
    This is an old post so I am not sure responses are still possible.

    I am linking SharePoint Lists to MS Access and have noticed that sometimes field names do not appear in the linked version of the list while importing the list shows both the correct field names and the correct content.

    The problem occurred when I inserted additional fields to the SharePoint list (via SharePoint). I deleted the link in Access and relinked but no change.

    My field names are long in some cases with no spaces. I have also seen instances where the field name will be repeated with only the number 1 appended. (both columns containing the same data.)

    Any thoughts?

    • Ben Clothier
      October 9, 2014 - 9:58 pm Reply

      This might be actually there but hidden. To verify this, open the table in design view. If you see more columns that you see in datasheet, then you know it was bought in but is hidden. Switch over to datasheet, then right-click a column header and select command “Unhide Field” which will give you a list of fields that are hidden – uncheck what you want to see.

      Hope that helps.

    • Leo
      October 10, 2014 - 2:07 pm Reply

      I think you have to refresh list connection doing right mouse button over list name and selecting “refresh list” under “more options..” choice. Forms and other objects referring to this list will be closed during refresh.

      I hope this may helps you.

  • Doreen
    October 1, 2014 - 5:25 pm Reply

    I understand access memo field only allows 255 characters in a memo field so if I use a table created in access and link it to a sharepoint list which now allows for that field to have 8,000 characters and create a query using access tables and the linked sharepoint list? Then this query will then merge with a form in Microsoft word allowing it to pull the data from the list accepting the 8,000 characters?

    • Ben Clothier
      October 9, 2014 - 10:02 pm Reply

      Queries can show memo fields — the rule is that you should do a simple SELECT — any other functions that you might use to group or totals might cause the memo to get truncated. This shouldn’t be a SharePoint thing — this is a known issue with Access for long time. Allen Browne has a handy article on this subject.


  • Jeanne Riggs
    October 16, 2013 - 8:46 pm Reply

    Hi Ben,

    I’m working with Access 2010 and SharePoint 2010. I’m a mid-level Access user and an ‘advanced beginner’ with SharePoint. Here’s what I want to do: Create a table in Access by linking to an existing SharePoint list. I want them to be linked, so that I’m capturing any data changes made and/or new records in the SharePoint list. I DON’T, however, want changes that I make in the Access table to be reflected in the SharePoint list. Can I do that?

    • Ben Clothier
      October 16, 2013 - 9:03 pm Reply


      I think to achieve your goal, you need to have a copy of the SharePoint list saved as an Access table. You would then need two queries that then looks up the changes.

      The update query would be like this:

      UPDATE MyAccessTable AS des INNER JOIN MyLinkedSharePointList AS src SET
      des.Field1 = src.Field1, des.Field2 = src.Field2, …
      WHERE src.Modified > des.Modified

      Note that you need to keep record of all SharePoint lists’ built-in column such as Created/Modified, etc so that you can compare your local copy against SharePoint.

      Then to insert new data:
      INSERT INTO MyAccessTable
      (Field1, Field2, …)
      SELECT src.Field1, src.Field2, …
      FROM MySharePointLinkedList AS src
      LEFT JOIN MyAccessTable AS des
      WHERE des.ID IS NULL;

      This frustrated join ensure that you only add data that you don’t have already from your SharePoint List into your Access table.

      With those 2 queries, you should then have a copy that you can freely edit without reflecting back in your SharePoint copy.

      Note that you also could work offline and simply discard the changes but I don’t want to try that since someone could accidentally forget to NOT synchronize and thus write the data anyway.

      I hope that helps, Jeannie. If you need further assistance, please contact us. Thanks for reading!

      • Jeanne Riggs
        October 16, 2013 - 9:07 pm Reply

        Thanks for the quick reply, Ben. Your explanation makes perfect sense! I’m following you now!

  • Joe
    October 11, 2013 - 7:42 am Reply

    I’ve been trying to update and append date from a SharePoint list into a local access table. However, the SharePoint list has to be refreshed/opened prior to being able to complete a successful update/append. Any thoughts on how to do this?

    • Ben Clothier
      October 11, 2013 - 8:06 pm Reply

      Joe, are you working offline? Are you using 2007 or 2010 for this?

  • Paul
    July 22, 2013 - 5:39 pm Reply

    Thanks for the video link showing how to prevent ‘Web users’ from viewing data that they shouldn’t. But let’s say I create an Access ‘desktop’ database and use SharePoint lists as my back-end.

    How do I prevent users from simply opening Access and linking to, or downloading the SharePoint lists? I have spoken to MS support about this and they have been unable to find a solution to what seems to be a huge security hole in Access/SharePoint set-ups.

  • Elmar
    June 11, 2013 - 1:56 am Reply

    Hi Ben
    Great article and I was hoping to find a solution to my tiny problem but… I have Sharepoint list and Access table linked to that list. Programmatically I re-build the table data once a week or so (deleting all records and then inserting new ones) and 9 times out of 10 the Sharepoint list is not updated. When I open the Access db, it tries to reconnect to Sharepoint and I get that yellow bar with a “Synchronize” button. Any idea why my changes don’t sync automatically?

    • Ben Clothier
      June 11, 2013 - 2:04 am Reply

      I believe I’ve seen that problem before. You didn’t say how many rows but I have observed cases where doing several rows changes at once cause timeout.

      Two ways to work around this problem, depending on the particulars:

      1) make all your edits offline. Note that to toggle offline manually, you have to either use Work Offline in the internet browser or use 2007-style caching. (discussed here: https://accessexperts.com/blog/2011/12/08/how-to-work-offline-with-sharepoint-and-access-20102007/ ).

      2) batch your edits into smaller sets and repeat in a loop.


      • Elmar
        June 11, 2013 - 2:32 am Reply

        Wouldn’t it be a solution if I have a single line macro in my database, something like – DoCmd.RunCommand acCmdSynchronize
        and then when all edits are made, I’ll start Access from my app, run that macro and then close the database?

        • Ben Clothier
          June 11, 2013 - 3:08 am Reply

          Maybe in your case. In the case I am thinking of (and that was a while ago, BTW), it wasn’t a solution because we’d get conflicts that needed to be reconciled and even if we tried to reconcile, we’d get same error and end up disconnected again.

      • Elmar
        June 12, 2013 - 2:48 am Reply

        not in my case… still can’t it get sync programmatically unless I open the Access db

        • Ben Clothier
          June 12, 2013 - 3:11 am Reply

          Elmar, I think I might be missing something — you say can’t until you open Access db, but where else would you run it programmatically if you don’t have Access db opened and therefore the VBA code or macro loaded?

          • Elmar
            June 12, 2013 - 4:07 am

            I added a module to the Access with a single function
            Public Sub Synchronize()
            DoCmd.RunCommand acCmdClose
            DoCmd.RunCommand acCmdSynchronize
            End Sub

            Then in my C# WinForms app. I have a function which:
            1. Deletes contents of the linked table
            2. Re-builds data
            3. Opens Access db with the code using Office.Interop.Access
            4. Runs Access Synchronize() procedure
            5. Closes the db

            I don’t get any error messages, it just doesn’t sync

          • Ben Clothier
            June 14, 2013 - 5:47 pm

            Elmar, does it work if you do it manually?

          • Elmar
            June 14, 2013 - 8:22 pm

            Not sure how to explain but just opening the database manually doesn’t work. I need to do some operations, like to open a table, to resize a row height, etc., just any… Then I get a message that the connection to SharePoint has been lost and restored now, and finally that yellow bar with the “Synchronize” button appears on the top. In the worst case scenario I may have a dedicated user to do this all but it’s real pain.

          • Ben Clothier
            June 14, 2013 - 10:38 pm

            I see. I suspect there’s more details that need to be considered. If you want, we could set up you as our client and see if we can get bottom to this situation. If you’d like give us a call.

  • John Adams
    June 5, 2013 - 2:31 am Reply

    I’ve enjoyed your explanations of Access web databases published to Sharepoint. I have a manager bugging me to do exactly that but instead of Access 2010 forms he’s thinking InfoPath 2010 forms would be a better UI for displaying/updating the Access database published to SP2010. Have you ever heard using InfoPath 2010 forms as a user interface to Access Services this way? My research so far suggests that is not the design intent for web databases. Would love to hear a word of guidance about this scenario?

    • Laszlo
      September 12, 2014 - 2:27 am Reply

      InfoPath is very limited compared to Access. Unless you are going down the path of Access web DB which I would strongly no recommend. InfoPath works great with SP lists if that is the route you wish to take.

  • Gerard Hascall
    June 3, 2013 - 8:09 am Reply

    Manufacturing takes turns under all types of economic systems. In a free market economy, manufacturing is usually directed toward the mass production of products for sale to consumers at a profit. In a collectivist economy, manufacturing is more frequently directed by the state to supply a centrally planned economy. In mixed market economies, manufacturing occurs under some degree of government regulation..:’`

    Take a look at all of the helpful content at our web-site

  • Mike
    May 22, 2013 - 5:49 am Reply

    Are you aware of any limitations with MS Access and SharePoint lists that have lookups or as to the number of records?

    We can’t execute UPDATE statements sometimes or after opening a list Access crashes. We’ve used Access 2010 SP1 and 2013 and we experience similar behavior.

    • Laszlo
      September 12, 2014 - 2:29 am Reply

      SP has List limits on views past 1000 items, this might be a cause of your issue if your going into a huge number of records. SP backend may not be your choice if your dealing with a large amount of records. Via SharePoint Designer, you can setup a SQL connection through lists in SP and Access treats the SQL tables as they were SP lists. (I have not tested this just yet but hope to do so this winter)

  • Charlie
    February 23, 2013 - 12:57 am Reply

    Hello Ben,
    ALWAYS fun to read up on whats happening with SP – thank you.
    My question is around SP List saving in Access without having to export to Excel with an “all columns” view. My SP Admins tell me there is a problem with the SP Generated fields showing up in the default Access table for the list… so we are not getting the Created/Modified/Created By/Modified By columns in Access view…. Comments ??

    • Ben Clothier
      February 25, 2013 - 8:28 pm Reply

      By default, they are hidden.

      You can always see them in a query OR you could open your SP lists in Access in datasheet view, right-click a header and select “Unhide fields” which you can then select the SP generated fields to display. I think you’d be better off using a query to export to Excel.

      I hope that get you closer. Thank you for reading, Charlie!

  • Henry Becker
    January 16, 2013 - 1:24 am Reply

    I work for the Army in Europe and help my Headquarters develop solution to collect requirements accross 60 communition arount the world. I used an Access connected to a Sharepoint site in Europe to collect requirements in a 50 column table. I used access to enable requirements to be collected in a manner compatible with SAP financial software standards used by the Army. Worked well in Europe but could not deploy it Army wide because of slow performance on the central Army Sharepoint side. With you solution, I was developed a view containing each communities data and have the GUID for each community in the communtiy table. When a user logs, Accessd deletes the TblRqmt, determines their Guid for the commnunity, and then links the Sharepoint with the name TblRqmt.
    This works, however, I have concerns that if the Sharepont table gets deleted, all the GUIDs will have to be created, extracted as you described and inputted into the community table. Seems like there ought to be a simplier way of doing server side filtering. Do you have any suggestions.

    I would recommend to the Sharepoint developers that they make a tool to automatically create views based on content of a field and automatticly put the GUIDs in a Sharepont list. For example, if the user wanted to filter by a column called “State”, the State field would be selected in Sharepoint, the unique valuses would be created (ie Kansas, Nebraska, Texas…). These values could be the view name and put in a Sharepoint list with the GUID. The programmer could then use this list to get the GUID. Does this exist.

    • Ben Clothier
      January 17, 2013 - 2:32 am Reply

      One approach you could consider is breaking the permission inheritance for the table and change the permission so that only you have permission to delete the table. That would give you some control over how the tables can be created, edited and deleted.

      As for enumerating GUIDs of lists in a SharePoint site, I believe the best way to do this is via Lists.asmx web service:

      Note that the book, “Microsoft Access in SharePoint World”, that I co-authored has a section that talks about how you can use the web service from VBA though be advised that it’s not as easy as using it in .NET and doing COM interop. See if that get you started.

  • Henry Becker
    January 16, 2013 - 12:59 am Reply

    Hello Ben,
    After reading this article, I ordered your book “Microsoft Access in a SharePoint World”. Your explanation are excellent. I eagerly await the arrival of your book.

    • Ben Clothier
      January 17, 2013 - 2:23 am Reply

      Excellent, thank you! I hope the book will help you and please do feel free to leave comments with us about the book.

  • Jackie
    November 1, 2012 - 1:48 am Reply

    Hi Ben.
    Thanks for the article. I am currently working with Access 2007 and MOSS 2007 and would like to publish (sync) data from an Access table with a SharePoint list.

    I’m using Access as a front-end for another application using an ODBC connection. I created a linked SharePoint list but am stuck on how to update the information. As most lists will probably end up with thousands of records I would not like to delete and recreate but sync data from a table (which has the latest info only) with SharePoint.

    Is this possible.

    Thank you very much in advance. Have a great day!


    • Ben Clothier
      November 5, 2012 - 10:25 pm Reply

      Jackie –

      One approach would be to use Windows Task Scheduler to open Access database, run a specific code routine that could include running queries inserting new records and updating existing records from your ODBC sources into SharePoint lists. If you want list to have latest info only, then you could also add queries to remove older records so the number of records on list is manageable.

      Also to be aware of alternatives, if your SharePoint server is an Enterprise license, you can use Business Connectivity Services (BCS) to create a external list that links directly to the ODBC source and show that instead so there’s no need to sync records, and you could define it so that it filters to only latest record.


  • Venkat
    September 9, 2012 - 7:56 am Reply


    I have created a access database 2010 connecting to sharepoint List. Its form an external Website, When try to create the connection first time, it opens up for the Web based authentication dialog for credentials. It all works well. even it gets synchronized.

    After some time, Created the Excel VBA coding to connect to Access Database. I have developed the VBA form to manipulate with the Access database..

    After creating the OLE DB connection string, when I try to bind the recordset to the table,which is actually connected to sharepoint list, It hangs. I can understand its missing for the authentication. Is there any way to call the Authentication dialog to popup again. I believe it should resolve my problem.

    Any help on this is appreciated.



    • Ben Clothier
      September 17, 2012 - 8:17 pm Reply

      Venkat, while it is possible to bind a recordset based on linked tables, I’ve found it to be somewhat flaky and think it may be better to use temp table so you have a local table that you can bind to then perform bulk import/export between your local temp table and the linked table within Access.

      If this is still a problem, I encourage you to give us a call and we can look at options.


  • Andrew
    August 23, 2012 - 4:47 am Reply

    Hi Ben,
    I am a new access user creating a web database to use on sharepoint. I researched how to do this for weeks, then I finally bought your book. It helped a lot but I still have one quick question. I understand that you are not supposed to add multiple fields to a lookup database because it is “bad database design”. How do you add the proper fields later so that it is user friendly? For example, if I have an ID that is autonumbered 522, how do I allow the end user to see it as, say an employee’s name instead of the autonumber? This has been a problem I have been researching for a while with no success. Thank you for your time and your book, again, it helped A LOT. 🙂

  • Maggi
    July 2, 2012 - 6:02 pm Reply

    I have question here , I am using linked table to connect to sharepoint list from MS access, can I insert the new record in MS access or can I insert new record using MS Access VBA to sharepoint list.My site is hosted on Office 365.

    • Juan Soto
      July 9, 2012 - 8:30 pm Reply

      You should be able to insert using just Access.

  • Sharyn Goedicke
    March 22, 2012 - 8:16 am Reply

    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!

    • Ben Clothier
      March 22, 2012 - 6:55 pm Reply

      Thank you, Sharyn for commenting and visting! Yes, RSS feed will work for keeping you updated.

  • Chris
    March 10, 2012 - 12:16 am Reply

    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!


    • Ben Clothier
      March 10, 2012 - 12:20 am Reply

      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.

      • Chris
        March 10, 2012 - 12:35 am Reply

        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.

  • Erin Cook
    March 1, 2012 - 10:58 pm Reply

    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”, _
    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 SettingsecookMy DocumentsNoneArchived.accdb”, True

    oAcc.Visible = False

    ‘Create a Recordset based on
    Set rstTable = oAcc.CurrentDb.OpenRecordset(“NoneArchived”)
    With rstTable
    ![PrintedPassdown] = “Verbatim Text from PMQ”
    ![ToolSet] = ToolSet
    ![Tool] = Chamber

    End With

    Any suggestions???

    • Ben Clothier
      March 9, 2012 - 2:14 am Reply

      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?

  • Libby Luft
    February 9, 2012 - 10:07 pm Reply

    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.


    Libby Luft

    • Ben Clothier
      February 10, 2012 - 1:23 am Reply

      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!

  • pedie
    January 25, 2012 - 1:31 am Reply

    Does this ariticle applies to Sharepoint 2007 and Access 2007?
    Very useful article. Thanks

    • Ben Clothier
      January 25, 2012 - 1:52 am Reply

      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!

  • Jim Parker
    January 23, 2012 - 10:11 am Reply

    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…

    • Ben Clothier
      January 23, 2012 - 6:54 pm Reply

      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.

  • Kevin
    January 21, 2012 - 12:56 am Reply

    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?

    • Ben Clothier
      January 21, 2012 - 1:02 am Reply

      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.

  • Shelli
    December 14, 2011 - 9:56 pm Reply

    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!!

    • Ben Clothier
      December 15, 2011 - 7:53 pm Reply

      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,

      • Kristofor
        February 21, 2012 - 4:38 am Reply

        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.

        • Ben Clothier
          February 21, 2012 - 4:45 am Reply

          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:

          Hopefully that helps! If you still need assistance, feel free to contact us via our main page.

  • Shubhangi
    November 30, 2011 - 1:25 pm Reply

    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….

    • Ben Clothier
      November 30, 2011 - 4:41 pm Reply

      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.

  • Bonnie
    November 11, 2011 - 1:33 am Reply

    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.


    I don’t see this as an Access replacement but an added ability. I hope Microsoft will continue to improve and expand on it.

    • Ben Clothier
      November 11, 2011 - 5:06 am Reply


      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!


  • Bryan
    November 3, 2011 - 2:50 am Reply

    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?

    • Ben Clothier
      November 3, 2011 - 3:16 am Reply


      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.

      • Anonymous
        March 14, 2012 - 8:01 pm Reply

        Thank you for the explanation. I was able to find the GUID and VIEW.

  • Luis Roman
    October 27, 2011 - 2:08 am Reply

    The article is very interesting and very good. However, I was not able to find the GUID based on the guide provided.

    • Ben Clothier
      October 27, 2011 - 3:10 am Reply

      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:


      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:


      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:


      I hope this helps.

Leave a Reply

Your email address will not be published. Required fields are marked *