Microsoft Access Application: VBA ‘Save-As’ Dialog to Save Entire Application in a New Location

This lesson comes as part of a series on building Microsoft Access Applications

So this one was tricky, and it did take me quite a few minutes to perfect before it was working perfectly.

This procedure will allow you to open a “Save-As” dialog box in Microsoft Access and allow users to save a copy of the database they are working on in a new location.

Ironically, this would be a simple, two second, line of code in Excel, but in Access – it’s almost impossible. Almost.

Application.GetSaveAsFilename 'This code works in Excel, but not in Access

The application of this may sound fishy, but for what I was doing, it helped tremendously.

I built this into a “Developers Edition” of an application for my team so they can build databases from a fresh template and just save the entire database in their own location whenever they want.

Attached to a button, this code will open a dialog box, ask the user where they would like to save a copy of the database and what the name of that file should be, then stores that info as variables and executes the ‘save as copy’ with that information. It then closes the current DB they are in, and opens the one they just saved.

It’s not earth-shattering code, but from the looks of the struggle just by googling “Save-As VBA in Access”, I can tell this may help some people.

Sub Save_New_Project()

DoCmd.SetWarnings False

Dim NewDB As Object
Dim Old_Database_Path As String
Dim New_Database_Path As String

Dim Prompt As Office.FileDialog
Dim NewFilePath As String

Set Prompt = Application.FileDialog(msoFileDialogSaveAs)

Prompt.Title = "Save File"
Prompt.InitialFileName = "*.accdb"
Prompt.Show
NewFilePath = Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1)

Old_Database_Path = "C:\WhateverAccessDatabase.accdb" 'Change the filename here to the file you plan to save a copy of
New_Database_Path = NewFilePath

Set NewDB = CreateObject("Scripting.FileSystemObject")
NewDB.CopyFile Old_Database_Path, New_Database_Path, True

Dim New_Access_DB As Access.Application

Set New_Access_DB = CreateObject(NewFilePath)

DoCmd.SetWarnings True

DoCmd.RunCommand acCmdExit

End Sub