Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Documenting Queries (97)

    Is there a way to use code to document the tables, fields, and criteria of a query? I would like to store the data in a table so reports could be generated.

    The database I am working with has all the major query type, Select, Delete, Append, and Update.


    Thanks for your help

    <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Richard

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Documenting Queries (97)

    In Access 97, you can already use the built-in Documenter (Tools --> Analyze --> Documenter) to generate a bunch of information in a report and then select Save asTable from the File menu to generate an Object Definition table. For some obscure reason, they removed the Save As Table capability in Access 2000.

    Otherwise, you would need to create a querydef object and a recordset object in code and then step through its properties collection of the querydef to collect that information and write it to the recordset.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Documenting Queries (97)

    I have code that loops through the querydefs and puts the source table and fields in to a table.

    Two limitations this code has is it only reports fields for Select queries and it does not report criteria.

    Are these available through the proprties? I have not been able to find a way to get this info so far.


    Thanks for you help.

    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Richard

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Documenting Queries (97)

    You can extract criteria from the SQL by parsing out the WHERE clause. Each querydef has a SQL property that will return the entire SQL statement for the query. You can also examine the parameters collection of the querydef for parameters (criteria) that have to be populated, i.e., [Enter CustomerID], etc.

    I don't know why your code would only handle select queries unless you're filtering them by query type. You won't get any fields for action queries because they don't return rows, but they all have a SQL property.
    Charlotte

  5. #5
    Lounger
    Join Date
    Aug 2001
    Location
    Toronto, Ontario, Canada
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Documenting Queries (97)

    Any chance either one of you could elaborate on this topic a bit more? This sounds like exactly what I need to do:

    I have code for ONE QUERY that loops through a list of sales reps and applies each name in turn as the criteria and exports the report under that person's name. I've been trying to add an extra loop that would go through the first query, loop through that and generate each person's report; THEN go to the next query and do the same thing, etc, etc. The rsCriteria loop works perfectly when I type in the name of the queries/reports as text strings.

    I obtained and modified the original loop code from Roger's Access Library (PrintingSpecificReports) : he's labelled that "Expert Level" and I ain't nowhere close to that.

    Here's my lay attempt at adding that extra loop - would anyone be able to point me in the right direction for correcting this. The error happens at the Set qdf line "Error in FROM clause".

    Thanks in advance.

    rsReports.MoveFirst
    Do Until rsReports.EOF

    rsCriteria.MoveFirst
    Do Until rsCriteria.EOF

    strSQL = "SELECT * FROM rsReports![QueryReportMain] WHERE "
    strSQL = strSQL & "[Manager] = '" & rsCriteria![Manager] & " '"

    db.QueryDefs.Delete rsReports![QueryReport]
    Set qdf = db.CreateQueryDef(rsReports![QueryReport], strSQL)

    DoCmd.OutputTo acReport, rsReports![ReportName], "SnapshotFormat(*.snp)", "X:AnixterMonthly IAP InfoBizman" & rsCriteria![FileName] & rsReports![alias] & ".snp", False, ""

    rsCriteria.MoveNext
    Loop
    rsCriteria.close

    rsReports.MoveNext
    Loop
    rsReports.close

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Documenting Queries (97)

    <hr>strSQL = "SELECT * FROM rsReports![QueryReportMain] WHERE "<hr>
    If this is the FROM clause in question, then I can see your problem. You can't run a query on an open recordset. You can filter a recordset using the Filter method or you can open another recordset based on the same SQL source but with a WHERE clause, but you can't do what you're trying to do the way you're trying to do it.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •