[AccessD] VBA7?

[Heenan, Lambert]

Does this only work in Access 2016 or something? Just ran it in Access 2010 and the qdf.Fields collection always shows up with a count of zero.

Just curious.

Lambert

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Salakhetdinov Shamil
Sent: Wednesday, February 10, 2016 2:56 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] VBA7?

 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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com