Save-As on accdb file Open (1 Viewer)

[Pat Hartman]

Thanks I didn't notice the email.

Maybe this wasn't where I was having the problem. I'll pay attention for a couple of days to see if I can isolate the issue I was having.

 

[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

 

[gblack]

Doc Man...

The point is this: Every time I open a new MS Access accdb... I have to open MS Access (which on my Govt computer, takes time) and create the accdb file... then run through these steps: goto File>>Options>>Current database>> change the settings I prefer>>click OK>>click OK again, when the Message pops up that tells me settings won't be applied, until you close the Db and reopen>>goto repair & compact and click that...then start whatever process I needed the database for.

This process contains way more steps, than I would like it to take... and that I have been taking, for years now...

That said, I am looking for a way to rapidly copy a template Db... So making a template in Access does work, but I still have to open access (which, like I said... is slow). So, I am looking for more speed.

I don't want to do all that every time, so my thought was that I would make a "template" accdb file... put a shortcut to it on my windows tool bar so whenever I need a database (with all my settings) I simply click the shortcut and BAM! a I am prompted to save the file with all my custom settings (giving it whatever name I like).

Then... if i need another new accdb file (for some unrelated dataset)... I simply click that shortcut again and BAM! I am prompted again to save the file again, but with some other name, rinse, repeat, over & over...

Anyway, that's what I am looking for. Underlying business reason: It saves me time, each and every time I have to create a new Db (which I do, often).

That said, I don't know how to write the VBA (or VB Script) to make this work, which is why I am asking all you Big Brain Folks to help me with that, if you can.

The one set of code I got (I think from Pat, I apologize I should not have assumed gender) was close to what I was asking, but not quite and I figured I would make attempts to fiddle with it, at a later date, and perhaps figure it out... BUT... if you guys know of an answer, I'd love to get it.

So, Doc Man does what I wrote (above) make sense? If not I am happy to try to answer whatever I can, I guess I am not so great at explaining stuff.

Isladog...

Yes, you gave me high level options when I was specifically asking for help with code. But yes I did read and try #2... but I couldn't get that to work:

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.

Going in order:
1. Is too clunky, not what i'm looking for. I want to quickly open the Db into:"Save As".

2. I actually attempted the VB script, but I couldn't get it to work. I'm not adept with that... and I googled it, but I couldn't find the code that would let me do the aforementioned things, I listed above (i.e. Prompt for a "Save AS"). Like I said, I was asking for help with code... I'll admit I don't know how to write this code.

3. Is MUCH closer to what I want... But honestly, I felt: that's what I was asking for and you were suggesting that I do this...I don't know how to do this, but if you do, I'd be grateful for your help, or for any links that might help me figure this out.

In the end, the template thing does work, and is faster, but it's still slow for me because I have to open Access each time, but it's way better than nothing!

So, it wasn't exactly what I was looking for, but it did do something close and so I figured that's what I was going to get. As such, I was trying to be grateful and gracious by saying it worked for me and by thanking EVERYONE for their input.

It kind felt like I was asking for a kayak and I ended up getting a canoe...So, I was thankful for getting that... at least it something and it floats. Still, I wasn't asking for the canoe, exactly...

Isladog I apologize for getting off on the wrong foot, I do appreciate yours and everyone's help and input... I know you guys are doing this to help people, so thank you!

If any of you have a closer solution (as to what I explained, above) I would very much appreciate the help. If I somehow rubbed folks the wrong way, and there's no chance of further help... then I am truly sorry and the template solution will have to work for me

In any event I thank everyone for their time and attention and if I do end up figuring out how to code this all by myself, I'll certainly post it here.

Thanks again to everyone!

Sincerely,
Gary

 

[Micron]

Personally, I suspect the template idea is your best option. Sure, your shortcut can point to it and you will be prompted to Save As, which ought to solve your problem as I read it. I think that having code to do this has 2 basic issues.

1) you'd have to open that code db in order for it to 'bake' you a new db, so that won't save any effort.

2) For an accomplished VBA coder, this would be like digging for water. Possible, but so much easier to turn on the tap, if you get my drift. If not, then think of the template as the tap. Thus the chances of you finding anyone who has done this are pretty remote IMHO. Regardless, if you did then re-read issue #1.


