How to run a macro in the background

[kdonovan]

I know absolutely nothing about VBA so please make everything somewhat simple.

I've set up a macro that runs 2 queries, one to move a record to another table, the other to delete the record from the original table. Is there a way to set this to automatically run, in the background?

Also, I have a query that I would like to run everytime someone adds something new and to have it emailed to me. Is there a way to do that?? Also in the background?

[NeoPa]

Running automatically generally implies some sort of trigger. Whether it be date/time related or a result of some other action. Without the basic trigger it is hard to answer your first question.

As for the second, if you can give a clear understanding of what you mean by "adds something new" then we can probably help. This should be possible.

As a general rule, all code runs in the background in as much as you only get to see what it shows. Otherwise, the operator will need to wait while the process runs before continuing.

[kdonovan]

Running automatically generally implies some sort of trigger. Whether it be date/time related or a result of some other action. Without the basic trigger it is hard to answer your first question.

As for the second, if you can give a clear understanding of what you mean by "adds something new" then we can probably help. This should be possible.

As a general rule, all code runs in the background in as much as you only get to see what it shows. Otherwise, the operator will need to wait while the process runs before continuing.

NeoPa,

Can the trigger be when the person exits?? Or can it be a certain time even if the database isn't open??

The "adds something new" I'll explain what the database is used for and hopefully that will help.

We use the database to track quote requests, the sales rep puts in all the info and right now tells me that they have added something new so I know to go in and get the info so I can build the quote in another program. I was hoping there was a way for an email to be sent to me after they put in the request (one or more) so the rep doesn't have to waste time emailing me about the request it's just automatically done. I've done a query that just pulls the new requests and I've found a macro that will run the query and send it to me but there is a pop-up screen that asks if I want to send the email and I'd like to have it set up that there is no pop-up. So far I haven't done anything in VBA, I've only used queries and macros. I have found some info online but I have no clue what to type to start or end the.... command, I guess. I know nothing about VBA, but I'm willing to learn.

Thanks.

[NeoPa]

There are so many possible triggers that it's almost impossible to list them all. Any event can be a trigger. There are various events for each object, and the number of objects there are is still growing. Closing the database object is certainly an event that can be handled though, so yes to that one.

As far as email goes, there are problems there that are related to security. I believe there are various threads which discuss these topics, but I find I have no notes on any of them to hand. It's certainly complicated, especially for someone new to it all.

Certainly though, code can easily be triggered when a form is closed (which would probably coincide with when they had completed entering the quotes).

I should make it clear that, though we are happy to help as much as is practicable, we're not really in a position to drive a whole project for you. That doesn't by any means imply we're not prepared to help, but for a task as large as this, we would need you to be doing the bulk of the work. I hate to put off anyone who's willing to learn, but this does seem a pretty complicated undertaking for someone who knows nothing about VBA.

[kdonovan]

There are so many possible triggers that it's almost impossible to list them all. Any event can be a trigger. There are various events for each object, and the number of objects there are is still growing. Closing the database object is certainly an event that can be handled though, so yes to that one.

As far as email goes, there are problems there that are related to security. I believe there are various threads which discuss these topics, but I find I have no notes on any of them to hand. It's certainly complicated, especially for someone new to it all.

Certainly though, code can easily be triggered when a form is closed (which would probably coincide with when they had completed entering the quotes).

I should make it clear that, though we are happy to help as much as is practicable, we're not really in a position to drive a whole project for you. That doesn't by any means imply we're not prepared to help, but for a task as large as this, we would need you to be doing the bulk of the work. I hate to put off anyone who's willing to learn, but this does seem a pretty complicated undertaking for someone who knows nothing about VBA.

NeoPa,

Ok, then forget about the emailing part, how can I have the macro (named Copy & Delete) run in the background at a certain time?? I pick things up pretty quick so I think once I understand this I can probably figure out the emailing thing. I mostly just want to know about what exactly I have to type first to make the whole thing start, from what I can tell from other stuff online and the "help" in access is that it seems to start with some sort of sub and it ends with, well end. I found that I can have the form "refresh" so I don't have any forms that say delete, I can play until I figure it out. So I guess I'll go with a time trigger and work/play from there. I'm using a copy of the database so if I screw up it doesn't matter.

Thanks.

[NeoPa]

Sounds like a good attitude :)

