Access 101: How Do I Run A Process Automatically Once A Day? Part I

There are two ways to do this: 1) from within Access, and 2) externally with Windows Scheduler. I talk about method 2 here: How Do I Run A Process Automatically Once A Day? Part 2 .

Suppose you have a macro (mcrImportFiles) that imports data and you want to schedule it for the middle of the night each night -- say 1 am. How would you do it?

First of all, to do this, you need to leave your Access database open all the time (or at least start it every night) because it must be open to work.

Next, you need to create a form that will stay open all the time. Have this form open automatically at Start Up (see How Do I Configure My Database Start Up? ). It can be hidden if you want. (see How Do I Hide a Form But Leave It Running? ).

Every form has at Timer Property and an OnTimer event. Set the Timer property to 60000 (60 seconds). This will cue the form to run the OnTimer event once each minute.

Now, add some code to the OnTimer event. Something like this:

Private Sub Form_Timer()
If Time() >= #1:00:00 AM# And Time() < #1:01:00 AM# Then DoCmd.RunMacro mcrImportFiles End If End Sub This will run the macro each day at 1 am. To make this process even more robust, convert the macro to VBA code (see How Do I Convert A Macro to VBA Code? ) and insert it directly into the OnTimer event. This way, you can trap for any errors and handle them gracefully instead of simply letting your application hang as it would with a macro.

New Sample: FormParametersForRecordsets

FormParametersForRecordsets

Author: A. D. Tejpal

SQL strings having embedded form based parameters do not readily lend themselves to creation of recordsets (Error 3061 - Too Few Parameters). Conventional approach involves concatenation of hard values represented by form controls into the SQL string.

Concatenation of hard values into SQL string suffers from the following drawbacks:

• (a) Proper delimiters need to be used for non-numeric data (Single quote for text type data and hash (#) for date type data).
• (b) If text data has embedded quotes, these need to be fixed before concatenation.
• (c) For date type data, the hard value has to be converted into "mm/dd/yyyy" format before undertaking the concatenation. This is meant to prevent inconsistent results, if the regional settings for short date on the local computer happens to be not as per U.S. date format.
• (d) Care needed in handling Null values.

The limitations brought out above, point to the desirability of devising a workable solution for utilizing SQL strings having embedded form parameters, as source for recordsets. Function Fn_FixFormParamsForRecordsets() is meant to fix such SQL strings, making them suitable for use with recordsets. This eliminates the problems outlined in preceding para.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=437

New Sample: ModulesAddIn_BulkImportFromFiles

ModulesAddIn_BulkImportFromFiles

Author: A. D. Tejpal

The ModulesAddIn_BulkImportFromFiles for MS Access is a utility that enables bulk import of .bas or .txt files as general modules.

You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=434

New Sample: Query_RowNumbersAndSumLargeData

Query_RowNumbersAndSumLargeData
Author: A.D. Tejpal

Subqueries (or user defined functions based upon recordsets) for calculation and display of row numbers and running sum, are found to be slow in case of large data sets.

Use of increments to global variables offers a faster alternative. This method is preferably used through an action query, as calculated values directly displayed via select query tend to be volatile (the results keep on changing as one navigates up and down the records.

Two alternative methods based upon incrementing global variables are demonstrated as follows. In each case, two styles are covered, namely (a) Straight simple sequence and (b) Group-wise sequence. If it is simple sequence, the user defined functions have provision for specifying the starting number or sum - if desired:
1 - Row number and running sum - via update action.
2 - Row number and running sum - via make table action.

For academic interest, depiction of row numbers through generation of autonumbers has also been demonstrated as follows (this approach is however not as fast as that involving increments to global variables):
3 - Row number - via append action..
4 - Row number - via make table action.

Note:
(a) Use of incrementing global variables (items 1 and 2 above), apart from being the fastest approach, has the added benefit that it is not dependent upon availability of primary key.
(b) Four user defined functions utilized in implementing this method are given in the general module.
(c) Use of these functions in WHERE clause of pertinent queries is meant to initiate the sequence in desired manner.
(d) If filter has been applied to displayed data, generation of row number and running sum gets implemented on the filtered data, as demonstrated in the sample db.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=433&PID=429#429

Featured Sample: Crosstab Query in Flex Grid

Crosstab Query in Flex Grid

Author: Peter Hibbs

This demo program shows how to display a Crosstab query in a Flex Grid control on a form. The problem with displaying Crosstab queries on a form (say in a ListBox control) is that some queries have a variable number of columns as well as rows. Using a Flex Grid control allows you to set the number of columns at run time as well as giving all the other facilities that you get with Flex Grid controls such as colouring cells, fixing columns or rows, etc, etc.

See Peter's other demo for more information on Flex Grid commands.

This demo works with Access 2000 onwards.

Find this sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=429