Filecopy gives permission error on server drive, but works on C:\

[DougP]

Anyone know how to edit the permissions on the server folder?
Server 2003 standard.
this code runs fine on files on C:\ drive but on the server
\\servername\Share\... it gives error

' open access database
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim SourceFile, DestinationFile, OriginalPath As String
    
   'Create a new ADO Connection object
    Set cn = New ADODB.Connection
   
    With cn
       .Provider = "Microsoft.Access.OLEDB.10.0"
       .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
       .Properties("Data Source").Value = "\\foc01\shareddir\Student Fee Folder\Data from pdf forms.mdb"
       .Open
    End With
    
    'Create an instance of the ADO Recordset class, and
   'set its properties
    Set rs = New ADODB.Recordset
    'OriginalPath = "c:\Report Cards 11-12\"
    OriginalPath = "\\servername\share\Report Cards 11-12\" 'Error when using this path

    For b = 1 To 8
        With rs
            Set .ActiveConnection = cn
            .Source = "SELECT [Childs Last Name],[Childs First Name] " _
                    & "FROM [Data from pdf Latest] " _
                    & "Where [Entering Grade] = '" & Trim(Str(b)) & "' " _
                    & "Order by [Childs Last Name],[Childs First Name];"
            .LockType = adLockOptimistic
            .CursorType = adOpenKeyset
            .Open
        End With
        rs.MoveLast
        rs.MoveFirst

         GradeFolder = Choose(b, "First Grade", "Second Grade", "Third Grade", "Fourth Grade", "Fifth Grade", "Sixth Grade", "Seventh Grade", "Eighth Grade")
         SourceFile = OriginalPath & GradeFolder & "\" & Trim(Str(b)) & " Template Report Card 2011-12.xls"
         
         For a = 1 To rs.RecordCount
              DestinationFile = OriginalPath & GradeFolder & "\" & rs![Childs Last Name] & ", " & rs![Childs First Name] & " Report Card 2011-12.xls"
              FileCopy SourceFile, DestinationFile ' <<< ERROR here
              rs.MoveNext
         Next
         rs.Close
    Next
   'Set the form's Recordset property to the ADO recordset
   
    Set rs = Nothing
    Set cn = Nothing

DougP

 

[Andrzejek]

Do you have right permissions to the locations on your server?

And why do you have SourceFile and DestinationFile declared as Variants? They are just Strings.

Have fun.

---- Andy

 

[UnsolvedCoding]

Also you may want to check your references

 

[DougP]

UnsolvedCoding,

check your references

What am I looking for in references?
this is what I have not:

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft scripting runtime
Microsoft forms2 2.0 Object Library
Microsoft ActiveX DAta Objects 2.9 Library

I can copy and paste the file manually while in Windows Explorer in the server folder. like if I press Control-C and Control-V

it just does not work in VBA.
Is it perhaps the VBA needs a user in the server? I have seen this when I installed other software, it created a local user.


DougP

 

[Andrzejek]

Exactly what error are you getting?
Is it possible the file is 'in use'?


Have fun.

---- Andy