[I want a free solution.]
I have a Windows mdb database.
Every night I wish to run a backup. I wish to compress the database and save it with a unique name consisting of the database name and date.
Is there a problem if the database is open in another application? What happens?
Thanks.
I did something like that for my mom.
It was dos commands in a .bat file that was then run with Task Scheduler. I’m sorry I don’t really have time to create that know, and would rather someone else critique that plan before I do.
Shouldn’t be a problem if it’s open. The first move will be to copy and rename. But the copy should put a time stamp on it anyway.
I’ll refrain from asking why you want to do this and just observe that the best way to perform this, assuming that Access is running, is to schedule an event in a VBA process to perform an export. If it is not running, you can run Access as in the Task Scheduler and execute an export routine.
I’m not sure what you mean by “compress the database”; Access has functionality to compact and repair the database (which should be done periodically to clean up the database) but this doesn’t compress anything; it just removes residual information. It sounds like you want to export the database to a text format (using SaveAsText) and then zip the text file via a shell command. This is all kind of clunky, but then that it basically the definition of Access.
Stranger
Macrium Reflect has a fully functional free version that might do what you want, including automatic scheduled backups from an XML script and compression, except that the free version (a) only backs up entire volumes, not specified files, and (b) will do full or differential backups, but not incrementals. File and folder type backups and incremental backups require the paid version.
It will back up files even if they’re open in another application, although obviously there’s no guarantee what that file might or might not contain.
If I run the Compress and Repair Database Utility it compresses the file to 118 MB.
If I right click the file name and select "Send to Compressed (Zipped) Folder it reduces the size to 32 MB, and I can also rename the file at the same time to include the date if I want. So this works fine as a manual process–but I want an automated backup process which runs every night.
As to why I want to do this I just had a problem where the database got badly messed up. And it must happened a few days previously, but I am not sure when. So I want to be able to restore the database from yesterday, look at it. If the data is not correct restore the data from two days ago, look at it and so on.
Compact and Repair; it doesn’t do any compression; it just removes historical information.
You should be able to do this using VBA and Windows Task Scheduler as described above. The problem with an external copy is that if Access is running and performing a write operation, you’ll likely end up with a corrupted backup. If you do it within Access (either exporting as text or saving to a separate database file) Access will hold up until the write process is complete.
Stranger
I would use something like 7-Zip rather than the built-in Windows file compression. 7-Zip is free and has a command line utility that you can call from a bat or cmd file to do the file compression. But I would definitely make sure the mdb file is not in use when zipping it. Is it a local file or a shared database on a network?
It’s a local file I manually update a few times a day, so there is no risk of an operation happening in the middle of the night when I am not using the computer.
Thanks everyone.
Right now I am looking at Hasleo, a freeware backup imaging program which will backup specified files like the paid version of Macrium Reflect does.
I will make the small but utterly vital distinction between “you using the computer”, and “the computer is turned on and Access is a running app and it has that database open”.
When you perform the compact / export / backup process you want to ensure that no copy of Access is running on any machine that can reach your database. Not knowing whether you have one PC at home or 15 I can’t get more specific than that. But if a copy of Access has the database open at the time your copy / backup operation tries to run, you will have problems eventually, and maybe every single time. So don’t do that.
Unrelated to the above. …
As @Stranger_On_A_Train said, the “compact” operation is, very loosely speaking, about the same as emptying the recycle bin on a disk drive. I would expect that if you compact the database in place, it might go from 118MB to 32. But just once. Tomorrow after some adds and updates it’ll compact from 32.8 to 32.4. etc. If you’re doing a compacted export, so your original isn’t being compacted, just the outgoing copy, then I’d expect the size reduction to happen each time.
I just checked out of curiosity, and Macrium Reflect is no longer free in any edition. Just FYI. If you have the free version, or can get it, it will continue to work, but you can no longer download it from Macrium. This just confirms my view that the world is getting worse in almost every way! 
Macrium Reflect was, and remains, really excellent backup software, and I can’t really blame them for discontinuing the free version.
Make friends with windows’ native backup functionality.
Macrium Free is still available at the traditional download sites which offer downloads of thousands of programs, such as:
But, correct, it is no longer being updated.
You said mdb file, which implies a very old version of Access, so I’m not sure if this is available but an option in Access is to compact and repair when the file is closed, that is when you close the db, or exit Access. That takes care of the compact requirement, and makes better sense since it only executes after you use the file, not over and over again regardless of whether you have done anything to the file.
To copy with a timestamp - https://shellgeek.com/powershell-move-file-and-rename-with-date/
Oh, I see that moves the file, you can use copy-item instead for the copy operation.