Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-04
10
Medium Priority
?
776 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 1000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

604 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