QueryDefs does not include MakeTable queries

  • Thread starter Thread starter Tom Slickers
  • Start date Start date
T

Tom Slickers

I have been asked to analyze approximately 200 Access databases. Each
one pulls data from an Oracle data warehouse using a combination of
Select, Append, MakeTable, and Update queries. I am trying to write a
VB program that will tell me which warehouse tables and fields are
actually being used.

The following code works for Select queries, but it seems that the
maketable, append, and update queries are not part of the querydefs
collection.

I am running this using DAO 3.6 (from Access 2000).

Any ideas?

Public Function CheckQueries()

Dim objDB As DAO.Database
Dim OBjquery As DAO.QueryDef
Dim objField As DAO.Field


Const Quote = "'"
Const Comma = ","

'On Error Resume Next

Set objDB = OpenDatabase("database name")

With objDB

For Each OBjquery In .QueryDefs

For Each objField In OBjquery.Fields

debug.print objdb.name,objquery.name,objfield.name,
objfield.sourcefield,objfield.sourcetable

Next objField

Next OBjquery

.Close

End With
 
Tom Slickers said:
I have been asked to analyze approximately 200 Access databases. Each
one pulls data from an Oracle data warehouse using a combination of
Select, Append, MakeTable, and Update queries. I am trying to write a
VB program that will tell me which warehouse tables and fields are
actually being used.

The following code works for Select queries, but it seems that the
maketable, append, and update queries are not part of the querydefs
collection.

I am running this using DAO 3.6 (from Access 2000).

Any ideas?

Public Function CheckQueries()

Dim objDB As DAO.Database
Dim OBjquery As DAO.QueryDef
Dim objField As DAO.Field


Const Quote = "'"
Const Comma = ","

'On Error Resume Next

Set objDB = OpenDatabase("database name")

With objDB

For Each OBjquery In .QueryDefs

For Each objField In OBjquery.Fields

debug.print objdb.name,objquery.name,objfield.name,
objfield.sourcefield,objfield.sourcetable

Next objField

Next OBjquery

.Close

End With
Click to expand...

Sure these types of queries are in the QueryDefs collection! However,
action queries don't have a Fields collection. I think you'll have to
analyze their SQL yourself to find out what tables and fields are
involved.
 
Back
Top