All along I've been wondering why you create so many databases like you would compose emails or Word documents but was reluctant to ask. I have to wonder if that is the crux of your problem. If the data is not even remotely related, then you must be in a strange business indeed. If sectors of it are unrelated it still might make more sense to house it in one db as opposed to what you seem to be doing. In one database, I have seen
- RCA (root cause analysis)
- FMEA (failure modes and effects analysis)
- maintenance (work orders, requests, scheduling, planning, execution)
- purchasing (PO's, requisitions, suppliers, invoicing, etc)
- stores (locations, stock, counts, transactions, etc)
- BOM's (bill of materials)
- equipment hierarchy
- work order costing
- and more... so I'm curious about why so many db's. Maybe you don't really have to do that.

 

[isladogs]

The attached file includes a routine to make an automatic compacted backup at startup.
It contains a module modBackup with a CopyCurrentDatabase procedure. It is a function so it can be run from an autoexec macro at start-up. Just copy both items to use in your own applications

The backup is saved to the same folder and has the same name as the original with the current date/time suffix e.g. Autobackup_20191004070114.accdb

If you want, you can shorten the code to remove any parts you don't need such as the first message box so it runs without prompting the user

 

[sxschech]

Here are two other options using Batch files.

Option 1:
Create a batch file with a parameter prompt. Then the batch file will copy the "template/blank" file to the name you provided and run the cmd line statement to open access and the selected file.


Option 2:
Since you are starting with a blank database and those don't take up much space, create a bunch of files (maybe 10?) at once and then as you need them, rename one of them and your good to go, no need for access to open and copy it etc. Create a batch file on your desktop and then whenever you need a fresh set of files double click on the batch icon.

From https://itknowledgeexchange.techtar...ple-times-and-rename-it-different-every-time/

If it is for a PC you can create a batch program with a text editor.

simply change to the dir where the file is stored.
then enter the file copies.

CD\ mydir
copy myfile.txt myfileA.txt
copy myfile.txt myfileB.txt
copy myfile.txt myfileC.txt
Then save as a batch PGM for example MYCOPY.BAT

This could also be incorporated into your access file and run from there and you could also set it up with a loop and fso to dynamically create the batch file if you want to prepopulate with different names.

 

[The_Doc_Man]

Government computer? I worked with the U.S. Navy for 28 1/2 years, at a site requiring Secret clearance to step on the floor, and we NEVER had something that severe for MS Access usage. We had a few issues with file sharing across a fairly large but still local domain because of multiple firewalls in use, because they didn't allow SMB to cross certain boundaries. Other than that, no biggies.

If you are in a situation where Access is that restricted, I am surprised you would be ABLE to consider writing a VB script to implement a file copy followed by a launch. If Access is that badly tied down, I don't see how VB would be allowed either. Which means you have one of two things: An insane security group (in which case you have my deepest condolences) OR a bad policy setup.

There is also something wrong here that you either don't know about or aren't telling me regarding the ability to just save files with your desired settings. What version of Access are you using? I almost don't have to ask whether you are on Windows 10 because Win7 was NEVER that wonky about saving and storing things.

I have enough government experience and enough understanding of security issues to know that something is wrong here but sadly, not enough to be able to pinpoint it from your description. Perhaps a discussion with your IT Security team might help you get some answers or even give them impetus to review their policy vis-a-vis Access.

 

[theDBguy]

Hi Gary. I agree with Micron. I tried to do a little experiment and although what you're asking for is possible, I tend to think it might be too much work than it's worth. For example, here's what I tried.

1. I couldn't find a way to invoke the built-in Save As feature in Access, so I created a function to copy the current db into a new file.
2. I called this function in an Autoexec macro, so it will automatically launch when you double-click your shortcut to open the original db.
3. In the function, it has an area to close the current db, after it's been copied, and then launches the newly created db.

I believe this is what you were asking for, correct? You double-click your shortcut to open your db, which automatically creates a copy of itself and then you're free to use the copy, leaving the original db intact.

However, here is the problem, as I see it...

1. When the copy of the db opens up, the Autoexec macro will fire and you could end up in an endless loop (copying and launching the same db over and over)
2. Or, if you manage to exit the loop (for example, the function fails because the new db copy is not yet trusted), then you would have to do some cleanup first to remove the function and the Autoexec macro before you could use it, which could be just as much as a time consuming process as the one you're trying to avoid or save with your current process or with using a template.

I also did another experiment, which I think could save you some time. I created a template, just like you have now. Then, instead of opening Access and then selecting File > New > from Template, I simply created a shortcut to the template file (.ACCDT) itself and double-clicked it. As a result, it automatically asked me to save a copy of the template as a ACCDB, which then allows me to use it immediately. In other words, I still think the template approach IS the correct approach, you just have to use it in other than the normal way to get what you wanted.

Hope this helps...

 

[gblack]

Doc_Man: I have been working for the Navy for over 9 years; can't talk about clearance, or where I work within the Navy (not because it's that big a deal, it isn't, but just because it's stuff we're told not to divulge, probably shouldn’t even say Navy, but I think that’s OK).

NMCI switched to Windows 10 a couple years ago (a couple different version of Access but most recent 2016) and recently all environments, where I am at, have become more and more oppressive, due to pressing Cybersecurity matters. End result: EVERYTHING runs far slower and the bandwidth, where I am at, for some shared environments is horrendous. There’re also other environmental issues, which slow things down, like CITRIX and the fact that our equipment is not the most fantastic in the world (a tech refresh would be nice).

Not sure where you worked, in the Navy specifically, but I'm 100% positive that there are better and worse places, for any given aspect; the Navy's a pretty big Organization.
Given the above, all that I am attempting to do is speed things up, any way I can. I felt, what I requested would help me move a little faster.

Truthfully I don't know a lot about VB Scripting (I've only done some basic things with that)... so, I tried doing a few things with it, but I couldn't figure out how to apply the Windows API to allow me to "save as", so I didn't get too far. I think I could use it on my laptop if I could get that “Save As” api to pop-up.

Micron: I am not a newbie, when it comes to Access or VBA. But I try not to act like I know stuff, in forums where I am asking for help. That said I a far from a newb. I have been using it since it was Access 2.0. (started using Access in 1996). That said I don't feel I know everything and sometimes I feel there might be some holes, here and there, in my VBA. I couldn't figure out how to do this one thing, exactly, but I'm pretty sure I use Access correctly.

I do use larger centralized files, but I don’t like dealing with them, when I have small things to tackle.

I often get Ad Hoc data requests that sometimes take 4 to 12 different queries to churn into usable report data. I then (mostly) spit that out to Excel to send back to our users. Sometimes I save the accdb and place it in an organized location, where I can reuse it. Sometimes I just chuck it, when I know it’s just a quick and dirty.

The template works, and now Db Guy has opened my eyes to the fact I can open the accdt file up directly... I think the matter is officially solved!

Respectfully,
Gary

DbGuy... I just saw this and will respond. Let me get a second to read...

 

[gblack]

DB Guy! You rock!

I also did another experiment, which I think could save you some time. I created a template, just like you have now. Then, instead of opening Access and then selecting File > New > from Template, I simply created a shortcut to the template file (.ACCDT) itself and double-clicked it. As a result, it automatically asked me to save a copy of the template as a ACCDB, which then allows me to use it immediately. In other words, I still think the template approach IS the correct approach, you just have to use it in other than the normal way to get what you wanted.

THIS IS PERFECT! Exactly what I want/need and no code necessary to do it! Fantastic! I never would have thought of trying to open the template file from that long location where access places them!

So now, I simply create a shortcut to the (.ACCDT) on my tool bar... when I click it I get to bypass opening Access and it prompts me to save it as a new file AND it's already open and ready to be used!

PERFECTO!

 

[isladogs]

The template works, but what I am requesting would work better for me, of this I am certain.

Whilst I also think the template approach is better, just checking whether you saw my autobackup application in post #27

 

[gblack]

Yes,
I just saw that and clicked the Thank you... I will definitely take a look!

Thanks so much!

 

[theDBguy]

DB Guy! You rock!

THIS IS PERFECT! Exactly what I want/need and no code necessary to do it! Fantastic! I never would have thought of trying to open the template file from that long location where access places them!

So now, I simply create a shortcut to the (.ACCDT) on my tool bar... when I click it I get to bypass opening Access and it prompts me to save it as a new file AND it's already open and ready to be used!

PERFECTO!

Hi. I'm glad to hear we were finally able to offer you something you could use. Good luck with your project.

 

[The_Doc_Man]

GBlack - I was on NMCI from the moment it came into existence, i.e. before everything got unified. I retired at about the time that NMCI was switching to Win10 in our site, which was NEDC New Orleans. Unless you are in a TS location, it is OK to say where you are. You cannot talk about the specific projects unless they were clearly known to the public. We had 80 different projects at our site, of which maybe 55-60 were SBU/FOUO; the remainder were on their separate floor with set of a cell-phone lockers outside the door. You draw your own conclusions.

I was a sys admin for a personnel system that was totally inside NMCI; i.e. we used NMCI login as a secondary confirmation of identity. (Yes, that is possible if you have the right kind of "trust" setup.)

Your discussion of restrictions surprise me simply because that never happened for us up to the day I retired. However, from the other thread members, you got something that appears to have done the job. For that I say, "great" - but just watch out for future paranoia. The reason I eventually retired was because of creeping training requirements. I was over 68 and they sprang a yearly 40 CEU on top of the Security+ and Sys Admin certificate requirements, and it was on my nickel to do that. AND it was on a way that the contracting company couldn't reimburse me because there was no billable entity. So I told them that on X date I was gone and would not be fulfilling the requirements for security next year because I would be gone before the beginning of the current fiscal year. That last day, when I turned in the CAC, I felt free like I hadn't felt in years!

By the way, I knew a G Black at NEDC New Orleans but he was an ORACLE DBA. No relation, probably, because there were literally tens of thousands of names in the NMCI address book, and I'm pretty sure I'm no longer active there. But if you ARE that G Black, say hello to Patrycia and the kids.

Good luck with your project.

 

[Micron]

Re: your first #1 bullet - the reason you can't find a way to invoke a save as in code is because there is no equivalent. What happens when you invoke that is that Access programmatically copies all the objects into a new db - same as if you created a new db then imported everything into it. If you want to automate that you have to write a lot of code. I suspect that's why a template can't be saved without at least 1 object.