Retrieve the list of tables from a query

[Guest]

I know how to get a list of fields from a table or query. However, I would
like to know how I can retrieve the list of tables used in a query or
recordset.

Thanks for any assistance.

Mr B

 

[Larry Linson]

You can use code to parse the Query's SQL and save the names of the Tables.
It's likely to take more work than I'd want to put in to get that
information. It's not going to change from one use of the Query to the next
use. Do you have a need to gather the information from just any query, at
runtime, or could you get the information in design view.

What do you want to accomplish by knowing the tables? If you clarify, with
detail, perhaps someone can offer a suggestion for a different way to
accomplish the same result.

I don't know if you can get that information out of a Recordset that you've
opened.

Larry Linson
Microsoft Access MVP

 

[Guest]

Larry,

Thanks for the reply.

I had not thought of parsing the SQL statement. That is a possibility.

I was hoping to be able to extract the table names from from a query or the
recordset in order to display the list of tables used in the query, then when
the user selected a specific table, I would then display the list of fields
from each of the tables used.

I would be needing this info from user defined as well as pre-defined
queries and/or queries and sql statements.

I may have to rethink my interface. I just thought that I might be able to
use this approach.

Thanks for your input.

Mr B

 

[Larry Linson]

Don't forget that a Query can have other Queries as its data sources, not
necessarily Tables, and those could be based on other Queries, too. Parsing
the SQL to get the tables may not be as simple as I made it sound.

Uh, you indicated what you want to get, and how you want to get it, but not
what you are trying to accomplish with the information, nor whether there's
a compelling need to do it at runtime, or whether it could be done in design
view.

It almost reads as if you want to create a "where used, by whom" kind of
Report.

My applications do not allow the user to create Queries inside; they may,
sometimes, have the ability to create a separate MDB, link the common
backend tables, and do their own querying.

Best regards,

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

Interesting application. If I get any "inspirations" that might help, I'll
post.

But, my guess is that you want to give the users "business language" to deal
with, in any case, not Table Names, etc., and that would not seem to be a
big deal in an application of this complexity. For example, "By what name do
you want to refer to the paragraph you just created?"

Larry Linson
Microsoft Access MVP

 

[Guest]

Keep me in mind. I'll be working on this for quite some time. I do not have
all of the details worked out just yet, but I'm still working on it.

The more I look at it the more I lean toward just presenting the users with
a specific list of fields. (These would be defined in a table) I can then use
this pre-defined information to do what ever with.

Thank you very much for your insight and thoughts.

Mr B