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
