Thursday, January 29, 2009

New Sample: DaysBetweenQuery.mdb

Author: Roger Carlson

This sample illustrates how to create a "Days Between" query. It shows how to:

1) Display the difference between dates in subsquent records of a table
2) Display the difference between dates in subsquent records of a table over a group. In this case, the group is the OrderID field.

There are two methods shown here: Using a 1) correlated subquery and 2) DMax domain aggregate function. The correlated subquery is the faster method, but it returns a non-updateable recordset. The DMax method is slower, but the recordset is updateable.

HOWEVER, it should be noted that against a large dataset, neither method will be very fast!

You can find the sample here:

Link to top: DaysBetweenQuery.mdb


Friday, January 23, 2009

New Sample: ExcelHyperLinksToAccess

Author: A.D. Tejpal


Sample Db - Explanatory Notes

This sample db demonstrates import of excel data including hyperlinks if any. Direct import of specified data block from excel, using DoCmd.TransferSpreadsheet command is not able to pull the hyperlink details. To do so, it is necessary to convert the face value of hyperlink cells into full information suitable for proper interpretation in access table's hyperlink type field.

Source excel file is placed in folder ExcelFile. This folder is located in the home folder (ExcelHyperLinksToAccess) that contains the sample db. It should be ensured that in the excel workbook, the sheet having source data is the active sheet (Open the workbook, select the sheet having source data, save and close). First row of data block should carry field names matching those in access table named T_Imported.

On opening the main form, excel source sheet is displayed in an unbound OLE control. Through auto detection, available data block is shown enclosed in a solid border. If there is any column within this data block that carries hyperlinks, it gets highlighted in special color. Row and column numbers of first cell (i.e. top left cell) of excel data block, as well as column number of hyperlink column, get displayed in text boxes. If found necessary, the user can edit these values.

After verifying that excel data block is displayed correctly, clicking of Import button carries out the import into table T_Imported, after converting the face value of cells in hyperlink column so as to suit the requirements of hyperlink type field in access table.

If data is required to be imported from more than one worksheet in a given workbook, the process can be repeated by making different sheets active in turn (Open the workbook in folder ExcelFile, select the desired sheet, save and close).


It is observed that DoCmd.TransferSpreadsheet action, if carried out as an integral part of automation code, can lead to sticking of excel application (excel refuses to quit despite explicit quit command and setting the object variables to nothing). For this reason, implementation of DoCmd.TransferSpreadsheet has been carried out independently, after execution of automation code modules.

You can find the sample here:

Link to this post: ExcelHyperLinksToAccess


Wednesday, January 14, 2009

New Sample: Form_AnimatedWavesAndShapes


Author: AD Tejpal

This sample db demonstrates programmatic animation of waveforms and nested shapes.

Five styles of waveforms are covered (The user can select the speed of animation e.g. Low / Medium / High):
  1. Spiral - Simple
  2. Spiral - Oscillating X axis
  3. Sine wave - Three components in series
  4. Sine wave - Three components in phase
  5. Sine wave - Three phase (typical of electric power supply)
Nested shapes cover various combinations of controls radiating outwards within a given box. Animation speed continues to cycle through normal / fast / faster.

The size of display can be altered by clicking anywhere within the outermost rectangle. Bottom right corner of display area shifts to the clicked location while the top left corner remains constant.

You can find this sample here:

New Sample: Form_AnimatedWavesAndShapes