Open database and run query on a certain day and time

[austenr]

Is there a way to have a series of queries execute one after the other instead of having to manually execute them? Preferably, I would like to schedule this to run around 1:00 AM in the morning. Thanks

[Ken Puls]

You could try creating a vbScript to create an Access instance, run them, then close. Schedule it with the windows task scheduler, and you should be good to go.

[austenr]

Could you provide an example? Thanks Ken

[Ken Puls]

Okay, I have not tested this at all, but here's something to get you started.

Open a new text file and paste the following code in it, making adjustments where necessary. Keep in mind that for vbs, it is important that you do NOT give the objects their types. (Dim as...)


RunQueries

Sub Execute_Stored_Question()

Dim cnt
Dim stCon
Dim stSQL

'Set path to DB here
stcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyDatabase.mdb"

'The name of the stored question (Query) is placed between the brackets.
stSQL = "[DelData]"

'Make ADO connection
Set cnt = CreateObject("ADODB.Connection")

With cnt
'Open the connection.
.Open stCon

'Execute the stored question (query)
.Execute (stSQL)

'Close the connection.
.Close

End With

'Release object from memory.
Set cnt = Nothing

End Sub
Save the file with a .vbs extension instead of .txt. Test it out by double clicking it.

When you're satisified that it works, go to All Programs|Accesories|System Tools|Scheduled Tasks and follow the prompts to schedule it.

PS, the guts of this were adapted from XL-Dennis's site (http://www.excelkb.com/article.aspx?id=10178&cNode=1I7C3V)

Hope this helps,

[jmwtac]

us the form timer function to check the time = 1.00am and then start your code to run the macros