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