Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List reports and their record sources (Access 97)

    I want a procedure to enable me to print out the name of each report, the name of it's record source.. and the name (possibly) of it's record source's source.. if that makes sense. That is, the reports are generally based on a query which is based on another query. I'm sure somebody must have done this before?

    Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List reports and their record sources (Access 97)

    You can use Tools | Analyze | Documenter to list reports and their record sources, but it doesn't perform recursion on the record source.

    For a much more comprehensive tool, see FMS Total Access Analyzer. It's not cheap, but it is very powerful, and you can download a free trial version.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List reports and their record sources (Access 97)

    I'm doing this at work and don't have access to the Documentor, or wish to download software. I'm getting there in stages:
    I can use the Containers object to list just the report names; for each report I am then using DoCmd to open the report in Design View so that I can read it's RecordSource property and store this in a string variable. I now have the query name (on which each report is based) and need to obtain the 'record source' or 'table name's' for this query??! Can I extract this from the Queries' SQL statement? Thanks, Andy.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List reports and their record sources (Access 97)

    You can obtain the SQL properry of a QueryDef object. If the query is based on one table or other query only, the name of this table/query is the first "word" after FROM. If the query is based on more than one table/query, you'd have to parse the FROM clause of the SQL, looking for the "word" after each occurrence of JOIN, taking into account that Access insert lots of parentheses into an SQL string.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List reports and their record sources (Access 97)

    Thank you. Luckily the queries are only based on one prior query. So to extract the second word of the SQL string would you use InStr twice (to find two spaces) and Mid to extract the word? As this is 97 I don't think many other text functions are available.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List reports and their record sources (Access 97)

    I have attached a text file with two functions:

    GetSourceFromSQL takes an SQL string and extracts the first name after FROM. Example of use:

    =GetSourceFromSQL("SELECT LastName, FirstName FROM qryNames;")

    GetSourceFromQueryName takes the name of a query and uses GetSourceFromSQL to extract the first name after FROM. Example of use:

    =GetSourceFromQueryName("qryNames")

    For others reading this: the code requires that a reference to the appropriate DAO library has been set. This is default in Access 97 and Access 2003, but not in Access 2000 and Access 2002.
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: List reports and their record sources (Access 97)

    This is something I've attempted to do in past, with varying results, so tried again.... If interested see attached exported code module. To test, run this procedure, specifying name of query:

    ListSourceTables "Invoices", False

    The second parameter, bRecurse, merely indicates procedure was not called recursively (recursion level tracked for indenting the printed out results where there are nestled queries). The ParseSQL sub parses SQL string for specified query. If any of the source tables/queries are queries rather than tables, the ListSourceTables procedure calls itself recursively to get these queries' sources as well. If you have nestled queries, and more than one use same query as a source, there may be some duplication of effort in results.

    Caveats: Do not try this if you created queries with a circular rference (Query1 has Query2 as source and vice versa); the procedure will call itself repeatedly till you run out of memory or stack space. Obviously such a query would not be useful as a RecordSource, since you can't open it. Did not test for subqueries; tested for WHERE clause only once (code does check for all JOIN clauses). Did not test for Really Stupid table names, for example Access will let you name a table "(FROM)". At least brackets are illegal in object name. Should work OK if you use normal naming conventions. Example of output, Northwind "Invoices" query:

    ListSourceTables "Invoices", False
    Query Name: Invoices
    Query SQL: SELECT DISTINCTROW [Orders].[ShipName], [Orders].[ShipAddress]... (etc)
    FROM Customers INNER JOIN ((Employees INNER JOIN (Shippers INNER JOIN Orders ON [Shippers].[ShipperID]=[Orders].[ShipVia]) ON [Employees].[EmployeeID]=[Orders].[EmployeeID]) INNER JOIN (Products INNER JOIN [Order Details] ON [Products].[ProductID]=[Order Details].[ProductID]) ON [Orders].[OrderID]=[Order Details].[OrderID]) ON [Customers].[CustomerID]=[Orders].[CustomerID];

    Source Tables/Queries:
    1 Customers (Table)
    2 Employees (Table)
    3 Shippers (Table)
    4 Orders (Table)
    5 Products (Table)
    6 Order Details (Table)

    (SQL string abbreviated.) You may be able to adapt some of this. Requires reference to DAO library.

    HTH
    Attached Files Attached Files

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: List reports and their record sources (Access 97)

    PS: Modified sample code, simplified ParseSQL sub using loop to test for all FROM or JOIN clauses in SQL string so returns correct results in case of Union queries and subqueries with more than one FROM clause. Also added function StripLiterals to strip any literal values delimited by single or double quotes from SQL string, so text included query criteria is not falsely evaluated as FROM or JOIN keywords. If interested see revised text file attached.

    HTH
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: List reports and their record sources (Access 97)

    One more note - if using Access 97, the Replace function not available in VBA 5.0 (introduced in VB/VBA 6.0). Example of user-defined replacement for Replace function:

    <code>Public Function ReplaceString(ByVal Expression As String, _</code>
    <code> ByRef Find As String, _</code>
    <code> ByRef Replace As String, _</code>
    <code> Optional Start As Long = 1, _</code>
    <code> Optional Count As Long = -1, _</code>
    <code> Optional Compare As VbCompareMethod = vbBinaryCompare) _</code>
    <code> As String</code>
    <code> On Error GoTo Err_Handler</code>

    <code> Dim strMsg As String</code>
    <code> Dim s As String</code>
    <code> Dim n As Long</code>
    <code> Dim lPos As Long</code>

    <code> If Len(Expression) > 0 Then</code>
    <code> If (Len(Find) > 0) And (Count <> 0) Then</code>
    <code> If Len(Expression) >= Start Then</code>
    <code> ' Start specifies position replacement starts at:</code>
    <code> s = Mid$(Expression, Start)</code>
    <code> lPos = 1</code>
    <code> Do Until InStr(lPos, s, Find, Compare) = 0</code>
    <code> lPos = InStr(lPos, s, Find, Compare)</code>
    <code> s = Left$(s, lPos - 1) & Replace & Mid$(s, lPos + Len(Find))</code>
    <code> lPos = lPos + Len(Find)</code>
    <code> n = n + 1</code>
    <code> If Count > 0 Then</code>
    <code> ' Replace n occurrences of Find only:</code>
    <code> If Count = n Then</code>
    <code> Exit Do</code>
    <code> End If</code>
    <code> End If</code>
    <code> Loop</code>
    <code> ReplaceString = s</code>
    <code> Else</code>
    <code> ' Start > Len(Expression):</code>
    <code> ReplaceString = vbNullString</code>
    <code> End If</code>
    <code> Else</code>
    <code> ' Find is zero-length, or Count = 0</code>
    <code> ' Return original string:</code>
    <code> ReplaceString = Expression</code>
    <code> End If</code>
    <code> Else</code>
    <code> ' Expression is zero-length:</code>
    <code> ReplaceString = vbNullString</code>
    <code> End If</code>

    <code>Exit_Sub:</code>
    <code> Exit Function</code>
    <code>Err_Handler:</code>
    <code> strMsg = "Error No " & Err.Number & ": " & Err.Description</code>
    <code> MsgBox strMsg, vbExclamation, "REPLACE STRING ERROR MSG"</code>
    <code> Resume Exit_Sub</code>
    <code>End Function</code>

    Note that the parameters, optional args, return values, etc are intended to be identical to those of the "real" Replace function, as described in Help. If utilizing code that uses Replace in an A97 project, you could simply do a Search & Replace, and the code should work correctly. Even in A2K or later, you can modify custom function if you don't like some aspect of how actual Replace function works. For example, some may not like that if you specify Start parameter, it will cause input string to truncated up to specified location in string, rather than merely start replacing at that position. To remedy this you'd delete the s = Mid$(Expression, Start) statement, and set initial value of lPos to equal value of Start parameter. Other modifications can be made as desired.

    HTH

Posting Permissions

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