Solved

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

Posted on 2011-03-04
10
767 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
ID: 35042040
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
ID: 35042070
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
ID: 35042073
its error 70 how do i trap that?
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:PeterBaileyUk
ID: 35042109
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
ID: 35042113
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
 

Author Comment

by:PeterBaileyUk
ID: 35042133
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
ID: 35042135
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
ID: 35043171
<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
ID: 35044436
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
ID: 35045127
thanks guys very interesting responses.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Modal form 11 30
Access Crosstab Query with Multiple Values 4 32
Column Layout in Access Xp VBA Report 3 19
DCount Type Mismatch 2 22
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

809 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