Making a copy of an open database - error messages

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In order to make a copy of the open database, I use the following code:

CopyString = "CMD.EXE /C COPY " & SourceFile & _
" " & DestinationFile
Call Shell(CopyString, 0)

It works fine. However, in case there is an error, no message appears. That
is, if the destination is in a memory key or a diskette, and there is no
memory key inserted, or not enough space in the diskette, the copy is not
made and there is no error message. I wonder why that happens.

(Before, in Windows 98, I used a slightly different code, which doesn't work
in Windows XP. Instead of CMD.EXE /C COPY, I used COMMAND.COM /C COPY - and
then the error messages worked!)

Any help would be appreciated.
 
You should never copy an open database: there's a risk that the copy will be
in an inconsistent state.

Once you know that the database isn't open, why not simply use the built-in
VBA FileCopy command?
 
The database in question must have a daily backup, and I feel it more likely
to be done if the operator has just to click a button, before closing it, at
the end of the day.

I have been using that COMMAND.COM /C COPY code for three years now - but
after reading your warning, I will definitely check randomly the backup
copies for problems.
 
If your database is split into a 'back-end' data file and a 'front-end'
application file, then the user can back-up the closed data file using a
button or menu command in the application. Here's an example from one of my
own apps. In this app I just warn the user to make sure they don't have any
bound forms or reports open and to ask other users to do the same. This app
is mostly single-user. In the few cases where it is used in a multi-user
situation, there are only two or three users. This approach has worked well
enough in that scenario. If there were larger groups of users involved, I'd
probably need to do something a bit more sophisticated, like maybe
attempting to open the data file exclusively to ensure that there were no
other open connections. This code probably won't work 'as is' in your app,
because it refers to other procedures and constants defined elsewhere in my
app, but it should serve as an example to give you some ideas.

strPrompt = "Before completing your backup, please ensure that no other
" & _
"forms (other than this 'Other Tasks' menu form) or reports are
open, " & _
"and ask any other users who may be using the same data file " &
_
"on your network to do the same."
If MsgBox(strPrompt, vbOKCancel Or vbInformation, gstrcAppTitle) = vbOK
Then
strFilter = "Microsoft Access Databases (*.MDB)" & vbNullChar &
"*.MDB" & vbNullChar & vbNullChar
Set db = CurrentDb

'Get path to data file from Connect property of linked table ...
strInput = db.TableDefs("tblStudents").Connect
strInput = Mid$(strInput, InStr(1, strInput, "=") + 1)

strOutput = db.Name
strOutput = Left$(strOutput, Len(strOutput) - Len(Dir(strOutput)))
strOutput = strOutput & Format$(Date, "yyyymmdd") & ".MDB"
strPrompt = "Where do you want to save your backup, and what do you
want to call it?"

'Call to GetSaveFileName API funcction. See
'http://www.mvps.org/access/api/api0001.htm
'if you need code for this.
strOutput = GetSaveFileName(Me.Hwnd, strOutput, strFilter,
strPrompt, 1)

If strOutput = strInput Then
strPrompt = "You have selected the same name and location as the
" & _
"existing data file. Please choose a different name, or a "
& _
"different location."
MsgBox strPrompt, glngcOkInfo, gstrcAppTitle
ElseIf strOutput = CurrentDb.Name Then
strPrompt = "You have selected the same name and location as the
" & _
"existing application file. Please choose a different name,
or a " & _
"different location."
MsgBox strPrompt, glngcOkInfo, gstrcAppTitle
Else
Me!lblStatus.Caption = "Copying data ... please wait."
DoEvents
FileCopy strInput, strOutput
Me!lblStatus.Caption = "Finished copying data."
DoEvents
End If
Set db = Nothing
End If
 
I make a mistake by copying the database while users were still in. I
modified it and now users sometimes encounter error message. Is there a way
I can repair this problem? Please, help! Thanks, Ling.
 
Like Matt, I have copied an opne database 1-2 users for several years without
a problem, and I too need to back it up daily at the touch of a button.

Can you describe in more detail what the issue could be? I thought that
when a multi-user .mdb was copied, the process of copying was just as if
there was another "user' using it (hence the ability to copy an open file).
If this is not the case, what is the issue?

Thanks.
 
Back
Top