Macro to save entire database as runtime

[Chky071]

I have built an Access 2007 database. I have a backend database and multiple front ends for multiple users. The front ends are saved as .accdr (runtime).



My current process is from within the full version of access I make changes to the original and then do a save as database "file_name.accdr" and that creates the runtime version that I then distribute.

I want to automate this process, is there a way to write a macro that will save the entire database as a .accdr?

Thanks!

[Micron]

I don't think so. But you can run a batch file. Not sure if how well this works if you're trying to save the db you have open (I think it will). To create a batch file (.bat) you use NotePad as save with the .bat extension (like CopyDb.bat). Once created, you don't OPEN these files unless you want them to run automatically. You right click to EDIT if all you want to do is look at it. Here's code for what you want to do (the batch file has to be in the same folder as the db, else this has to be coded to switch directory folders). This code snippet will handle the case where different users have different drive letters for the same volume in a network environment - something that took me a long time to find.

rem to copy MyDatabase to backup database
rem must be run from this folder

cls
@echo off

rem next line sets directory to current location, but using their assigned drive letter (e.g. F, L, etc.)
set MyDir=%~dp0

rem now use that value by reference
cd %MyDir%

:CopyFiles
rem you can remove this line. If your db to copy from has same name as new db, this will cause an overwrite
copy %MyDir%NameOfYourDatabaseHere.ProperExtensionHere %MyDir%NameOfYourDatabaseHere.accdr /y

:quit

So if you're a macro guy (I'm not) there doesn't seem to be an easy way to open a file like this. An interim step would be needed. Your macro would have to RunCode and specify RunBatchFile() as the code to run. First you'd have to create a standard module (open vb editor and Insert > Module, save it as mdlWhateverYouWant and enter

Function RunBatchFile()
  Application.FollowHyperlink "complete path to batch file here in quotes"
End Function

If you're going to get into vba programming, make sure you learn how to turn on Option Explicit.
All the above assumes you will have no Windows Trusted Location issues or that you will know how to solve them. It also doesn't provide any error trapping capability since macros have none anyway.

[CJ_London]

what is the benefit of having a macro/code to automate saving as .accdr? All you have do is change the file extension. Or am I missing something fundamental?

[Micron]

I just took it as a request for automating a backup or overwrite of a front end as is often done. I assumed it to be no different than a request for distributing an accde, mde or mdb.

[Chky071]

Ajax,

I am looking for a simple way to distribute/overwrite multiple front ends.

[Chky071]

Thank you! I will try this and let you know how it goes.

[Micron]

Note that the copy line in the batch file only produces one copy in one location. If you're doing this as a batch of overwrites for distribution, you'll have to add a copy line for each location, and any db that is left open will likely not get copied. A more efficient method is to have the FE compare its version number to the latest version number stored in a be table. If they are out of sync the fe code does the download before allowing the user to proceed.