I will explain later (as I'm in a rush to leave atm) about timer-based scheduling, but it's certainly much more complicated.

[Jim Doherty]

NeoPa,

Ok, then forget about the emailing part, how can I have the macro (named Copy & Delete) run in the background at a certain time?? I pick things up pretty quick so I think once I understand this I can probably figure out the emailing thing. I mostly just want to know about what exactly I have to type first to make the whole thing start, from what I can tell from other stuff online and the "help" in access is that it seems to start with some sort of sub and it ends with, well end. I found that I can have the form "refresh" so I don't have any forms that say delete, I can play until I figure it out. So I guess I'll go with a time trigger and work/play from there. I'm using a copy of the database so if I screw up it doesn't matter.

Thanks.

If I am reading you correctly it seems to me you wish to initiate Access outside the bounds of personally being in the program itself. An automatic DIY type of thing. This can be done but as Neopa has rightly said already, it does require a significant and sound knowledge of scripting using a combination of disciplines VBScript files (files that have an.vbs extension) and/or batch files .BAT extension, both of which can be run scheduled on your PC.

The very least option might be a command line switch in Access that runs a particular macro on startup (the startup code of which 'must' be capable of handling your set based logic - have a look at that feature in help in Access)

Any of the above featured mechanisms are designed to focus on an unattended environment ie: "query my database while I am asleep at home and if records exceed 1000 then grab first 500 write them out to a text file and send me a copy via email without invoking Outlook that type of thing") All very advanced stuff if you are prepared dig it all up on the web and learn it you have a few pointers :-)

[kdonovan]

If I am reading you correctly it seems to me you wish to initiate Access outside the bounds of personally being in the program itself. An automatic DIY type of thing. This can be done but as Neopa has rightly said already, it does require a significant and sound knowledge of scripting using a combination of disciplines VBScript files (files that have an.vbs extension) and/or batch files .BAT extension, both of which can be run scheduled on your PC.

The very least option might be a command line switch in Access that runs a particular macro on startup (the startup code of which 'must' be capable of handling your set based logic - have a look at that feature in help in Access)

Any of the above featured mechanisms are designed to focus on an unattended environment ie: "query my database while I am asleep at home and if records exceed 1000 then grab first 500 write them out to a text file and send me a copy via email without invoking Outlook that type of thing") All very advanced stuff if you are prepared dig it all up on the web and learn it you have a few pointers :-)

Wow. Ok, screw the emailing thing. I just want the macro to run in the background at a certain time (I like the startup idea) so the pop-ups aren't there telling me how many records are being copied then deleted.

I'll search some more online and see what I can find. Ok, so I found how to do an autoexec macro but how can I get rid of the popups asking if I want the "macro/query" run??

Thanks.

[kdonovan]

Wow. Ok, screw the emailing thing. I just want the macro to run in the background at a certain time (I like the startup idea) so the pop-ups aren't there telling me how many records are being copied then deleted.

I'll search some more online and see what I can find. Ok, so I found how to do an autoexec macro but how can I get rid of the popups asking if I want the "macro/query" run??

Thanks.

Ok, I figured it out with just a macro.

Thanks.

[Jim Doherty]

Ok, I figured it out with just a macro.

Thanks.

Wow you dont mess about, nice one :)

[kdonovan]

Wow you dont mess about, nice one :)

Thanks. I even figured out how to refresh the page. Now I just need to play some more to have the new stuff sent to me, and I think I can figure that out too, with just a macro.

Access is gettin' kinda fun.

[NeoPa]

K,

You're making excellent progress in my absence clearly, but may I suggest you hold up with all the macro work. It's an ever-diminishing spiral that will leave you having wasted much time & effort. VBA is a better, more flexible, and much more powerful approach, and is more compatible with other developers that you may have to interact with.

See Invoking a Database From the Command Line for more on doing this in such a way. It also covers scheduling the command and even includes an example database to download and play with.

PS. I may have worried earlier that being so green the VBA approach might be too much of a leap, but having seen your reactions etc, I'm confident it is the right approach for you.

[NeoPa]

kdonovan: Wow. Ok, screw the emailing thing (Sooo ladylike :D). I just want the macro to run in the background at a certain time (I like the startup idea) so the pop-ups aren't there telling me how many records are being copied then deleted.

As it happens, the way to hide the informational messages (X records updated/deleted/etc) is to use :

Call DoCmd.SetWarnings(True/False)

[NeoPa]

By the way, I did a search for mail at the bottom of the forum this thread is posted in, and one of the threads I found was Problem with e-mailing from within Access, which I felt likely to be of use to you.

I'm sure I remember one that talked about the security settings and how to get around them (at least cause them not to pop up), but didn't see it in good time. The facility is there to use though if you think it could be of help.