Author |
Message |
shawn29316
Joined: 27 Dec 2007 Posts: 27 Country: United States |
|
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
|
|
Thu Dec 27, 2007 1:24 pm |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7857
|
|
|
|
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.
|
|
Thu Dec 27, 2007 2:25 pm |
|
|
shawn29316
Joined: 27 Dec 2007 Posts: 27 Country: United States |
|
|
|
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
|
|
Fri Dec 28, 2007 8:38 am |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7857
|
|
|
|
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.
|
|
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.
|
|
Fri Dec 28, 2007 9:17 am |
|
|
shawn29316
Joined: 27 Dec 2007 Posts: 27 Country: United States |
|
|
|
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
|
|
Fri Dec 28, 2007 10:00 am |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7857
|
|
|
|
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.
|
|
Fri Dec 28, 2007 10:36 am |
|
|
shawn29316
Joined: 27 Dec 2007 Posts: 27 Country: United States |
|
|
|
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""
|
|
Fri Dec 28, 2007 11:05 am |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7857
|
|
|
|
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.
|
|
Fri Dec 28, 2007 11:56 am |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7857
|
|
|
|
By the way, in this particular case, a workaround is not to use the DateAdd function and change the query as below
|
|
WHERE YearIssu.Date BETWEEN #@D6"yyyy-mm-dd"# - 7 AND #@D1"yyyy-mm-dd"# |
|
|
Fri Dec 28, 2007 12:24 pm |
|
|
shawn29316
Joined: 27 Dec 2007 Posts: 27 Country: United States |
|
|
|
Perfect!
- Thanks
|
|
Fri Dec 28, 2007 12:46 pm |
|
|
|