1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 Updating Sharepoint Lists

Discussion in 'Business Applications' started by spatha, Nov 18, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    I have an Access db that updates a large number of sharepoint lists (over 800). The lists that it updates are on a large number of workspaces that are always changing.

    Right now the database uses the DoCmd.TransferSharePointList to link to the sharepoint lists one at a time, then deletes the table after it's updated. This causes a lot of bloating and slows down the database until it's nearly unusable.

    I would like to find a way to simply change the sharepoint list that the tables are linked to instead of constantly creating and deleting new tables. Is this possible? Maybe there's a way that will reduce the bloating?

    Any help would be appreciated.
     
  2. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    Shameless self bump.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well I have never worked with web data and sharepoint lists, but I might be able to help.
    What is getting bloated?
    You should be able to append the data to just one temporary table, transfer the data to the correct table (update or append) and then delete the records from the temp table (not delete the table)
    Repeat as required. You should also take a backup copy before transferring and then after the transfer carry out a "Compact & Repair".
     
  4. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    The db is increasing in file size the whole time it's running. It takes about 10-12 hours to update every table. The longer it runs the slower it gets.

    I always make a backup before I run it. If all I needed to do was compact the database when it was finished, that wouldn't be a problem. I usually end up having to cancel the data transfer, compact and repair, then start the transfer where it left off.

    The temporary tables that are created are linked tables to the sharepoint lists being updated. What I really would like to do is find some code that will change the list that the table is linked to, but the only code I've found to link Access to Sharepoint creates a new table every time you run it.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you show me the Code?
     
  6. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    Code:
        Do
            URL = <Sharepoint Site URL>
     
            ListURL = <Sharepoint List URL>
     
            DoCmd.TransferSharePointList acLinkSharePointList, URL, GetSPGUID(ListURL), , "tblSharepointLink"
     
        DoEvents
        Loop Until TableExist("tblSharepointLink") = True
     
        DoCmd.RunSQL strSql
     
        If TableExist("tblSharepointLink") = True Then
     
        Do
            DoCmd.RunSQL "DROP TABLE tblSharepointLink"
     
        DoEvents
        Loop Until TableExist("tblSharepointLink") = False
     
        Else: End If
     
    
    GetSPGUID is a custom function that I wrote to pull the GUID from a sharepoint list
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Does it just create the one table called tblSharepointLink?
    Where is it picking up the Sharepoint data list from?
     
  8. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    The code above just creates the one table, but this code runs over 800 times throughout the whole process.

    The variables URL and ListURL determine which sharepoint list is used for the linked table. These variables are populated through a select query.
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
  10. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    The "True" at the end decides if you want to lookup the display values for the list. I don't need to so i left it blank (default value is false).

    I can permanently link to the lists, but the paths are always changing. So i would have to run some code that goes through and deletes tables that no longer link to anything and create new ones. I tried this to begin with, but the database was much larger and it was slow all the time instead of just after it's been running for a while.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    When you perform the Transfer what happens if you do not delete the tblSharepointLink table?
    Does it keep adding data to the table?
    Or are the tables all different in terms of Fields?
     
  12. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    if i dont delete the table, it just keeps creating new tables by adding a number after tblSharepointLink (tblSharepointLink1, tblSharepointLink2, etc).

    The DoCmd.TransferSharePointList doesnt update the sharepoint list. It creates a linked table to the sharepoint list. Once that linked table is created, i run my action queries that update the list.

    There are 6 different table formats that it might be linking to. Each one has different fields.
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well I think I have just about exhausted any ideas I can think of.
    I did find some code for automatically refreshing the links, but if you say the links change maybe that won't help either.
    I assume that the tables must be very large for this to take so long and for the database to get so large.
    How often do you have to do the update?
    Is the database Split?
     
  14. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    The tables themselves aren't very large. The first group of tables takes about 30 seconds to update, but by the time it gets to towards the end of the list, it's taking 10-15 minutes.

    I have to do this update at least once a week, but usually every other day or so.

    The database itself really doesnt hold much data. nearly all the tables it uses are linked from other sources. I'm the only one who uses it so i never saw a point in splitting it.
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    A split database would at least remove the Front end, leaving a bit more space for the tables, it might even be possible to split it in to more than one back end.
    Have you tried a VBA Compact & Repair, does it interupt the looping process?

    I have been looking at your code and I wonder if it can be made any faster or more straightforward, although I wouldn't have thought it would make much difference if the earlier tables only take 30 seconds or so.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1027395

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice