Automate Fie Copy to Sharepoint

[yrstruly]

I am mowing data from alocal drive to a Sharepoint Drive.
I amende the VBA and it states FileCopy is not possible,possibly due to Sharepoint and it also does nothing/no action.

Amended code:

Option Compare DatabaseOption Explicit


'------------------------------------------------------------
' SAVE BUTTON: Saves the current record, then goes to a new record.
'------------------------------------------------------------
Private Sub btnSave_Click()
    ' 1. If there are unsaved changes, save the record
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    ' 2. Move to a new, blank record
    DoCmd.GoToRecord , , acNewRec
    
    ' 3. Optional confirmation message
    MsgBox "Record saved successfully! Ready for a new entry.", vbInformation, "Save"
End Sub


'------------------------------------------------------------
' UPLOAD BUTTON: Lets the user pick files to upload,
'               then copies them to SharePoint folder and
'               records them in tblDocumentStorage.
'------------------------------------------------------------
Private Sub btnUpload_Click()
    Dim fd As Object
    Dim filePath As String
    Dim fileName As String
    Dim fileType As String
    Dim transactionID As Variant
    Dim transactionType As String
    Dim PermitNumber As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim destinationFolder As String
    Dim fileItem As Variant
    Dim fso As Object
    
    ' Define the SharePoint folder path (UNC path)
    destinationFolder = "\\saplant.sharepoint.com@SSL\sites\SAPODBDocuments\Shared Documents\Export Documents\"


    ' Check which form is active and assign TransactionID & Type
    If Me.Name = "frmCultivarePermitExport" Then
        transactionID = Nz(Me!ExportID, 0)
        transactionType = "Export"
        PermitNumber = Nz(Me!Permit_No, "")
    Else
        MsgBox "Unknown form type!", vbExclamation, "Error"
        Exit Sub
    End If
    
    ' Ensure a TransactionID is selected
    If IsNull(transactionID) Or transactionID = 0 Then
        MsgBox "Please select an Export ID before uploading a document.", vbExclamation, "Missing Record"
        Exit Sub
    End If


    ' Ensure Permit Number is selected
    If PermitNumber = "" Then
        MsgBox "Please select a Permit Number before uploading a document.", vbExclamation, "Missing Permit Number"
        Exit Sub
    End If


    ' Open File Dialog (File Picker)
    Set fd = Application.FileDialog(3)  ' 3 = msoFileDialogFilePicker
    With fd
        .Title = "Select Documents to Upload"
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        .AllowMultiSelect = True  ' Allow multiple file selection
        
        ' Show the dialog and get the selected files
        If .Show = -1 Then
            ' If user picked at least one file
            Set db = CurrentDb()
            Set rs = db.OpenRecordset("tblDocumentStorage", dbOpenDynaset)
            Set fso = CreateObject("Scripting.FileSystemObject")
            
            For Each fileItem In .SelectedItems
                filePath = fileItem
                fileName = Dir(filePath)  ' Extract filename
                fileType = Mid(fileName, InStrRev(fileName, ".") + 1)  ' Get extension
                
                ' Error handling for file copy
                On Error Resume Next
                fso.CopyFile filePath, destinationFolder & fileName
                If Err.Number <> 0 Then
                    MsgBox "Error copying file: " & Err.Description, vbCritical, "File Copy Error"
                    Err.Clear
                    On Error GoTo 0
                    GoTo Cleanup
                End If
                On Error GoTo 0
                
                ' Insert a new record into tblDocumentStorage
                rs.AddNew
                rs!transactionID = transactionID
                rs!transactionType = transactionType
                rs!Permit_Ref = PermitNumber  ' Storing Permit Number instead of Permit ID
                rs!fileName = fileName
                rs!filePath = destinationFolder & fileName
                rs!DocumentType = fileType
                rs!UploadDate = Now()
                rs.Update
            Next fileItem
            
Cleanup:
            ' Close recordset
            rs.Close
            Set rs = Nothing
            
            ' Update PermitNumber in the Export table
            db.Execute "UPDATE tblExportInformation SET Permit_No = '" & PermitNumber & "' WHERE ExportID = " & transactionID
            
            Set db = Nothing
            Set fso = Nothing
        Else
            ' User cancelled the file selection
            Exit Sub
        End If
    End With
    
    Set fd = Nothing
    
    MsgBox "Documents uploaded successfully to SharePoint!", vbInformation, "Success"
End Sub
 The code that worked before is:

	
	
Option Compare Database
Option Explicit


'------------------------------------------------------------
' SAVE BUTTON: Saves the current record, then goes to a new record.
'------------------------------------------------------------
Private Sub btnSave_Click()
    ' 1. If there are unsaved changes, save the record
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    ' 2. Move to a new, blank record
    DoCmd.GoToRecord , , acNewRec
    
    ' 3. Optional confirmation message
    MsgBox "Record saved successfully! Ready for a new entry.", vbInformation, "Save"
End Sub


'------------------------------------------------------------
' UPLOAD BUTTON: Lets the user pick files to upload,
'               then copies them to a destination folder and
'               records them in tblDocumentStorage.
'------------------------------------------------------------
Private Sub btnUpload_Click()
    Dim fd As Object
    Dim filePath As String
    Dim fileName As String
    Dim fileType As String
    Dim transactionID As Variant
    Dim transactionType As String
    Dim PermitID As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim destinationFolder As String
    Dim fileItem As Variant
    
    ' Define the storage folder path
    destinationFolder = "C:\Data\INFORMATION\SAPO Databasis\DO NOT DELETE - DB Development Anthony\Combined\V1\"
    
    ' Check which form is active and assign TransactionID & Type
    If Me.Name = "frmCultivarePermitExport" Then
        transactionID = Nz(Me!ExportID, 0)
        transactionType = "Export"
        PermitID = Nz(Me!PermitID, 0)  ' For tblExportInformation
    Else
        MsgBox "Unknown form type!", vbExclamation, "Error"
        Exit Sub
    End If
    
    ' Ensure a TransactionID is selected
    If IsNull(transactionID) Or transactionID = 0 Then
        MsgBox "Please select an Export ID before uploading a document.", vbExclamation, "Missing Record"
        Exit Sub
    End If
    
    ' Open File Dialog (File Picker)
    Set fd = Application.FileDialog(3)  ' 3 = msoFileDialogFilePicker
    With fd
        .Title = "Select Documents to Upload"
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        .AllowMultiSelect = True  ' Allow multiple file selection
        
        ' Show the dialog and get the selected files
        If .Show = -1 Then
            ' If user picked at least one file
            Set db = CurrentDb()
            Set rs = db.OpenRecordset("tblDocumentStorage", dbOpenDynaset)
            
            For Each fileItem In .SelectedItems
                filePath = fileItem
                fileName = Dir(filePath)  ' Extract filename
                fileType = Mid(fileName, InStrRev(fileName, ".") + 1)  ' Get extension
                
                ' Copy file to destination folder (doesn't remove original)
                FileCopy filePath, destinationFolder & fileName
                
                ' Insert a new record into tblDocumentStorage
                rs.AddNew
                rs!transactionID = transactionID
                rs!transactionType = transactionType
                rs!Permit_Ref = PermitID  ' Storing PermitID in Permit_Ref
                rs!fileName = fileName
                rs!filePath = destinationFolder & fileName
                rs!DocumentType = fileType
                rs!UploadDate = Now()
                rs.Update
            Next fileItem
            
            ' Close recordset
            rs.Close
            Set rs = Nothing
            
            ' Update PermitID in the Export table
            db.Execute "UPDATE tblExportInformation SET PermitID = " & PermitID & " WHERE ExportID = " & transactionID
            
            Set db = Nothing
        Else
            ' User cancelled the file selection
            Exit Sub
        End If
    End With
    
    Set fd = Nothing
    
    MsgBox "Documents uploaded successfully!", vbInformation, "Success"
End Sub

.

[yrstruly]

This code gives me "Run time error 76":Path not found.

'------------------------------------------------------------' UPLOAD BUTTON: Lets the user pick files to upload,
'               then copies them to SharePoint folder and
'               records them in tblDocumentStorage and tblExportInformation.
'------------------------------------------------------------
Private Sub btnUpload_Click()
    Dim fd As Object
    Dim filePath As String
    Dim fileName As String
    Dim fileType As String
    Dim transactionID As Variant
    Dim transactionType As String
    Dim PermitID As Variant
    Dim PermitNumber As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim destinationFolder As String
    Dim fileItem As Variant


    ' ? SharePoint destination folder
    destinationFolder = "\\saplant.sharepoint.com@SSL\sites\SAPODBDocuments\Shared Documents\Export Documents\"


    ' Check if correct form is active and assign values
    If Me.Name = "frmCultivarePermitExport" Then
        transactionID = Nz(Me!ExportID, 0)
        transactionType = "Export"
        PermitNumber = Nz(Me!Permit_No, "")
    Else
        MsgBox "Unknown form type!", vbExclamation, "Error"
        Exit Sub
    End If


    ' Ensure ExportID is selected
    If IsNull(transactionID) Or transactionID = 0 Then
        MsgBox "Please select an Export ID before uploading a document.", vbExclamation, "Missing Export ID"
        Exit Sub
    End If


    ' Ensure Permit Number is selected
    If PermitNumber = "" Then
        MsgBox "Please select a Permit Number before uploading a document.", vbExclamation, "Missing Permit Number"
        Exit Sub
    End If


    ' Retrieve PermitID from Export table
    PermitID = DLookup("PermitID", "tblExportInformation", "ExportID = " & transactionID)


    ' Open File Dialog
    Set fd = Application.FileDialog(3) ' 3 = msoFileDialogFilePicker
    With fd
        .Title = "Select Documents to Upload"
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        .AllowMultiSelect = True


        ' Show dialog and process selected files
        If .Show = -1 Then
            Set db = CurrentDb()
            Set rs = db.OpenRecordset("tblDocumentStorage", dbOpenDynaset)


            For Each fileItem In .SelectedItems
                filePath = fileItem
                fileName = Dir(filePath) ' Extract filename
                fileType = Mid(fileName, InStrRev(fileName, ".") + 1) ' Get extension


                ' Copy file to SharePoint folder
                FileCopy filePath, destinationFolder & fileName


                ' Insert record into tblDocumentStorage
                rs.AddNew
                rs!transactionID = transactionID
                rs!transactionType = transactionType
                rs!PermitID = PermitID
                rs!Permit_Number = PermitNumber
                rs!fileName = fileName
                rs!filePath = destinationFolder & fileName
                rs!DocumentType = fileType
                rs!UploadDate = Now()
                rs.Update
            Next fileItem


            ' Close recordset
            rs.close
            Set rs = Nothing


            ' Update tblExportInformation
            Dim updateSQL As String
            updateSQL = "UPDATE tblExportInformation " & _
                        "SET PermitID = " & PermitID & ", " & _
                        "Permit_No = '" & PermitNumber & "' " & _
                        "WHERE ExportID = " & transactionID


            db.Execute updateSQL, dbFailOnError


            ' Clean up
            Set db = Nothing


            MsgBox "Documents uploaded successfully to SharePoint and export table updated!", vbInformation, "Success"
        Else
            ' User cancelled
            Exit Sub
        End If
    End With


    Set fd = Nothing
End Sub