locked
SharePoint 2007 and Access RRS feed

  • Question

  • I'm familiar with the linking options between SharePoint and Access.  Is there a way to have an Access table link to a SharePoint list but where I can modify the table in Access (in other words the Access table is the source rather than the SP list)?

    We'd like to build forms with considerable validation and some calculations against an Access database for a central user who would enter through Access, then have it linked to a SP list for updating columns for a broader user community using SharePoint where the calculations are not needed.

    Thanks.

    Tuesday, June 12, 2012 3:06 PM

Answers

  • Calculations can live only on a form, but in the case you need a table, then...

    The form could be based on a "Data Entry" table, which is local, and then changes can be committed to the SP List.  It would require populating in the OnLoad, which is fairly simple, but could be a nuisance if the table/form changes often. The good news is that if a user cancels an entry, then there's nothing to commit, else, you could either do a simple Delete & Append or a more rigorous Update process. All of this is fairly easy with a little VBA code and a query or three. 

    This would also require some kind of hand-rolled "locking" scheme to prevent conflicts, or just adopt the "Last In Wins" philosophy.

    For better and smarter Access answers, I believe that UtterAccess.com is the Access Hospice support site.


    Steve Clark, MCTS | Twin-Soft.com

    Tuesday, June 12, 2012 7:12 PM

All replies

  • Calculations can live only on a form, but in the case you need a table, then...

    The form could be based on a "Data Entry" table, which is local, and then changes can be committed to the SP List.  It would require populating in the OnLoad, which is fairly simple, but could be a nuisance if the table/form changes often. The good news is that if a user cancels an entry, then there's nothing to commit, else, you could either do a simple Delete & Append or a more rigorous Update process. All of this is fairly easy with a little VBA code and a query or three. 

    This would also require some kind of hand-rolled "locking" scheme to prevent conflicts, or just adopt the "Last In Wins" philosophy.

    For better and smarter Access answers, I believe that UtterAccess.com is the Access Hospice support site.


    Steve Clark, MCTS | Twin-Soft.com

    Tuesday, June 12, 2012 7:12 PM
  • Thanks Steve.  Your first line was the reminder I needed...since I got an error when trying to view the linked table in Access in design mode, I assumed I couldn't build a form against it, but, of course, I can.  So, I think I'm good for now.
    Tuesday, June 12, 2012 8:10 PM