VBA Code Question

[Guest]

Can anyone see what I am doing wrong with the FileCopy line??? What is going
on here is that the code is reading a folder and taking all the mdbs and
compacts them into a new folder as a backup and then moves them out to a
shared area for safety backup.

The FileCopy line is blowing and I don't know why. Is there any guidance
someone can give me???

Public Function BackUpFiles(sSourceDir As String, sDestDir, kDestDir)
Dim FileSys As FileSystemObject
Dim File As File
Dim Folder As Folder
Set FileSys = CreateObject("scripting.FileSystemObject")
Set Folder = FileSys.GetFolder(sSourceDir)
For Each File In Folder.Files
If Right(File.Name, 3) = "mdb" Then
If Not Dir(sDestDir & "\" & File.Name, vbDirectory) =
"" Then
Kill sDestDir & "\" & File.Name
End If
DBEngine.CompactDatabase sSourceDir & "\" & File.Name,
sDestDir & "\" & File.Name
'Kill sSourceDir & "\" & File.Name
FileCopy sDestDir & "\" & File.Name, kDestDir & "\" &
File.Name
End If
Next
End Function

Thanks in advance

 

[Guest]

Hi, Joe.

You don't mention what the error message is. The problem could be lack of
directory “write†permissions, lack of disk space, lack of a proper network
connection, using the wrong function, missing References, or maybe something
else. By “using the wrong function,†I mean that using a different function
to copy the files might work where FileCopy( ) fails.

FileCopy( ) will fail if the mdb file is currently open. FileCopy( ) can
also fail when the computer is set to restrict execution of unsafe Jet
expressions (“Sandbox modeâ€) within Access after Jet 4.0 SP-8 has been
installed. To check for this, see the Windows Registry key:

\\HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\engines\SandboxMode

If the value in this key is either 1 or 3, then the Sandbox mode is too
restrictive to use this function.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

 

[Dirk Goldgar]

Can anyone see what I am doing wrong with the FileCopy line??? What
is going on here is that the code is reading a folder and taking all
the mdbs and compacts them into a new folder as a backup and then
moves them out to a shared area for safety backup.

The FileCopy line is blowing and I don't know why. Is there any
guidance someone can give me???

Public Function BackUpFiles(sSourceDir As String, sDestDir, kDestDir)
Dim FileSys As FileSystemObject
Dim File As File
Dim Folder As Folder
Set FileSys = CreateObject("scripting.FileSystemObject")
Set Folder = FileSys.GetFolder(sSourceDir)
For Each File In Folder.Files
If Right(File.Name, 3) = "mdb" Then
If Not Dir(sDestDir & "\" & File.Name,
vbDirectory) = "" Then
Kill sDestDir & "\" & File.Name
End If
DBEngine.CompactDatabase sSourceDir & "\" &
File.Name, sDestDir & "\" & File.Name
'Kill sSourceDir & "\" & File.Name
FileCopy sDestDir & "\" & File.Name, kDestDir &
"\" & File.Name
End If
Next
End Function

Thanks in advance

So what error number and message do you get? Have you verified that the
folder specified by kDestDir exists?