Solved

Check if file open and close it in vba prior to copy

Posted on 2011-03-04
10
764 Views
Last Modified: 2013-11-27
I have used this
Call FileCopy("d:\Data\smmt\smmt master.mdb", "d:\data\smmt\previous\SMMT_MasterPREV.mdb")

this works fine however I would like to check that the file is not open prior to copy and if open close it and then perform the copy.

How do i do this in vba?

0
Comment
Question by:PeterBaileyUk
  • 7
  • 2
10 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
From the Help file:

"If you try to use the FileCopy statement on a currently open file, an error occurs."
I forget what the error number is, but you can trap it.
So, if you try the Copy and that error occurs ... then the file is open. You can test on an open mdb to find out the error number.

However, if the mdb is open ... I don't know of any way you can close it.  The user who has it open will have to do that, unless you possibly you have a forced shutdown scheme in place.

mx
0
 

Author Comment

by:PeterBaileyUk
Comment Utility
If the connection cannot be broken can I copy the db another way that will copy it but leave the connection to the original?

0
 

Author Comment

by:PeterBaileyUk
Comment Utility
its error 70 how do i trap that?
0
 

Author Comment

by:PeterBaileyUk
Comment Utility
ok got the error handler working
On Error GoTo Handler
Call FileCopy("d:\Data\smmt\smmt master.mdb", "d:\data\smmt\previous\SMMT_MasterPREV.mdb")

   
 
Handler:

    MsgBox ("Cannot copy and backup smmt master file as it is held open by a user onsite")
0
 

Author Comment

by:PeterBaileyUk
Comment Utility
I assume thats it, I can only inform a user that the copy never took place.

Would there be a windows command to copy the file if it were open that could be invoked from vba, I ask as windows does allow me to copy irrespective of the lock file
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:PeterBaileyUk
Comment Utility
I founhd some code on the devx website that allows this all working now irrespective if user left db open
Option Explicit

Private Declare Function CopyFile Lib "kernel32" _
  Alias "CopyFileA" (ByVal lpExistingFileName As String, _
  ByVal lpNewFileName As String, ByVal bFailIfExists As Long) _
  As Long
Public Function APIFileCopy(src As String, dest As String, _
  Optional FailIfDestExists As Boolean) As Boolean

'PURPOSE: COPY FILES
'PARAMETERS: src: Source File (FullPath)
            'dest: Destination File (FullPath)
            'FailIfDestExists (Optional):
            'Set to true if you don't want to
            'overwrite the destination file if
            'it exists
            
            'Returns (True if Successful, false otherwise)
            
'EXAMPLE:  
  'dim bSuccess as boolean
  'bSuccess = APIFileCopy ("C:\MyFile.txt", "D:\MyFile.txt")

Dim lRet As Long
lRet = CopyFile(src, dest, FailIfDestExists)
APIFileCopy = (lRet > 0)

End Function

Open in new window

0
 

Author Comment

by:PeterBaileyUk
Comment Utility
do i accept your solution to close this off?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
<I founhd some code on the devx website that allows this all working now irrespective if user left db open>

It is a bad-bad idea to copy in-use files, especially Access database files. Doing so leaves you with no knowledge of whether the action completed successfully or not - it just copies the file, and if the file is in-use, there is no way of knowing if the copy is good or bad. That's almost as bad as no copy at all (and some would say worse).

You can use the JetRoster method to determine if there are any connections to the database:

http://msdn.microsoft.com/en-us/library/Aa165308

The article if for Access 2000 but should work on any version of Access, including 2010. Note that if you do use this method, it will ALWAYS return one user, since when you open the connection you will become that user. So you'd check for MORE than one user. If you find more than one user, then someone has the database open.

Alternatively, you can use 3rd party utilities that implement the Shadow Copy methods to do this. This would be a far more robust and reliable backup method.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
Comment Utility
re: "You can use the JetRoster method to determine if there are any connections to the database:"
But the Error 70 tells you that

RE :ok got the error handler working"
Almost, but you need to amp it up a bit ... something like

On Error Resume Next
Call FileCopy("d:\Data\smmt\smmt master.mdb", "d:\data\smmt\previous\SMMT_MasterPREV.mdb")

Select Case Err.Number
     Case 0
        ' n/a no error occurred
         MsgBox "Copy completed ..."
         Goto Continue
     Case 70
        ' File is open - do what ever here
        MsgBox ("Cannot copy and backup smmt master file as it is held open by a user onsite")
        Err.Clear
        Goto Continue
      Case Else
        ' Unexpected error ...
        MsgBox "Unexpected Error " & Err.Number & "  " & Err.Description
        Err.Clear
        Goto Continue
End Select
   
Continue:
     ' more/other code if necessary


End Function
0
 

Author Closing Comment

by:PeterBaileyUk
Comment Utility
thanks guys very interesting responses.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now