Export access query to Sharepoint

[Michael_Red]

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.

[ssanfu]

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....

[NoellaG]

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.