Access Event Reminder

armsys · Post by **armsys** » Thu Jun 11, 2009 11:03 pm

How to write an MS script to scan an Access database for coming due events?
Can MS script select Access queries instead of tables?
If the events meet the reminder criteria, MS lists them on screen.
Thank you.
Armstrong

Bob Hansen · Post by **Bob Hansen** » Fri Jun 12, 2009 1:53 am

You could use Macro Scheduler command DBQuery to write a SQL expression to retrieve all records with an Event Date due in the next 3 days.

From the HELP file example:

Code: Select all

//Connect to Datasource
Let>str=Driver={MySQL ODBC 3.51 Driver};Server=someserver.com;Port=3306;Database=example;User=admin;Password=xxxx;Option=3;
DBConnect>str,dbH
 
//Perform SELECT query
Let>SQL=select * from customers where custID='abc123'
DBQuery>dbh,SQL,CUSTOMERS,numrecs,numfields
 
//loop through returned recordset
Let>r=0
Repeat>r
Let>r=r+1
Let>f=0
Repeat>f
  Let>f=f+1
  Let>this_field=CUSTOMERS_%r%_%f%
   Message>this_field

  Wait>0.5
Until>f=numfields
Until>r=numrecs
 
//Close database connection
DBClose>dbH

You will need to set up an ODBC connection to the Access database first. And you will need to make a SQL statement something like: Select All from tblEvents where EventDate<=#06/13/2009#

If you want to access a query vs a table, just copy the SQL code of the Access query into the Macro Scheduler SQL statement that is used in the DBQuery command.

armsys · Post by **armsys** » Fri Jun 12, 2009 2:20 am

Bob,
Thank you.
I'm humbled by your enormous knowledge in several development tools.
Even though I don't quite understand your script, at least it's my starting point.
BTW, do you mean Access and/or MS support MySQL?
Thanks again.
Armstrong

Bob Hansen · Post by **Bob Hansen** » Fri Jun 12, 2009 3:37 pm

First, not my script, but directly from the Help file on DBQuery.

The answer I provided is for most databases that allow an ODBC connection. The example looks like the ODBC connection is to an MySQL database. If you are connecting to Access you will use a different ODBC driver.

My normal system is using "MS Access Database" ODBC driver, and connects to Access 2003. I don't have access to work on that system now, so I cannot give you a tested script.

Here is a screenshot of my ODBC connections for multiple databases, Excel, etc.:

But if you follow the sample script, use the values from your own ODBC connection and from your databases and tables.

armsys · Post by **armsys** » Fri Jun 12, 2009 10:06 pm

Bob,
Thanks for your help.
Armstrong