Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Find the table name

Discussion in 'Information Technology' started by Rich, Jul 28, 2006.

Tags:
  1. Rich

    Rich
    Expand Collapse
    Guest

    I have inherited an Access Database with several make table querys and
    reports. My problem is the previous person that had the data base knew what
    make table query to run before the report was ran.

    My question is how can i find that correct query to run? The report is
    sourced back to a table (I have the name) I just do not know what query to
    run. I would have to open each query in design mode to determine what table
    that query made.

    Is there a system table that has the sql statement in it that makes the
    table? Then I could just do a word search for that table name, pinpointing
    what query ran.

    Rich
     
  2. Loading...

    Similar Threads Forum Date
    Opinion Alcohol: A Social Disease that Many Malaysian Sikhs Find Irresistable Breaking News Aug 31, 2013
    Danish zookeepers find muskrats delectable (Reuters) Interfaith Dialogues Jun 19, 2005
    Pathfinder Joins Sikh Philosophy Network! New SPN'ers Sep 5, 2016
    The Salmon Story, Finding Home Sikh Sikhi Sikhism Jul 29, 2016
    Sikhi Guru Nanak's Methodology Of Finding Truth Sikh Sikhi Sikhism Jan 1, 2016

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Rich,

    How many Make-Table Queries are there in the database? If you know the
    name of the table you are looking for, it probably wouldn't really take
    all that long to look at each of them one by one. Probably less time
    than it took to write your post, unless there are a large number.

    You could run a VBA routine, I suppose. Something like this...

    Dim qdf As DAO.QueryDef
    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
    For Each qdf In CurrentDb.QueryDefs
    If qdf.SQL Like "INTO " & tdf.Name Then
    Debug.Print tdf.Name & ": " & qdf.Name
    End If
    Next qdf
    Next tdf

    .... but once again, probably take longer to write the VBA than to look
    in the query design ;-).

    --
    Steve Schapel, Microsoft Access MVP


    Rich wrote:
    > I have inherited an Access Database with several make table querys and
    > reports. My problem is the previous person that had the data base knew what
    > make table query to run before the report was ran.
    >
    > My question is how can i find that correct query to run? The report is
    > sourced back to a table (I have the name) I just do not know what query to
    > run. I would have to open each query in design mode to determine what table
    > that query made.
    >
    > Is there a system table that has the sql statement in it that makes the
    > table? Then I could just do a word search for that table name, pinpointing
    > what query ran.
    >
    > Rich
     
  4. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Find the table name -- Documenter

    Hi Rich,

    Tools, Analyze, Documenter...

    you can click on the options... button and make sure SQL is
    checked

    After the documentor is done, you can print the report it makes

    use a highlighter to emphasize the table name after any INTO
    keyword


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Rich wrote:
    > I have inherited an Access Database with several make table querys and
    > reports. My problem is the previous person that had the data base knew what
    > make table query to run before the report was ran.
    >
    > My question is how can i find that correct query to run? The report is
    > sourced back to a table (I have the name) I just do not know what query to
    > run. I would have to open each query in design mode to determine what table
    > that query made.
    >
    > Is there a system table that has the sql statement in it that makes the
    > table? Then I could just do a word search for that table name, pinpointing
    > what query ran.
    >
    > Rich
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Find the table name -- formatting SQL in Word

    try this to document the SQL for Queries:

    run the Documentor on Queries, click on the Options...
    button -- choose only SQL and NOTHING for fields or indexes
    -- then you can right-click on the report that is generated
    and export it to RTF format -- now you have a document in
    Word with all of the SQL from your queries.

    then do the following:

    search --> ,
    replace with --> ,^p^t
    (comma) (^p=paragraph mark) (^t=tab)

    search --> FROM
    replace with --> ^pFROM

    (FROM has a space after it)

    search --> WHERE
    replace with --> ^pWHERE

    (WHERE has a space after it)


    search --> INNER JOIN
    replace with --> ^pINNER JOIN

    (INNER JOIN has a space after it)

    search --> ON
    replace with --> ^p^tON

    (ON has a space after it)


    search --> AND
    replace with --> ^p^tAND

    (AND has a space after it)

    search --> INTO
    replace with --> ^p^tINTO
    --> format = bold

    (INTO has a space after it)

    etc

    I use heading1 tags for the query name, format the SQL so it
    is easier to read (modify it after you run the mass
    search/replace for specific instances such as with commas
    that didn't break right)

    Use spacing to balance parenthesis and groupings



    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    strive4peace wrote:
    > Hi Rich,
    >
    > Tools, Analyze, Documenter...
    >
    > you can click on the options... button and make sure SQL is checked
    >
    > After the documentor is done, you can print the report it makes
    >
    > use a highlighter to emphasize the table name after any INTO keyword
    >
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > Rich wrote:
    >
    >> I have inherited an Access Database with several make table querys and
    >> reports. My problem is the previous person that had the data base
    >> knew what make table query to run before the report was ran.
    >>
    >> My question is how can i find that correct query to run? The report
    >> is sourced back to a table (I have the name) I just do not know what
    >> query to run. I would have to open each query in design mode to
    >> determine what table that query made.
    >>
    >> Is there a system table that has the sql statement in it that makes
    >> the table? Then I could just do a word search for that table name,
    >> pinpointing what query ran.
    >>
    >> Rich
     
  6. George Walsh

    George Walsh
    Expand Collapse
    Guest

    Here is an idea. Start with a particular report and view it in design mode.
    Check the property information of the report (i.e., just right click on any
    blank space outside of the designed area of the report - i.e., somewhere
    outside the designed area in the detail section of the report) and make
    note of the recordsource selected as the basis for the fields which the
    report prints.

    If the recordsource for the report is either a table or a query which uses a
    table that is one which is created by one of the "Make Table" queries in
    your database, then you can deduce that that particular table needed to be
    Made or refreshed in order to produce the information in the fields which is
    the basis for the specific report.

    You can also judge by the fields of information in the specific Table. The
    concept is to work backward from the report output, or the form output of
    the database - i.e., whatever elements your database uses as the "use" or
    "output" of field information - since the Tables and Queries are commonly
    created in order to facilitate the data presentation and/or output functions
    of forms and reports.


    "Rich" <Rich@discussions.microsoft.com> wrote in message
    news:358A5E3D-82E5-48DC-BE45-028502E29F55@microsoft.com...
    >I have inherited an Access Database with several make table querys and
    > reports. My problem is the previous person that had the data base knew
    > what
    > make table query to run before the report was ran.
    >
    > My question is how can i find that correct query to run? The report is
    > sourced back to a table (I have the name) I just do not know what query to
    > run. I would have to open each query in design mode to determine what
    > table
    > that query made.
    >
    > Is there a system table that has the sql statement in it that makes the
    > table? Then I could just do a word search for that table name,
    > pinpointing
    > what query ran.
    >
    > Rich
     

Share This Page