Automated query

[YNWA]

Hi, does anyone know how to automate a query to run at a set time?

What I need is a query to run at say 4am in the morning every day.

It will then update a backend table with that previous days date or something similar to that.

Is this possible? Also daft question but would the Access DB need to be open every day at this time in order for the query to run? And if I got this set up on a server, would that make it alot easier to run the query without having access open over night?

Thanks
Will

 

[DCrake]

You should be able to set up a scheduled task to do that. Then on the open of the app instruct it to run a macro that fires of the query and then quits the app.

 

[boblarson]

You should be able to set up a scheduled task to do that. Then on the open of the app instruct it to run a macro that fires of the query and then quits the app.

Just a little additional info about this. If on your machine it would require you to be logged into the network (you can lock your machine but it has to remain logged into) or, you could put it on a server but Access would need to be installed on the server (if IT would even allow that) and of course you would need a license for that.

 

[YNWA]

The database would be on our network, it would be linked to tables in another system.

The idea is behind it is to stop me running about 10 queries when I get into work.

The queries I run are on one system, I then export the queries to Excel on my C drive and copy over to a network drive. I then use SQL Server 2008 to import and merge these exports into a data warehouse.

Hopefully by setting up an automated job, I can have those queries run automatically over night and we can set the SQL server to beging the import and merge some time after the automated job has finished.

What are the implications of having Access on the server? Is it dangerous?

We have a Access DB set up already that is ODBC connected to the server (I think).

 

[YNWA]

I have a marco design open now.

I have set first command to the following:

1) RunMacro
2) OpenQuery

This works and opens the query. But how would I get this to run the query and export an .xls sheet (I am using 2010 Access) to a folder on my C drive?

I need say qryConsult to be ran, exported to excel to folder C:\Exports\FebExports

Then it needs to move onto the next query (eg. qryCase) and do the same for about 10 queries.