0

The RecordSource for an Access Form or Report can be a table or a query. A table can be local or linked (and might contain calculated fields), and a query can refer to other queries and other tables. Furthermore, a query can use "*" selections, User-Defined Functions (UDFs) and even remotely connected sources. Crosstab queries can have field names that are determined by the values within fields, and as such, the crosstab field names can vary as the data changes.

I'm trying to find the most efficient "light-touch" way of determining the field names of a given RecordSource, with the following considerations:

  • Queries that include User-Defined Functions should not call the functions while determining the field names, so a recordset approach is not suitable.

  • Locks should not be put on the underlying objects

  • The database would ideally not be in Exclusive mode

  • Connections to linked tables should ideally not be opened, and nor should any triggers on remote tables be triggered. If possible fields should be determined without any access to remote data sources.

  • The Field Name is required, the field type and attributes would be nice to have. The Field Description would be a bonus.

  • I fear that Crosstab queries will potentially open the underlying tables and/or run UDFs (as they have in my testing), so I am prepared to skip enumerating the fields of a Crosstab query, although I won't necessarily know if a query has a source that is a crosstab query...

I'm unsure if using DAOs TableDef and QueryDef will be adequate, or whether I should explore ADO Schemas and Catalogs, or perhaps some other approach?

This is a very basic DAO approach, but I'm not convinced it is necessarily the fastest, or if it is light-touch.

Sub EnumQueryDefFieldNames()

  Dim qdf As QueryDef
  Set qdf = CurrentDb.QueryDefs("Query7_Crosstab")
  For Each fld In qdf.Fields
    Debug.Print fld.Name
  Next fld

End Sub
Xstian
  • 8,184
  • 10
  • 42
  • 72
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60

1 Answers1

1

TableDef and QueryDef are the usual objects for this. Simple and fast.

Of course, a crosstab will need to retrieve data to determine the field names, except (perhaps, haven't tested) if these are hardcoded.

Gustav
  • 53,498
  • 7
  • 29
  • 55