Exporting data to a Teams folder (is it possible)

[kevlray]

So I have a request to export some data (MS-SQL backend) to a Teams folder (as an Excel file). Since they want it near real time, I know I will have to write some code when a value changes, but I am not sure if is even possible. Thanks in advance.

So further information that (might) be helpful. Organization has OneDrive available to our users. In looking at how Access exports data, I discovered that it can see OneDrive folders. I do not know if this is a feasible route.

 

[Ranman256]

normally youd send to a file:

vFile = "c:\temp\myfile.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qsMyQuery", vFile, True,"MyData"

so either the vFile can be replaced with your team folder
or
export to a normal drive (like the example) then add a 'copy' command like:
Copy1File vFile , [path to team folder]

Public Sub Copy1File(ByVal pvSrc, ByVal pvTarg)
Dim fso
On Error GoTo errMake
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile pvSrc, pvTarg
Set fso = Nothing
Exit Sub
errMake:
MsgBox Err.Description & vbCrLf & pvSrc, , "Copy1File(): " & Err
Set fso = Nothing
End Sub

 

[The_Doc_Man]

This is a GUESS but I believe that Access wants to use SMB for practically anything originated by VBA - but a closed file might not matter. So if I were a betting man, I would go for the option to create the file locally, then as a separate step, copy it as an entire file, where SMB would be less of an issue. Then perhaps delete the local copy after the Teams folder has been updated. But that is a guess.

 

[theDBguy]

If you're trying to upload the file to the OneDrive cloud storage, you might be able to start here.

Uploading files - OneDrive API - OneDrive dev center

docs.microsoft.com

 

[kevlray]

Attempting to export to OneDrive did not work as expected (the file name, i.e., path to the OneDrive location was too long). I think a better and easier solution is to give the users a simple FE that just has the results of the query (I don't think they really need an Excel file, just the results).

 

[The_Doc_Man]

Just curious, here... was the path more than 255 characters long? Web browsers don't have that limitation, but because you have to store names in a short text field, Access DOES have that limitation. But there might be a "cheat" available.

Can you create a batch file using string concatenation and create the desired data file, then tell Access to run that batch file via a SHELL command? You can write a text file to expect long records, you just have to explicitly write vbCRLF where each record is supposed to end. Another thought: If you can create a drive letter mapping for part of that long path, you can do the copy to the drive letter.

 

[kevlray]

It was when I tried to put in the file path for the DoCmd Tranferspreadsheet....

 

[The_Doc_Man]

Ah, I think that explains it. Access is known to have limited strings for some of its function arguments. That call to TransferSpreadsheet obviously uses an argument passage method that truncates strings. Which means it is NOT using "traditional" VBA string passage, because that is done using a string descriptor that can handle much longer strings up to 64KB. (The length field is 16 bits in that case.) There is a string data type from older systems called "Counted ASCII" or "ASCIC" that uses a byte for the byte count of the string, followed by the string itself, that limits you to 255 bytes.

It might be possible to create a way around this using the SUBST command as a way to define a path to associate with an otherwise idle drive letter and then take your long path, split it into the part covered by SUBST and the rest of the path, which you would tack on behind the new drive letter. Read up on the Windows Command Line (CMD) verb SUBST as a way to do this. There probably will also be a way for Access to make a WinAPI call to do this.