Modifying Reports which use Temporary Tables

I often get told that it is impossible to customize a report which uses a temporary table, and in particular a temporary table which uses single fields for multiple purposes. While I admit that these reports are more challenging to customize they are far from impossible.

Usually, the temporary tables use some sort of sequence number field to help define what each of the generic fields are being used for.  Start by adding all the fields from the temporary table to the report so you can see how they used.

Creating relationships from a temporary table to another table is sometimes possible, if the required key fields are available as non-generic fields.  If this is not the case you can use VBA to access the SQL data via ActiveX Data Objects (ADO) and a Transact-SQL query based on the data available to you from report fields and/or fields from tables already linked to the report.

Please see the posting VBA - Accessing any SQL data from a Report Example for an example.

In summary, VBA can be used to make the impossible.... well.... possible.