Solved

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

Posted on 2011-03-04
10
774 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

688 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