Save-As on accdb file Open

[gblack]

Looking for code that does this:

When I open a specific accdb file, I am automatically prompted to save this file as...

Basically some autoexec code in the file itself that prompts me to save that file each time it's opened.

Thanks so much!
Gary

 

[Pat Hartman]

Where is the file stored? If it is inside a .zip file, this is what will happen. You MUST extract the database from the zip file if you need to do anything except view stuff. You also might see the save as if you down loaded the file from the internet and Windows thinks it is unsafe.

 

[Deleted member 147267]

All you want is a prompt to "Save As" the currently open db? This is because you might forget to do so otherwise?

Create AutoExec macro to open a message box, or have that macro run a function that presents the message box.

 

[gblack]

Nope, it's not in a zip file. It's not a split database. It's simply a regular ole MS Access.accdb file, on my hard drive.

All I want is code that allows me to save the accdb file that i'm opening.

No, this isn't because I might forget to save the file...

I want to continually use a copy of the current file. Let's just call it file "A" (or A.accdb)

So each time I open file "A" it will prompt me to copy itself as... I will then save file "A" as file "B" and work in file "B"

Then later I will open file "A" again... and save it "as" file "C"...

I can keep a shortcut to file "A" handy... and continually use it....

I'm not really sure why it's important to know why I want this code, but there ya go.

In any event, my question remains... is there code that will prompt file "A" to "save as" on open?

 

[Deleted member 147267]

No, this isn't because I might forget to save the file

I think you misunderstood my intent because you don't Save Access databases. Changes are saved automatically as you go with respect to records. Object design changes will produce prompts to save if you attempt to close them first. That's pretty much it.

So, you will open A, save as B, make changes in B. Later, reopen A and save as C. That's interesting.

I'm not really sure why it's important to know why I want this code, but there ya go

Because more often than not, the why leads to other suggestions that are often better. In this case, I have no improvement suggestion so won't ask further questions in that regard.

Seems to me that an AutoExec macro to raise a message box as a prompt in db A would be the way to go.

 

[gblack]

Seems to me that an AutoExec macro to raise a message box as a prompt in db A would be the way to go.

Yes, I'm asking how one would go about making the above happen, pragmatically.

 

[arnelgp]

I am unable to run "Save-As" from VBA.
but I made a sample, simulating what
you want to accomplish.

save the two db in same folder.
run TestMe db.

SaveAsDB is the one who will save TestMe db.
to your Documents\db_backup.

 

[isladogs]

It isn't clear whether you want to have a backup copy of code or data or both.

Anyway I can think of three simple ways you could do this.
1. Open the database as read only using the /ro switch in a shortcut.
2. Open the database using a vb script which includes making a file copy before the app is opened
3. Create a backup routine and save it as a function. Then run that function at startup using an autoexec macro or from the load event of your startup form.

However you do it will slow down the full loading of your database and is likely to annoy end users. A better approach might be to make a backup automatically when you close your app.

 

[gblack]

Thanks arnelgp that's closer to what I am looking for.

Isladogs (and everyone),
I don't really want a "back-up". What I am trying to get around is this:

Every time I create a new accdb file (sometimes several times a day), there's a myriad of set-up options I always change, in order to make the MS Access settings to my liking.

In truth, I am requesting this code, simply because I've gotten tired of doing this, each and every time I create a new MS Access Db file.

I slice and dice data all the time and use MS Access to do this and (for example) I can't really use MS Access in the default Tab format (because it doesn't allow me to see two or three datasets matched up against each other at one time (i.e. I can't eyeball deltas, in tab format, which I need to do in my job)

So to set a new accdb file, the way I like/need it... I have to go into File>>Options>>Current Database... click overlapping windows (along with a few other changes) then click OK... I am then warned that these changes will not take, until the database has been closed and reopened... instead I go back to File and choose compact & repair...

I have to do this EVERY TIME I create a new MS Access file... it gets tedious. This isn't a big deal because I have done it about 7.84M times... but I was thinking... since we use code to deal with repetitive actions like this, I am looking for a way to bypass these repetitive actions. So yesterday I created a brand new template database file, where the settings are exactly the way I want.

And...Yes, I could just go to the template (every time I need a new MS Access file) and copy it and then paste it somewhere else and then change the name yada yada... but I'd much rather have a shortcut to this template file, accessible on my tool bar, that I can click any time I need a new file... and when the template file opens, it simply saves this template file elsewhere (i.e. to a place on my hard drive, I've direct and with a new name that I give it) "save as".

So far arnelgp has given me something to chew on (thanks so much for creating this for me arnelgp, very much appreciated)...

If anyone can direct me to a link that would help me attain the above, I'd be extremely grateful.

Isladogs: Option #2 and #3 might be doable... do you have any code or links you might provide? I'm at a loss at the moment as to how to go about this. Also I work on a US-DoD machine, so I am not sure it would even allow me to use VB Script to copy the file... but if they do, perhaps that's really the avenue I need to pursue.

Thanks to everyone for the ideas!

Respectfully,
Gary

 

[theDBguy]

Hi Gary. Sounds like you were already halfway there already without using code. If you created a file as a template to base future projects, have you tried actually saving it as a template file, so when you go to File menu and select New, you can just select your template? Please pardon me for jumping in.

 

