VBA to save as existing file name with current date appended

[moke123]

Ah, apologies ... in that case I believe I do have a split configuration.

So the original DB is stored on the BE. A copy of that DB is then provided to a data validator. The validator uses queries to manipulate the data in the tables (of the copy). Once this process is complete the file is saved as the original DB name but suffixed with _COMPLETE_20212111

Hopefully that makes sense.

Still not sure we're on the same page and not sure I understand what a data validator is doing.

See this article from FMS on split databases.

Microsoft Access Split Database Architecture to Support Multiuser Environments, Improve Performance, and Simplify Maintainability

One of the most important architectural designs is splitting the database into a front-end and back-end database. This is the way Access was designed to let you support multi-user databases and significantly simplify how you enhance the application over time.

www.fmsinc.com

you should have 2 .accdb files. One is the front end with all your forms, reports, queries, code modules, etc. The other is your backend with only your tables in it. The tables in the backend are linked to the frontend objects.

any validation of the data should be done in your frontend forms before being saved in your tables, usually in the before update event of the form.

 

[Pat Hartman]

It's the renaming of the database itself once closed down. When it has been worked on the staff should Save As and suffix it with _COMPLETE_20212111 but if they forget, which happened recently, it causes significant problems. I just want to automate the process.

This is terrifying as a concept. You are using Access as a spreadsheet. I suggest you learn more about how a relational database works and rethink your entire concept.

If you are using forms to do your updating, then you can add a new form that opens when the db opens and then hides itself and opens the menu/switchboard. When the database closes, that hidden form will be the last one to unload. It can then create a backup with the correct name automatically.

 

[gemma-the-husky]

^^^^
@Pat Hartman is spot on. It's really hard to force Access to not behave like Access. A database tries to save record changes at every opportunity, and there's no "back" button to undo this. Not only is there on back button, there's no need for a back button, or daily copies, but it does need thinking about what you are trying to achieve.

 

[Pat Hartman]

I hated giving up the bullets to load the gun but it is a band aid that will work until the entire app is re-imagined as a database rather than a spreadsheet.