Results 1 to 3 of 3
  1. #1
    Michael_Red is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    May 2016
    Posts
    6

    Export access query to Sharepoint

    Hi


    I have been tasked with some old access databases that need updating. These databases produce reports that are then exported to our onsite server. We are now in the process of moving to SharePoint, can we still export to the query to SharePoint?

    We currently use DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Summary Report Period End", "\\localserver" & Format(Date, "yyyymmdd") & " Summary & Movement Report" & ".xlsb", True, "data"

    I tried to change the location of the save file to the SharePoint server and get a runtime error '2825': The file does not exist, or you don't have read access to the file. Have tried to speaking to our sharepoint people but they are not interested!

    Any help would be appreciated, thanks.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First, a disclaimer.. I have never used SharePoint. Don't know anything about it.

    I will be referencing these two sites: AcSpreadSheetType and DoCmd.TransferSpreadsheet

    I see three things that concern me.

    The Syntax is
    DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)


    1)
    For the SpreadsheetType parameter, I would use acSpreadsheetTypeExcel12Xml (see the AcSpreadSheetType link)


    2)
    And the path doesn't look right. Using today's date, 11/18/2021, the path & filename would be
    "\\localserver20211118 Summary & Movement Report.xlsb"
    Shouldn't there be a back slash after "localserver"?


    I would expect the path to look something like
    \\PC980\Users\Bozo\Downloads\lnt2_el4_el7b_el3_el7 .pdf

    Can you use the File Explorer to navigate to the location you want the file to be saved and get the actual path?



    3)
    You have "data" for the Range parameter. According to the DoCmd.TransferSpreadsheet link, this is only valid for importing.


    Good luck with your project....

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    A few of our users had access databases and ran into trouble when they tried to switch to sharepoint. We advised them to split the database and export the tables to sharepoint lists and link these to the access front end or use excel.
    For creating a file on sharepoint the account has to have write access to the sharepoint site.

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

Similar Threads

  1. Replies: 0
    Last Post: 09-18-2017, 09:02 AM
  2. Replies: 2
    Last Post: 09-10-2015, 11:22 AM
  3. Replies: 1
    Last Post: 07-24-2015, 07:06 AM
  4. Replies: 3
    Last Post: 12-16-2013, 09:44 AM
  5. import/export attachments to SharePoint list
    By Cojack in forum Import/Export Data
    Replies: 0
    Last Post: 07-15-2011, 01:39 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