[gblack]

DBGuy,
I could do that... I wasn't thinking along those lines, so thanks for the suggestion!

Meh... I just tried it and in order to create a template I have to post a bogus object in there (i.e. MS Access won't save a file as a template, with only the changed settings there must be an object, in order to save it as a template).

So I made a bogus table that I'll have to delete each time, bit a big deal, still... I'd rather not have to do that. I guess it's no worse than having a (save as) module I would have had to delete... ok so that's a wash...

That said, it's much closer to what I am looking for but still has added steps, to get to what I want... since I am dealing with the issue now, I might as well try to create as few steps as necessary.

One of the issues (and maybe you can help me with this) is that MS Access always points me to "DOCUMENTS" not a huge deal but I almost always (initially) create my accdb files on my desktop (to work with) then move them later.

Is there any way to change MS Access' default path from my documents to my desktop...? If so, I think that'd work exactly the way I was hoping!

 

[Deleted member 147267]

We've gone from 'I want a reminder to do a manual save as when I open a db' to wanting code to create a new db with your desired db settings. I had suggestions for how to do a full 'save as' of the currently open db but figured all you wanted was a prompt.

Seems to me that an AutoExec macro to raise a message box as a prompt in db A would be the way to go.
Yes, I'm asking how one would go about making the above happen, pragmatically.

Even saving the opened db doesn't seem to be what you want - it's creating a new one with your option settings.

 

[theDBguy]

DBGuy,
I could do that... I wasn't thinking along those lines, so thanks for the suggestion!

Meh... I just tried it and in order to create a template I have to post a bogus object in there (i.e. MS Access won't save a file as a template, with only the changed settings there must be an object, in order to save it as a template).

So I made a bogus table that I'll have to delete each time, bit a big deal, still... I'd rather not have to do that. I guess it's no worse than having a (save as) module I would have had to delete... ok so that's a wash...

That said, it's much closer to what I am looking for but still has added steps, to get to what I want... since I am dealing with the issue now, I might as well try to create as few steps as necessary.

One of the issues (and maybe you can help me with this) is that MS Access always points me to "DOCUMENTS" not a huge deal but I almost always (initially) create my accdb files on my desktop (to work with) then move them later.

Is there any way to change MS Access' default path from my documents to my desktop...? If so, I think that'd work exactly the way I was hoping!

Hi. That makes sense. I was also thinking a template could include any color scheme you like to have. Rather than add a bogus table, if you'll always have at least one form in all your database, then maybe you could add a blank form instead. If you're always going to creat a form anyway, this could save you a step because all you have to do is go to design view right away. As for the default file location, I am not in front of a computer now but look up either Application.SetOption or CurrentDb.Properties.

 

[gblack]

hahaha...Micron, I never wanted a "reminder" to do a manual save. I wanted a way to save the file I created (with all the settings I have to change each time) as another Db file, when I opened it.

But yes, creating a template did the trick..
Also, I figured out how to change the default (Options>>General) to my desktop each time I open MS Access to create a new file.

So yeah, I think that does the trick. Thanks to DBguy for the answer...
Thanks to everyone for helping me work through the issue... this will be extremely helpful to me, moving forward!

Thanks so much!
-G

 

[isladogs]

I agree completely that the requirements seem to be shifting.
I think creating an Access template (ACCDT) is the best solution for what you want. This can include all the Access Options settings you want to use e.g. Overlapping windows, choice of default theme etc
I use a template for exactly that purpose and it works fine....but that's nothing like what you were originally asking for :banghead:

Change the default database directory in Access options - General.
I really wouldn't recommend saving large files to the desktop as these will need to be loaded each time you log on which will make getting started each time take longer

 

[theDBguy]

hahaha...Micron, I never wanted a "reminder" to do a manual save. I wanted a way to save the file I created (with all the settings I have to change each time) as another Db file, when I opened it.

But yes, creating a template did the trick..
Also, I figured out how to change the default (Options>>General) to my desktop each time I open MS Access to create a new file.

So yeah, I think that does the trick. Thanks to DBguy for the answer...
Thanks to everyone for helping me work through the issue... this will be extremely helpful to me, moving forward!

Thanks so much!
-G

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

In the Access options, you can change the default directory. This doesn't work anywhere near as well as it used to but at least it works in some situations.

 

[isladogs]

In the Access options, you can change the default directory. This doesn't work anywhere near as well as it used to but at least it works in some situations.

Yes I mentioned that in my last reply. However, no idea why you suggest it doesn't always work

 

[Pat Hartman]

Because it doesn't. Windows is obsessed with the cloud and the Documents folder. So when you open Access and want to find an existing app, this is what you are presented with. Notice that it does not include C:\Data which is MY default Access directory. This view used to include MY preferred directory and now it doesn't. It doesn't even include the most recent directory which is most useful and also used to be included. You can use two clicks - Recent and Folders to get a list of the most recently used folders. But what used to just be there, is now buried because someone went for change for the sake of change and decided that they knew best. You'd think that "Add a Place" would allow me to specify my preferred folder but no, that goes to the cloud also.

 

[isladogs]

Pat
Try clicking Browse. It goes straight to your default directory - in my case G:\MyFiles

EDIT:
I removed your screenshot as it contained personal info (your email). Hope that's OK with you