How to copy an already opened file to different location in VB?

Posted on 1999-01-13
Last Modified: 2010-05-03
I have a MS Access file that is already opened but I need to make a backup of this. In Windows Explorer, I can copy the file and paste it any where. Unfortunately, my vb app does not allow to copy. I get error 70 - Permission denied message. Any workaround?
Question by:hoosang

Expert Comment

ID: 1456038
Shell to a command line and try it.  

Someone posted some code here that allowed you to pass keystrokes to the shell prompt.  That should do the trick.

Author Comment

ID: 1456039
I still get 'File Not Found' Error Message.

Condition here is that I have an already opened MDB file here. This is the way I did but not successful.

stmp = "D:\Source\File1.MDB D:\Target\File2.MDB"
vRet = Shell(stmp, 0)


Expert Comment

ID: 1456040
Try folloing:

prompt$ = "Make Backup?"
reply = MsgBox(prompt$, vbOKCancel, dat.Databasename)
If reply = vbOK Then
 FileNM$= InputBox$("Insert name of the new file:")
 If FileNM$ <> "" Then FileCopy dat.DatabaseName, FileNM$
end if
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.


Author Comment

ID: 1456041
I tried FileCopy and Shell but neither worked.

Accepted Solution

cantrell earned 50 total points
ID: 1456042
try this (using the windows copy routine)
paste this in a module...
Option Explicit

      Private Const FO_COPY = &H2&   'Copies the files specified in the
                                     'pFrom member to the location
                                     'specified in the pTo member.
      Private Const FO_DELETE = &H3& 'Deletes the files specified in pFrom
                                     '(pTo is ignored.)
      Private Const FO_MOVE = &H1&   'Moves the files specified in pFrom
                                     'to the location specified in pTo.
      Private Const FO_RENAME = &H4& 'Renames the files specified in pFrom
      Private Const FOF_ALLOWUNDO = &H40&   'Preserve Undo information
      Private Const FOF_CONFIRMMOUSE = &H2& 'Not currently implemented.
      Private Const FOF_CREATEPROGRESSDLG = &H0& 'handle to the parent
                                                 'window for the progress
                                                 'dialog box
      Private Const FOF_FILESONLY = &H80&        'Perform the operation
                                                 'on files only if a
                                                 'wildcard file name
                                                 '(*.*) is specified.
      Private Const FOF_MULTIDESTFILES = &H1&    'The pTo member
                                                 'specifies multiple
                                                 'destination files (one
                                                 'for each source file)
                                                 'rather than one directory
                                                 'where all source files
                                                 'are to be deposited.
      Private Const FOF_NOCONFIRMATION = &H10&   'Respond with Yes to All
                                                 'for any dialog box that
                                                 'is displayed.
      Private Const FOF_NOCONFIRMMKDIR = &H200&  'Does not confirm the
                                                 'creation of a new
                                                 'directory if the
                                                 'operation requires one
                                                 'to be created.
      Private Const FOF_RENAMEONCOLLISION = &H8& 'Give the file being
                                                 'operated on a new name
                                                 'in a move, copy, or
                                                 'rename operation if a
                                                 'file with the target
                                                 'name already exists.
      Private Const FOF_SILENT = &H4&            'Does not display a
                                                 'progress dialog box.
      Private Const FOF_SIMPLEPROGRESS = &H100&  'Displays a progress
                                                 'dialog box but does not
                                                 'show the file names.
      Private Const FOF_WANTMAPPINGHANDLE = &H20&
                                   ' If FOF_RENAMEONCOLLISION is specified,
                                   'the hNameMappings member will be filled
                                   'in if any files were renamed.
 ' The SHFILOPSTRUCT is not double word aligned.  If no steps are
 ' taken, the last 3 variables will not be passed correctly.  This
 ' has no impact unless the progress title needs to be changed.
  hwnd As Long
  wFunc As Long
  pFrom As String
  pTo As String
  fFlags As Integer
  fAnyOperationsAborted As Long
  hNameMappings As Long
  lpszProgressTitle As String
 End Type
 Private Declare Sub CopyMemory Lib "KERNEL32" Alias "RtlMoveMemory" (hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)
 Private Declare Function SHFileOperation Lib "Shell32.dll" Alias "SHFileOperationA" (lpFileOp As Any) As Long
Public Function ShellCopyFile(Source As String, Dest As String)

Dim lenFileop As Long
Dim foBuf() As Byte

lenFileop = LenB(fileop)    ' double word alignment increase the
ReDim foBuf(1 To lenFileop) ' size of the structure.

'Set the attributes to normal first.
If Len(Dir$(Source)) Then SetAttr Source, vbNormal
If Len(Dir$(Dest)) Then SetAttr Dest, vbNormal

  'Set all of the copy parameters
   With fileop
    .hwnd = Form1.hwnd
    .wFunc = FO_COPY
    'Source file or directory.
    .pFrom = Source & vbNullChar & vbNullChar & vbNullChar
    'Target or Destination directory.
    .pTo = Dest & vbNullChar & vbNullChar
    .lpszProgressTitle = "Copying files to... " & Left$(Dest, Len(Dest) - 1) & vbNullChar & vbNullChar
   End With
    ' Now we need to copy the structure into a byte array
     Call CopyMemory(foBuf(1), fileop, lenFileop)
     ' Next we move the last 12 bytes by 2 to byte align the data
     Call CopyMemory(foBuf(19), foBuf(21), 12)
     If SHFileOperation(foBuf(1)) <> 0 Then    'Operation failed
      MsgBox "FILE COPY OPERATION FAILED! " & Chr$(13) & "ERROR CODE: " & Err.LastDllError, vbCritical Or vbOKOnly
      If fileop.fAnyOperationsAborted <> 0 Then
        MsgBox "FILE COPY Operation Failed", vbCritical Or vbOKOnly
      End If

    End If

End Function

now, use this in your form when you want to copy the file...
shellcopyfile "path and filename of file to copy", "path and filename to copy to"


Expert Comment

ID: 1456043
Each time a file is opened, sharing clauses are specified (or taken from default values) when open is executed. If Access opens the file in such a way it permits no further access (this is, file is not shareable), you have no way to gain access to it.

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question