Backup Backend data file (Access 2000)

[johnhutchison]

On the Access Web at http://www.mvps.org/access/api/api0026.htm I found a function that uses API calls to backup the current database. (provided it is not open exclusively).

Is this a safe thing to do? I thought that trying to copy files while in use was dodgy.

I have modified the code so that it just copies the backend data file.

[WendellB]

Unless you have a database that is really being hammered by users, I think you can nearly always get away with simply copying the .MDB file - at least we've done it numerous times and never been burned.

[johnhutchison]

Thanks

Is there any advanatge using the API calls in this function, from just using VBA FileCopy ?

[WendellB]

Not to my knowledge - the API version was developed before we had that facility in VBA.

[johnhutchison]

I have now tried this.

If I use FileCopy to copy the backend data file of a db that is currently open, I get an error message because FileCopy cannot copy a currently open file.

The API method is happy to do it.

[WendellB]

To be honest, I've never tried to do it with a shell command and command line use of FileCopy. However I have done it repeatedly with Windows Explorer and the Copy/Paste process. I suspect the XCOPY utility should also work in a command window, but have not tried it.

[johnhutchison]

I wasn't using a shell command, just the built in VBA FileCopy.

XCOPY works OK in a command window.

I will just use the API method as it works OK.

I have a couple of jobs where I need to provide 'click a button' backup of the data from within the db without closing it down.

In each case there will usually be only one user (sometimes a second) at any one time so this method of just copying the backend file seems fairly safe.
.

[MarkD]

For some other options on how to copy a file programatically, see MSKB article 207703:

ACC2000: FileCopy Statement May Not Copy Open Files

Article provides some examples of using Windows CopyFile API function (a simplistic alternative to the more involved SHFileOperation API function), as well as examples of using Shell function to run the old MS-DOS Copy command in VBA.

HTH

[johnhutchison]

Thanks Mark.

I am looking at using the Windows CopyFile .

I am wondering if you really meant to describe it as 'simplistic' , rather than just 'simpler'.

Simplistic usually implies a simple solution that shows insufficient awareness of potential complexies, and so to be avoided.

In my experiments both it and SHFileOperation seem to achieve the same result. Is SHFileOperation safer?

[MarkD]

In reference to "simplistic" vs "simpler", that's a matter of semantics. "Simplistic" may best be applied to some of the old VB statements, which do not provide a great deal of flexibility or advanced options, but are sufficient for simple file operation tasks. The main reason for using the API in place of simpler methods (such as intrinsic VB statements or the Scripting FileSystemObject) is because it provides some functionality not provided by the other methods. For example, as noted in MSKB article 165919:

HOWTO: Use the Windows 95/98/Me Copy and Recycle Functions in VB

"The Windows API provides the ability to perform a copy, move, rename, or delete operations on a file system object using the SHFileOperation function that is exported by Shell32.DLL. The example below shows how to copy a list of files into a named folder and then move them to the Recycle Bin." And: "In Visual Basic 6.0, you can use the FileSystemObject Object to copy, move, rename, and delete files. However, the FileSystemObject does not provide a way of placing files in the Recycle Bin."

So if you wanted to send files to Recycle Bin, rather than deleting them permanently, you'd have to use the API. In one case where I had been using the SHFileOperation API to copy/move files & folders, wound up going back to using FileSystemObject, because when needed to modify code using FSO was a lot simpler (and quicker) to modify. Another reason for using the API would be, if it is possible that the Scripting library is missing or disabled on a target system, you could use the API methods as a "backup" (use FSO using CreateObject and late binding (no reference set); if error results, call API methods from error handler). In such a case you'd want to create a standard or class module to wrap the API functions. Where I work, Scripting being disabled is not an "issue" so I generally use FSO methods for file operations as the simplest approach.

HTH