Using a scheduler to run action queries that are in module 4

[BvCF]

Have module that contains action query (append query that appends records from oracle 8.1.7 to a local Access table).

Can one use a scheduler to automatically enable the action queries within a module to run every morning at 9:00 p.m.?

What I have read thus far is that ADO is the preferred method to append Oracle data into the local Access table.
Therefore, I put the action queries in a module.

Also, read that one should use a macro to automate queries in a scheduler.

Any insight is greatly appreciated.

 

[mp9]

1. Create a macro that runs your code/function (take a look at the RunCode action).

2. Use soemthing like the Handy Access Launcher (

http://www22.brinkster.com/accessory/resources/001.shtml)

to schedule the macro to run at certain times, and hence your code/function.

 

[PaulBricker]

A macro can only run a Function call. It can not run a Sub procedure so your action queries will need to reside in a Function. After that, you can use mp9's suggestion, or just use Microsoft's scheduler. I have a number of routines that I run during the night. This is what I use in MS scheduler
In the Run box
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "\\mainserver\physpdb\PysPlant2002.mdb"/xEmailMacro
The first half is the path to Access. The second half is the path to the .mdb file, and the part after the / is a switch you use to call the macro so it would be /xMacroName

Then in the Start In box, you just put the path to MS Access. In my case it's
"C:\Program Files\Microsoft Office\OFFICE11"

HTH

Paul

 

[BvCF]

PaulBricker,

Thanks for the insight.

Have not created a function before.

Any generic example of a function that you have used to append data from Oracle or another external database to a local access table? Also, I will try to use a function to update data within the local table.

Thanks in advance.

 

[PaulBricker]

I have never worked with Oracle so I'm not going to be much good there but you might look at the TransferDatabase Method.
I assumed you already had a module that ran your action queries but you couldn't run it from a scheduler.
In this case, where you are not passing a value to the procedure from some other location, the Function and the Subprocedure both work the same. If you have a sub, like this

Sub CallQueries()
'run action queries inside this module
End Sub

all you would do is change the name to

Function CallQueries()
'run action queries inside this module
End Function

then the macro will be able to run the function and you call the macro from the scheduler using the switch. If you need more help in importing data from Oracle, you should probably start a new post on that subject.

HTH

Paul

 

[BvCF]

Okay,

Still not able to resolve. The function name is not displayed in the Macro field "function name." Therefore, I cannot select the function name. What am I missing here?

(Obviously, I need to learn more about functions!)

Displayed below is what I have;


Public Function UpdateAccessFromTempTable1() 'Originally, this was a sub procedure; converted to

a function so it can be run from a macro
CurrentDb.Execute "DELETE * FROM tblUpdateTemp"
Debug.Print tblUpdateTemp
CurrentDb.Execute "INSERT INTO tblUpdateTemp (AcctCurrent, AcctNo, TotChgCurrent, ExpPymtCurrent)

Select AcctCurrent , AcctNo, TotChgCurrent, ExpPymtCurrent FROM qrySQLPassthruUnion;"
CurrentDb.Execute "UPDATE tblVar INNER JOIN tblUpdateTemp ON tblVar.AcctNo = tblUpdateTemp.AcctNo

SET tblVar.AcctCurrent = tblUpdateTemp.AcctCurrent, tblVar.TotChgCurrent =

tblUpdateTemp.TotChgCurrent, tblVar.ExpPymtCurrent = tblUpdateTemp.ExpPymtCurrent,

tblVar.DateTableUpdated = Now() "
End Function

 

[PHV]

Is the function in a standard code module ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BvCF]

Yes,

The function is in a standard code module.

All I did was change the name from "Sub UpdateAccessFromTempTable1"
to "Function UpdateAccessFromTempTable1()."

Can one do this or is there another way?

Any additional insight is greatly appreciated.

 

[PHV]

Does the DB compiles properly ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PaulBricker]

Can't you just type the name of the function on the Function line -

UpdateAccessFromTempTable1()

That should be all you need to call the function from the macro. Put the name in, and run the Macro and see if it opens your function. If you don't want to run that specific function, make another one that just pops up a message box.

Function callmessage()
MsgBox "The function works"
End Function

and in a macro put the name callmessage() on the function line and run it. See if it works.

Paul

 

[BvCF]

PaulBricker,

Tried your suggestion and receive the error that I misspelled the function name.

Any other clues as to a resolution?

 

[PHV]

Again, Does the DB compiles properly ?

 

[TheAceMan1]

How are ya BvCF . . .

[blue]The function name is not displayed in the Macro field "function name." Therefore, I cannot select the function name. What am I missing here?[/blue]

Just enter [blue]UpdateAccessFromTempTable1()[/blue] on the function line . . .

See Ya! . . . . . .

 

[PaulBricker]

Make sure the name of your function and the name of the module it resides in are different. Also, to compile the Database, as PHV suggest, open your module and on the menu bar go to Debug...compile databasename.
Try it and see if you get any errors.


Paul