file backup 1

[kimtp]

Would like to backup an Access database. Right now I am using Filecopy sSourceFile, Filename which works until I open a record. Then I get a 'Permission denied' msg.

My code so far:
cn is the database connection

If cn.State = adStateOpen Then cn.Close
FileCopy sSourceFile, sFilename

Thanks.

 

[ettienne]

You cannot copy an open file. Close the file first or use backup software that will backup open files.

 

[waytech2003]

You say it works until I open a record.

Do you mean you are trying to open a record on the original database or the backup?

Did you reopen the connection before you try to get a record? Remebmer you closed the DB connection before you made your backup.

 

[kimtp]

When I start the program I can make a backup of the database. No records have been called at that time. However, after a record is called the error occurs whenver trying to backup.

Apparently closing the connection and recordset does not work. I tried Close(Filename) but that does not work either. Type mismatch. And that's as far as I can get.

Any ideas/help is appreciated.

Kim

 

[dglienna]

Set the connection and rs to nothing to release the memory.

-David
2006 Microsoft Valueable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[kimtp]

I added this code this morning:

    If rs.State = adStateOpen Then rs.Close
    Set rs = Nothing
    Set cdata = Nothing

and I still get 'Permission denied'.

 

[JerryKlmns]

kimtp

I use the same logic for closing and destroying a connection to a database and then copy the file. But between the close and copy there is a call to a sub (write a logg to a txt file for actions taken). If you give it some time to take a breath will that work?

 

[SBerthold]

>Apparently closing the connection and recordset does not work.

It may take a few seconds until the JET cache is actually flushed and the connection actually released.

FileCopy: The VBA.FileCopy will not allow to copy the opened MDB.

Use the FileSystemObject CopyFile method found in the Scrrun.dll (set a reference to "Microsoft Scripting Runtime").
It should let you copy the MDB, even when opened, when local, and maybe even when on the server.

Dim oFso As Scripting.FileSystemObject
Set oFso = New Scripting.FileSystemObject
Call oFso.CopyFile(srcFile, dstFile)
Set oFso = Nothing

 

[SBerthold]

Should mention that if a recordset is opened Pessimistic, or possibly on XP-SP2+ it may also be a problem.

But, you can verify this: See if the MDB can be copied, when opened, in Explorer, or not. If it can, then CopyFile should work, where as FileCopy will not.

 

[kimtp]

Thanx, Sammyb.

Exactly what I was looking for.

There is, however, something strange in that the date/time stamp is always the same. In this particular case the date stamp is 2/22/2006 7:02AM whereas the actual is 2/28/2006 7:34AM. Even if the date/time is altered in the control panel.