[AccessD] VBA7?

[Salakhetdinov Shamil]

 Hi Dan --

Here is how you can list querydefs' source tables and fields:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdfIndex As Integer
Set dbs = Access.Application.CurrentDb
qdfIndex = 1
For Each qdf In dbs.QueryDefs
Debug.Print qdfIndex & ". Query: " & qdf.Name
Dim fld As DAO.Field
For Each fld In qdf.Fields
Debug.Print " " & (fld.OrdinalPosition + 1) & _
". " & fld.SourceTable & "." & fld.SourceField
Next fld
Debug.Print ""
Next qdf HTH.

-- Shamil


>Tuesday, February  9, 2016 4:33 PM -08:00 from David McAfee <davidmcafee at gmail.com>:
>
>So we're trying to consolidate the over 3000 dbs and spread sheets we have
>throughout the company. I made up a little split db that allows users to
>enter a project/task name, select usage occurrence from a drop down then
>browse to the access file and/or excel workbook.
>
>The  FE then takes said file and lists all of its tables, queries, macros,
>modules, forms, View, stored procedures (for ADPS)  and worksheets (for
>Excel files). All of these objects get stored in a table, and are displayed
>on the main form in a list box. They can double click an object in the list
>box to "move" it into the "selected" list box to mark it as an object that
>is used.
>
>Is there a programmatic way that you can loop through queries to get a list
>of the tables (or sub-queries) that are used, so I can also add them to the
>list? Kind of like how the built in dependencies can be checked by right
>clicking on an object.
>
>I'm trying to figure out what is really being used, what isn't, what is
>stale...
>
>I've found tables that are linked to older DBs that aren't being updated as
>well as local (stale) tables that are also not being updated.
>
>I'm trying to figure out what it used most, in order to prioritize which
>reference table to work on first. I want everyone pulling from the same
>place.
>
>Thanks,
>David
>-- 
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website:  http://www.databaseadvisors.com