Enumerating Access reports

[AdaHacker]

Can someone tell me what's wrong with this code? I'm just trying to loop through the reports in an Access .mdb and extract the names, so that I can give my users a nice list of reports to print. I'm using Access 97 and VB 6 to do this. Everything looks good to me, but it never enters the loop. When I check the Reports.Count property, it tells me 0, but I have 2 reports in there. What's going on?

    Dim AccessApp As Access.Application
    Dim rep As Access.report
    
    Set AccessApp = New Access.Application
    
    AccessApp.Visible = False
    AccessApp.OpenCurrentDatabase "mydb.mdb"

    For Each rep In AccessApp.Reports
        Report_list.AddItem rep.name
    Next

    AccessApp.CloseCurrentDatabase
    Set AccessApp = Nothing

 

[MichaelRed]

You need to "access" (pun NOT intended) the documents collection. Reports only has the "Open" objects. Since the instance of Ms. A. was just created in your code, no objects are open, do all of the Specific "Objects" collections will report "No Members". The approach is o.k. but the collection is wrong. BTW the documents collection will need to reference the subcollection "reports". All quite confusing, using the same noun to reference the "same" objects from different perspectives.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[AdaHacker]

Thank you for the response.

I'm really confused now. I understand that the Application.Reports collection only contains the open reports, but how can I get at the unopen reports? You say to use the Documents collection, but where is that? How do I access it? I searched the Access library and I can't find any reference to it. Can you give me some specifics?

 

[MichaelRed]

The following is really from within MS. A. using VBA. Some syntax may be different in VB. I don't think so, but don't have VB up and need to do oh=ther thinggys right now.

Public Function basDocColl(Coll As String)

    Dim dbs As DAO.Database
    Dim doc As Document

    'Michael Red 12/7/2001
    '? basDocColl("Reports")
    'Sample Usage
    'Documents in Reports container
        'rptContributions
        'rptMarkDupAndSkip
        'rptRunningSum
        'rptTournament
        'rptVarLines
        'sbrptVarLines



    Set dbs = CurrentDb

    With dbs.Containers(Coll)

        Debug.Print "Documents in " & .name & " container"
        ' Enumerate the "Coll" collection
        For Each doc In .Documents
            Debug.Print "  " & doc.name
        Next doc

    End With

    dbs.Close

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[AdaHacker]

Thanks for the code MichaelRed. I tried it out and it works more or less as posted. Not exactly the most intuitive object design, but I guess I can see where they got it from.

While playing around with this question, we discovered that it's actually noticably faster to extract just the names if you use ADO to make a quick query against the MSysObjects table in the database. This requires changing the security on the database to make system objects readable, but that isn't a big deal to us. After that, a simple "SELECT name FROM MSysObject WHERE type = -32764" gives all the report names. Kind of handy, and much faster than using the collections.

 

[MichaelRed]

Yes, but (and there is always a "BUTT&quot -the MSys objects are not "officially" documented as users accessible objects. MS has stated that these objects are ' ... subject to change ..." AND they have followed through on the implied threat on several occassions. I would advise aginst using these for ANY production work, and only use it VERY cautiously for ad-hoc purposes. After all, in a producction app it will (probably) show up as an extra "bug" in some future upgrade, and the use in an ad-hoc process will (probably) end up in the production process. Once you have the functionallity, won't you just embed it in some procedure which is instantiated by a user? Why wouldn't you put the list in a combo box for users to be able to select from?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[AdaHacker]

Yeah, I figured it wasn't the recommended way to do it, and if this was a "production app" I would've been concerned. But since I can count my users on one hand, I wasn't going to worry too much about it. Especially since this entire module is nothing but a quick hack to add a non-critical (read "unimportant&quot feature.

If it makes you feel any better, I probably will use the DAO code. I tried rewriting the code without using the Access library at all, and it's now acceptably fast. Not quite as good as ADO, but close enough.

 

[MichaelRed]

Hmmmmmmmmm,

I don't feel better / worse with any decision likely to not intercect with my existance. I just offer opinion / advice (OpEd?). Sort of the poor man's 'talking head' (like a columnist or Sunday morning political pundit).

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[AdaHacker]

LOL Well, that seems like a good attitude to me. And you're advice is certainly more useful than anything I've ever seen on the OpEd page, so thanks for giving it.