Solved

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

Posted on 2011-03-04
10
770 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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

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.

Question has a verified solution.

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

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…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

821 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