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:\serversite", _
   "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.

Posted in Access Help, Access Web Database, SharePoint
60 comments on “SharePoint Lists and Microsoft Access in depth
  1. Jeanne Riggs says:

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

      Jeanne,

      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!

  2. Joe says:

    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?

  3. Paul says:

    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.

  4. Elmar says:

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

      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: http://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.

      HTH.

      • Elmar says:

        Thanks.
        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 says:

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

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

        • Ben Clothier says:

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

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

            Elmar, does it work if you do it manually?

          • Elmar says:

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

            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.

  5. John Adams says:

    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?

  6. Gerard Hascall says:

    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
    <http://homelifestyledigest.com/index.php

  7. Mike says:

    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.

  8. Charlie says:

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

      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!

  9. Henry Becker says:

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

      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:
      http://msdn.microsoft.com/en-us/library/lists.lists.getlistcollection(v=office.12).aspx

      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.

  10. Henry Becker says:

    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.

  11. Jackie says:

    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!

    Regards,
    Jackie

    • Ben Clothier says:

      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.

      Ben

  12. Venkat says:

    Hi

    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.

    Regards

    Venkat

    • Ben Clothier says:

      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.

      Thanks!

  13. Andrew says:

    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. :)

  14. Maggi says:

    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.

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

  16. Chris says:

    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

    • Ben Clothier says:

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

        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.

  17. Erin Cook says:

    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 SettingsecookMy DocumentsNoneArchived.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???

  18. Libby Luft says:

    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

    • Ben Clothier says:

      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!

  19. pedie says:

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

    • Ben Clothier says:

      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!

  20. Jim Parker says:

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

      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.

  21. Kevin says:

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

      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.

  22. Shelli says:

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

      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

      • Kristofor says:

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

          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.

  23. Shubhangi says:

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

      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.

  24. Bonnie says:

    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.

    • Ben Clothier says:

      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

  25. Bryan says:

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

      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.

  26. Luis Roman says:

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

    • Ben Clothier says:

      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:

      //_layouts/viewlsts.aspx

      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.

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Free email subscription

Enter your email address:


Facebook

Twitter

Blog Archives

ITImpact
%d bloggers like this: