Looking for Backup Strategies (Access 2002)

Topic

New Reply

We need to include a backup module in the Access 2002 VBA application that we are writing. When the system goes live, it will be split into a front end (programs) mdb and a back end (data) mdb. The backup file will be written to CDRW and its name should include the current business date which is stored in one of the tables. The user requires a “hands off” approach. In other words, the less action that the staff needs to take, the better.

I was thinking of copying the tables via a DoCmd.CopyObject statement, one for each table. I can copy all tables this way. That is not a problem. My question is: Is there a fast and easy way to copy the entire back end mdb file, in one shot, from the front end mdb?

Thanks.

Reply | Quote

Replies

As long as the backup is performed from the frontend, from an unbound form with no bound forms open, you can use the “FileCopy” statement to copy the backend to another location.

HTH
RDH

Reply | Quote

Here is a sample routine that should work for you:

Public Sub BackUp()
On Error GoTo Err_Backup
Dim strMsg As String, strTitle As String
Dim strSource As String, strDest As String
Dim strError As String

strMsgComplete = “The Backup of the Database was Sucessful.”
strTitleComplete = ” Backup Complete”

BeginBackup:

Reply | Quote

Ricky:

Thank you so much. Once again, you have come to my rescue! This is exactly what I was looking for. I tried the basic code you provided and it works perfectly.

It’s curious but I don’t see a reference to FileCopy in VBA Help. That is part of what threw me off. Do you happen to know why there is no reference in Help?

Thanks, again.

Reply | Quote

FileCopy Statement is in A97 Help, but not in A2K or higher. I have to keep Help file of all versions just in case.

TIm K.

Reply | Quote

Thanks for the info. Tim.

Reply | Quote

Hmmmm ……..
I just took a look in my Access 2002 help and went right to FileCopy. It is in the VBA help files. To access it you must be in the VBA code window then cilck on help from that window. This is another one of Microsoft’s “Great Ideas” I guess …. You need help to work with the help files …. LOL

RDH

Reply | Quote

I stand (sit) corrected! I was looking in VBA Help but I was using the Index tab. It is not listed in the index, at least as far as I can tell, but it is listed under Statements on the Contents tab. Go figure!

Thanks.

Reply | Quote

I posted this question several months ago and tried the solution posted at that time on a non split database. It worked. Now I am attempting to try it again, but this time I split my front end mdb file (using the Splitter wizard). Let’s call the original file a.mdb. The wizard created a_be.mdb. I also have a linked table in the unsplit mdb. Let’s says that it refers to b.mdb.

Here’s the problem: When I now try to use filecopy on a_be.mdb it says “permission denied” (At the moment, this was tried on Win ME. The target OS will be Win 2K Pro but haven’t tried it there yet). When I try it on b.mdb it works. I noticed in Windows Explorer that there is a lock file called a_be.ldb open when the front end (a.mdb) is open but there is no ldb file for b.mdb. I assume that’s why I am getting “permission denied” when trying to copy the back end file.

Any suggestions for copying a_be.mdb? Thanks, in advance.

Reply | Quote

What does a lock file (or its absence) on b.mdb have to do with copying a_be.mdb? The simple answer is that you can’t copy an mdb with an active lock on it.

Reply | Quote

Access won’t copy a locked file but a batch file seems to do it OK and you can call the batch file from Access.

HTH

Peter

Reply | Quote

Thanks, Peter. I’ll give it a try.

Reply | Quote

Thanks for the reply.

I mentioned the lock file on b.mdb to support my theory (now confirmed) that an mdb with a lock file on it cannot be backed up. b.mdb does not have a lock file associated with it and can be filecopied. I’m sorry if I did not make that clear.

Reply | Quote

With regard to the title of the post

Reply | Quote

Thanks for the thought, Rupert. I’ll definitely keep it in mind.

BTW, the bat file idea works very nicely in Win 2K Pro. It even closes the DOS Window when it’s done. It does not close the DOS window in Win ME.

Reply | Quote

I saw this function which you might be interested in.
See::>>>
Code samples
Private Sub ExportTablesAsText()

At AAD Consulting

Reply | Quote

Thanks for the tip, Rupert. This site seems to have some valuable stuff. I’ve saved the URL.

Reply | Quote