Use of MS Access form to schedule weekly make-table queries 3

[MwTV]

Currently exploring the use of an Access form (as opposed to a auto-exec macro) to schedule approximately 10 make-table queries in sequential order on a weekly basis.

The make-table queries are based on pass-through queries that are used to extract data from a SQL-Server database.

For example, something along the lines of the following;

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
Call DoCmd.SetWarnings(False)
Call CurrentDb.QueryDefs!MyQuery1.Execute
Call CurrentDb.QueryDefs!MyQuery2.Execute
Call CurrentDb.QueryDefs!MyQuery3.Execute
Call CurrentDb.QueryDefs!MyQuery4.Execute
Call CurrentDb.QueryDefs!MyQuery5.Execute
.
.
.
Call DoCmd.SetWarnings(True)
End Sub

Questions;

Is it possible to use this approach to schedule the make-table queries that are based on the passthrough queries?
(I have heard that scheduling uses the SYSTEM account that does not allow network access.)

What are the advantages/disadvantages to using a form to schedule the action queries relative to using an auto-exec macro?

Any additional insight is greatly appreciated.

Thanks in advance.

 

[Crowley16]

not sure if there's anything in 2007 but in 2003 and under, access doesn't have any native scheduling functionality, the only way would be to use your windows scheduler to run the database, and then put the startup form as your create db form...

--------------------
Procrastinate Now!

 

[MichaelRed]

hmmmmmmmmmmmmmm ... mmmmmmmmmmmmmm,

Making ten tables per week would create 520 tables per year. Seems like a this could easily become an issue in a relatively short period of time.

Mayhap there is some issue with the db schema design?




MichaelRed

 

[MwTV]

How do I specify the startup form as my create db form?

Also, can one have the Windows Scheduler "kick off" make table queries that are based on passthrough queries?

Thanks in advance.

 

[PHV]

Have a look at the /x command line switch of msaccess.exe to launch a specific macro at scheduled time.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mp9]

Or, if you don't want to wrangle with command lines, batch files or Windows Scheduler (though this might be an interesting learning experience for you), take a look at the Handy Access Launcher...

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