Scheduling Microsoft Access Queries

Hi,

I've just purchased the software and have made a good bit of progress understanding it but I'm stuck when it comes to opening ACCESS queries. I don't know JAL so I'm trying to do it by sending keystrokes. The only way I can figure out how to do it is to use keystrokes to be sure I'm on the "Queries" object, then do control-home to get to the top of the list of queries and use the {DOWN} command to move down to the right query. That works but every time I create a new query I'll have to make sure it goes to the bottom of the list. That's manageable but there has to be a better way.

Thanks,
Shawn

Sending keystrokes should be used only as a very last resort. The method is not reliable by definition. What if the target window is not in focus and the keystroke reaches some other application? What if your anti-virus or windows update pops up a message in a middle of keystroke processing? etc...

As far as I understand, all you want to do is to run a query. Right?

Here is how you can do it without JAL.
1. Create an ODBC connection pointing to your Access file (Control Panel/Admin Tools/ODBC Manager). Make sure the connection is defined on system DSN tab not file DSN.
2. In 24x7 create a connection profile for the ODBC connection. (Tools/Database Profiles menu) Select ODBC interface, click New button, complete the profile and test the connection
3. Create a new job; choose Database as the job type; select the created profile in the drop-down list, paste the query text. Schedule as required. That's all and it is much more reliable by definition than the keystrokes method.

4. Repeat step 3 for other queries creating as many jobs as required.

WOW! I really was taking the long path. If you can answer a couple of more questions, I think I can handle this. First, one of the queries I need to run prompts the user for a start and end date. Those should be the dates for the previous week's Sunday and Friday. Can I populate those automatically? And second, I normally would export the output of one of the queries to Excel so I can email that file to a distribution list. I don't know how to export the output nor have I figured out how to send email through Lotus Notes. Can you help?

Thanks,
Shawn

Yes, it is sure possible. You can replace these parameters with a simple formula using DateAdd function and @D macro-variable available in 24x7. For example, the formula for last week's Monday would be
DateAdd("d", -7, '@D2"yyyy-mm-dd"')

The @D2 returns current weeks Monday then DateAdd subtracts 7 days to get last week's Monday.

Here is a more functional example. Perhaps you want to copy records from table SourceTable into TargetDatble for a date range from Friday of last week to Sunday of this week.

	Code:
	INSERT INTO TargetTable SELECT * FROM SourceTable WHERE my_date_column BETWEEN DateAdd("d", -7, '@D6"yyyy-mm-dd"') AND '@D1"yyyy-mm-dd"'

Theoretically you can perform all necessary date calculations in the query using only standard date functions, it is just much easier and more efficient to use macro-variables available in 24x7. For details please see the macro-parameters topic in 24x7 help system.

This kind of thing is going to make life so much easier once I get it to work. I pasted your where statement into my query and receive an error saying "Too few parameters. Expected 1." My where statement reads:

WHERE YearIssu.DATE BETWEEN DateAdd("d", -7, '@D6"yyyy-mm-dd"') AND '@D1"yyyy-mm-dd"'

Can you help me?

Thanks,
Shawn

Do you paste into the query in 24x7 job properties or into the query in the Access query window?

If in 24x7, please try using double-quotes instead of single quotes.

I pasted into 24x7. I replaced the single quotes with double quotes and received the message "Too few parameters. Expected 3." My where statement now is:

WHERE YearIssu.Date BETWEEN DateAdd("d", -7, "@D6"yyyy-mm-dd"") AND "@D1"yyyy-mm-dd""

Looks like it doesn't like DateAdd function in the query. I ran a quick search on the Internet and found out that this is a known issue with old Microsoft Jet driver (aka Access ODBC driver v3.5 and earlier) described in several Microsoft Knowledge base articles, for example, in 239104 http://support.microsoft.com/kb/239104 This article describes some issues with the Sandbox mode, but the real problem is a bug in Microsoft Jet 3.5 driver, which requires SP3 update. The article also describes how to turn off the sandbox mode.

By the way, in this particular case, a workaround is not to use the DateAdd function and change the query as below

	Code:
	WHERE YearIssu.Date BETWEEN #@D6"yyyy-mm-dd"# - 7 AND #@D1"yyyy-mm-dd"#

Perfect!

- Thanks