Results 1 to 3 of 3
  1. #1
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016

    SharePoint document libraries, using Access to grab inventory and then report.


    I am not a SharePoint admin. Our company has one, but I would prefer to come up with a good plan for him to examine prior to engaging him on this project need.

    I am well-versed in Access, and a little VBA...

    The Current: <Caveat - I did NOT design this process, but I am very interested in improving it.
    A department stores its documents in a SP site which serves as a queue of inventory for jobs for them to process.
    Each document is a job, for instance.
    There are 33 separate document libraries/folders, each needing to be inventoried every day.
    Currently, we use one excel spreadsheet per library(33), and something called an "iqy" file to run some XML and retrieve a listing of the documents in a single library.

    The method for refreshing these each day is to open each Excel spreadsheet, it then uses the IQY associated with it (1 of 33) to retrieve a listing of documents in that single folder/library. After that connection refreshes, we save and close.
    The spreadsheets are then linked as tables in MS Access, where it combines them and puts them into a Crosstab query for examining how those documents are "aging".

    The Need:
    My frustration is related to the opening/closing of each of these documents. We have done some VBA within Excel to automate the open/close, but the refreshes are not occuring consistently enough to use this auto-open/close code. Not withstanding any fix for said VBA, the person who is preparing this data each day has a slow machine, where opening/closing Excel repeatedly takes longer than it should. Ideally, we'd be able to move this entirely into Access.

    My thoughts...
    Could a singular Iqy file be written to grab inventory from all of these libraries, and simply indicate a category for each one so that it can be split out again later?
    OR, could a SQL query within Access perform the same operations as one or all of these iqy files?

    Here is a snipet of what the iqy code looks like: [Identifiable data redacted]
    RootFolder=/teams/Dept/Client Name - Program Name
    Can this be done with either one iqy that grabs from 33 libraries, or can we do this with SQL and accomplish the same? Seems like there should be a way!

  2. #2
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    UPDATE: I have figured out how to add a linked table to one of the 33 Sharepoint "Lists", but when I examine the data, it just shows #DELETED in all of the fields/records. Any idea why it is doing this? I have noticed in another DB connection that this means the data is Null or not available for some reason.

    UPDATE, UPDATE: The real data does appear in the table for only a split-second, then appears to revert to #Deleted. Same with a query running against the linked table... When "convert to local table" is chosen, only 33 records of the 623 I am expecting come through into the local table. (I have verified at this moment the inventory should be showing 623)

  3. #3
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    UPDATE #3: Running the query as a make table seems to store the real values, but is inconsistent in this database. I am engaging my SharePoint admin for assistance. I'll report back on findings.

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

Similar Threads

  1. Replies: 0
    Last Post: 07-10-2018, 12:37 PM
  2. Replies: 3
    Last Post: 06-17-2016, 12:05 PM
  3. Access VBA - Libraries and Plug-Ins HELP
    By adke001458 in forum Access
    Replies: 7
    Last Post: 04-07-2014, 11:53 AM
  4. Recreate Word document in Access Report
    By cphelps48 in forum Reports
    Replies: 2
    Last Post: 11-29-2011, 04:32 PM
  5. Replies: 0
    Last Post: 03-26-2010, 09:11 AM

Tags for this Thread

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 - Senior Forums