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

 

[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.

 

[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

 

[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

 

[The_Doc_Man]

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.

OK, maybe I'm denser than normal right now. But I'm still not following what is going on here. The first time you modify file A and save it to file B, why can't you just open file B the next time and have all the settings in place? Why would you NEED to save it as file C if the settings that you were describing are now correctly handled?

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 understand you wanted to save an updated file A to file B. Then you wanted to save it as file C. And so on... but my question is, what is the underlying business reason?

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

The reason it is important is that we might be able to give you better options if we knew the end goal of this seemingly tedious process. What happens to B, C, and subsequent copies? And how many of these copies are we discussing?

 

[gblack]

Isladogs,
I disagree with any assessment that there was ever a shift in requirements.

I simply acquiesced to the template option and was trying to be nice about it, given that no one was posting answers to what I was actually looking for, (but I think Pat may have been close). That said, I may go back and play with his code. If i'm able to make it work, I'll post the answer and what I was looking to do; if it's still unclear to you.

Honestly and, in fact, I'd still be interested in figuring out the programmatical answer to what I originally asked for if you feel you can provide it.

The template is a workaround, but I'd rather be able to create a copy to my accdb file, as it opens, using a shortcut... this would be faster solution than having to open MS Access startup and choose a template each time...

 

[isladogs]

Isladogs,
I disagree with any assessment that there was ever a shift in requirements.

I simply acquiesced to the template option and was trying to be nice about it, given that no one was posting answers to what I was actually looking for, (but I think Pat may have been close). That said, I may go back and play with his code. If i'm able to make it work, I'll post the answer and what I was looking to do; if it's still unclear to you.

Honestly and, in fact, I'd still be interested in figuring out the programmatical answer to what I originally asked for if you feel you can provide it.

The template is a workaround, but I'd rather be able to create a copy to my accdb file, as it opens, using a shortcut... this would be faster solution than having to open MS Access startup and choose a template each time...

In post #8, I gave 3 possible solutions to what you originally seemed to be requesting. Have you tried any of those ideas? If so what were your results?

Your later explanation in post #9 seemed to indicate that using a template would do exactly what you want.

Which suggestion did Pat make that was close? BTW Pat is female

If you can make this work to your requirements, please do post your solution here for the benefit of others