Results 1 to 8 of 8
  1. #1
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408

    get table names from query

    Hi, is there a way to get the names of the tables used in a query?


    i have a combobox in which i select a query, can i feed another combobox with the tables used in the query in the first combobox?
    thanks

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

  3. #3
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    this is witchcraft

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I've recently updated a lengthy article about how Access stores queries: How Access Stores Queries - 1 (isladogs.co.uk)

    The information you need is indeed stored in a system table called MSysQueries
    The articles included many examples that should show you how this is done.
    In essence, the Name1 field for Attribute5 gives the list of tables used in each query

    It may be witchcraft to you but that is how its done in Access.
    If you want that information in code that is how you need to obtain it
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    this is even more witchcraft i found the solution, thanks both, very nice stuff

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    For the benefit of anyone else reading this thread, please describe the solution you found. Thanks
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    sure, i thought it was clear, cause i just copied the query

    Code:
    SELECT MSysQueries.Name1 AS DataSource FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId WHERE (((MSysObjects.Name)=Forms!ricerca!tabella1cb) And ((MSysQueries.Attribute)=5)) ORDER BY MSysObjects.Name, MSysQueries.Name1;
    the only difference is in the WHERE clause:

    my solution
    Code:
    WHERE (((MSysObjects.Name)=Forms!ricerca!tabella1cb) And ((MSysQueries.Attribute)=5))
    original solution:
    Code:
    WHERE (((MSysObjects.Name) Not Like "~sq*")
    AND ((MSysQueries.Attribute)=5))
    cause i want the tables of a specific query, not every query

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Its clear now.
    I asked as you said you had 'found' the solution suggesting you had used a different method
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-03-2021, 04:01 PM
  2. Get field names fro table/query
    By diegomarino in forum Access
    Replies: 10
    Last Post: 11-19-2020, 10:39 AM
  3. Query using changing table names
    By Wonderifican in forum Queries
    Replies: 9
    Last Post: 10-22-2019, 04:41 PM
  4. Replies: 7
    Last Post: 03-21-2018, 04:58 AM
  5. Replies: 11
    Last Post: 10-30-2016, 05:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums