Results 1 to 2 of 2
  1. #1
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    84

    Manage MS Project Server Lists - Access DB size is Huge

    Hi All.



    We have Microsoft's Project Server to manage projects . Each project (thousands) has its own sharepoint (with all kinds of stuff) and 2 lists (I am interested in) + userinfo. I want to use ms access to manage, edit, update the lists (I have the url to each project site) - the current web based interface is too slow. With this new method, users do not need to go to each individual sharepoint site to update their info in the lists - all project lists are available from a list box in access.

    I tried 2 methods...
    1) I linked the 3 lists from each, all "Active" projects of a specific type (qty 103 - url and project info are in an sql server table that I link to to get my list of tables to link), with VB. Each group of 3 Lists on each sharepoint site is of the same name, so when I link, I rename each list with a prefix of project number so I know which is which.

    I have a list box with project numbers on a form, that once selected, all forms reset to link to the proper lists for the selected project. Seems to work well with regard to speed. Forms allow edits / additions / etc. But the database is huge, 560Meg! There are 103 * 3 = 309 linked sharepoint lists, 1 linked sql server table, 2 forms, 3 queries, 1 small vba module. The size is too large... I manage updates by checking versions using a "Starter Access DB"... If a new version is available, I automate downloading it, starting it up, and closing the starter. I am concerned with the 560meg, because users in asia will see download speed issues.

    Yes, I have compacted it, multiple times.

    With all local tables / forms / queries / modules exported to a new database - no linked sharepoint project lists, the db size if 1Meg.

    Why is it so large? Why do qty 309 linked tables add 559MEG?

    2) my second method was to use the list box, once a project is selected, only that 1 project's lists (3) are re-linked. When a new project number is selected in the list box, Delete the old linked tables, and relink the new ones that are associated to the selected project. Well, this method is too slow. Each time the lists need to be deleted, relationships are deleted first, then the linked tables are deleted, then relinking the new lists. The re-linking is the slow part.

    Any ideas??
    Thanks.
    Steve

  2. #2
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    84
    Update. By checking option for current database, "Clear cache" on exit, I got the file down to 60Meg. This is manageable

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-17-2019, 06:56 AM
  2. Access/SQL Server .adp (project) ado programming
    By dbeelow@comcast.net in forum Access
    Replies: 1
    Last Post: 06-28-2013, 01:53 PM
  3. Working on a huge project, need some input
    By AudiA4_20T in forum Access
    Replies: 12
    Last Post: 06-30-2011, 12:29 PM
  4. splitting tables to manage their size
    By aat in forum Database Design
    Replies: 5
    Last Post: 09-22-2010, 12:47 PM
  5. Database size is huge due to memo fields
    By swaroopbhave in forum Import/Export Data
    Replies: 2
    Last Post: 08-07-2010, 11:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums