Converting Private Sub to Public Function

[ctackett6407]

I have been on a mission to help automate a task I do every morning.

A person created an Access application years ago that we still use to compare two databases and kind of run all kinds of queries and tables. When I open the application I click a "Run All" button and then it just goes off and running and completing the list of commands inside the modules.

I've asked questions and poked around and found that I might be able to get this to work with Task Scheduler if I create something as a Public Function , add a macro somewhere, and then use Task Scheduler to invoke the access application with some type of parameter.

I'm been trying to figure out where to start on this as I reviewed the application the person created and everything in it is Private Sub.

Do I have to convert this or can I convert this? I'm hoping I'm going to be told to "run a replace all for Private Sub, because it's just that easy!"

 

[ctackett6407]

So I attempted to make a macro and I selected these options

when I attempt to run it I get the following

 

[JLGWhiz]

1. copy the code to a public code module.
2. Open the vb editor and click on Tools>Macros
If your macro does not appear in the dialog box then it is not in a public module.
3. Omit the Parentheses ( ) from the macro name when entering in the scheduler. They are not part of the name.

 

[Micron]

If that is a sub, you can't call it from a macro or anything else that requires use of a function. Since it looks like a click event for some control, and such events are subs, not functions, that would be an issue. If you need to run a sub, you'll have to create a function and use it to call the sub.

 

[ctackett6407]

I'm learning and I have microsoft documents pulled up while I type this.

To do what you suggested @Micron , do I create a module and then put the function in the module that calls the sub?

I have to look up how to do this.

You are correct by the way. I click on a button on a form that runs this sub. It's a "Run All" command that runs a bunch of queries.

If that is a sub, you can't call it from a macro or anything else that requires use of a function. Since it looks like a click event for some control, and such events are subs, not functions, that would be an issue. If you need to run a sub, you'll have to create a function and use it to call the sub.

 

[Micron]

A function called by a ribbon button, toolbar button or a macro has to be in a standard module - and do not give them both the same name. So if you must initiate this from a macro, then yes, I would create a module (e.g. mdlDbFunctions) and put a function in it. Use the macro action to run the function and call the button click event from the function. You will have to change the Private keyword on the button click code (assuming you have any) to Public or it won't work.
I'm not understanding why macros are involved in this if you are using code.

 

[ctackett6407]

@Micron I'm trying to automate a process I have to do randomly all day.

I have to open an .mdb file and click on the "Run All" button and it goes through a ton of queries and updates various things based on criteria. I'm hoping if I can figure something out that I can set this up to run every few hours on it's own and I can stop doing it.

 

[Micron]

EDIT - your function will likely have to set the focus to the form first if you attempt to do anything within the button click code that requires it to be so.

 

[Micron]

Did you consider task manager to initiate the process according to your needs?
I don't have your db or fully understand the process, but I have done a similar sort of thing before, and the only macro I needed was AutoExec so that it would run when the db opens. Apart from that, there were no other macros - all functions and subs, all automated.

 

[ctackett6407]

@Micron This is what I found online.

Goal: is to be able to do the "Run All" command on a schedule without having to leave the database open or have to monitor it at all.

I found some stuff on another site that read that I should make a macro, tie it to a batch file, then hook the batch file into Windows Task Scheduler and set it up that way using a command code with the function tied to the end of it? Something along those lines.

This is a huge learning process for me, but I'm learning a lot in the reading and discovery.

This application was written years ago by someone who was versed in Access and VB. Right now I open this report and click the Run All and 15 or so minutes later it tells me it's complete. The report goes through all kinds of queries and syncs up data between two databases and populates fields i one database that are found in the other (syncs).

When people work their job all day they end up calling me and asking "hey sync the database please" and I have to keep stopping what I'm doing or if I'm in a meeting I can't do it. It also stinks when I take a day off because I have to log into my computer and update the system.

So this was a solution I was trying to come up with.

Did you consider task manager to initiate the process according to your needs?
I don't have your db or fully understand the process, but I have done a similar sort of thing before, and the only macro I needed was AutoExec so that it would run when the db opens. Apart from that, there were no other macros - all functions and subs, all automated.

 

[Micron]

Well, that doesn't explain why you need a macro, but no matter.

Not that I say this was any better, but I also had a multi-user situation with an autoexec macro that called the startup code. If the db had no Command property value then a person was opening the db and automatic updating part didn't run but the user validation part did. The db had a button so that the user could perform their own update if for some reason the automatic update didn't happen the night before. Can you not just let them do their own updating?

Anyway, the TS line to open the db at night had a command line switch, which when used to open a db, sets the db Command property value to whatever the switch is. In that case, the code would validate that it was a pc (task scheduler) opening the db, so it would automatically update the back end tables and skip anything that had to do with logging anyone in and so on. Before doing an update, I created a backup file in case it all went south. Then performed the updates and if OK, then over-wrote the backup at that point. This way, if anything bad happened, I had all the user modified data up to the point just before the backup. That happened whether or not TS or a person opened the db. Maybe some of that will provide a bit of insight to just one of what must be a hundred ways to do this.