Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Apr 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Enumerate External Reports

    Does anyone know how to enumerate the reports from an external A2K database into a listbox on a form in the Currentdb?
    I can't seem to get it to work for an external database. It works great for the Currentdb(). The line:
    Set docs = NewAccessApp.Containers!Reports.Documents
    Is not allowed. Please see below.

    Dim docs As Documents, DB as Database
    Dim AccessApp As New Access.Application
    Dim strDb As String, i as Integer

    strDb = "c:Testdb.mdb"

    If strDb <> CurrentDb().Name Then
    NewAccessApp.OpenCurrentDatabase strDb
    Set docs = NewAccessApp.Containers!Reports.Documents
    Else: Set DB = CurrentDb()
    Set docs = DB.Containers!Reports.Documents

    iRptCount = dox.Count
    For i = 0 To iRptCount - 1
    sRptName(i) = dox(i).Name
    Next

    I may be approching this all wrong. I'm sure someone will
    tell me if I'm barking up the wrong tree. I thought about
    setting DB to the External Database but I don't know how
    to do that or if it will work. Please advise.

    Thanks in advance.

  2. #2
    Lounger
    Join Date
    Apr 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enumerate External Reports

    I've also tried Charlotte's Code but I can't Get it
    to work Either.
    This is probably the right way to do it but I need
    help setting the database to get the Reports from
    to the one I desire and then to fill the Listbox
    in the Current Database.

    Function ADOReports()
    Dim obj As AccessObject
    Dim dbs As Object
    Dim Proj As Object
    Set Proj = Me.DbPathName & "" & Me.DBName
    Set dbs = Me.DbPathName & "" & Me.DBName
    ' loop through AccessObject objects
    ' in the AllReports collection.
    For Each obj In dbs.AllReports
    Me.lboReportToChoose = obj.Name
    Next obj
    End Function

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

    Re: Enumerate External Reports

    Which code did you try?
    Charlotte

  4. #4
    Lounger
    Join Date
    Apr 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enumerate External Reports

    The db that is running is C:Internal.mdb
    The db to get the Report list from
    is C:External.mdb
    My internal.mdb's listbox that I want to populate
    is called lboReportToChoose.

    I tried to use this code from message# 19984:

    Dim obj As AccessObject
    Dim dbs As Object
    Set dbs = Application.CurrentProject
    ' loop through AccessObject objects
    ' in the AllReports collection.
    For Each obj In dbs.AllReports
    [RepName]=obj.Name
    Next obj

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

    Re: Enumerate External Reports

    The ADO code uses CurrentProject, and I think you'll have to use DAO automation instead. At least, I don't know of any way to switch currentproject to point at a remote database, since CurrentProject points by definition at the project for the current Access database. There might be some tricks in the Access 2000 Developer's Handbook, if you have it handy. Or someone else may come up with some usable code.

    Here's the DAO equivalent of that code, if it helps:
    <pre>Public Function BuildDisplayList( _
    ByVal strContainer As String) As String
    'Created by Charlotte Foust 3/17/2000
    'Last modified 4/27/2001
    'Returns: Semicolon delimited list of names
    ' for documents in container .
    '
    'EXAMPLE: BuildDisplayList("Reports")


    Dim dbs As DAO.Database
    Dim ctr As DAO.Container
    Dim strDocs As String
    Dim intLoop As Integer

    Set dbs = CurrentDb()
    Set ctr = dbs.Containers(strContainer)
    For intLoop = 0 To ctr.Documents.Count - 1

    'concatenate the name to the list and add a semicolon
    strDocs = strDocs & ctr.Documents(intLoop).name _
    & ";"
    Next intLoop

    'Trim the trailing semicolon
    If Len(strDocs) <> 0 Then
    strDocs = Left(strDocs, Len(strDocs) - 1)
    End If 'Len(strDocs) <> 0

    BuildDisplayList = strDocs
    Set ctr = Nothing
    Set dbs = Nothing
    End Function 'BuildDisplayList( _
    ByVal strContainer As String) As String
    </pre>

    Charlotte

  6. #6
    Lounger
    Join Date
    Apr 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enumerate External Reports

    Thanks Charlotte,

    I finally figured it out! [img]/forums/images/smilies/smile.gif[/img] The Access 2K Developer's
    Handbook Volume 1 had the answer again. It is an Access
    variation of the Excel code in Chapter 13 and the
    Automation Class module on the CD ROM. I also used a
    routine by Allen Browne 1997 version called EnumReports
    to fill the listbox in my database once I got connected
    to the external database. Now I can Browse to any db and
    get the list of reports from it and even run them from
    my currentdb()'s Form.

    The Code below is probably not as efficient and I may be
    redundant with some things but I'm learning and it's still
    under development, however it works for me.

    Requires the Modules from A2K Developer Vol 1

    Option Compare Database
    Option Explicit

    Public MainApp As Database
    Public objAccAuto As New AccessAutomation
    Private Function AutoSession()
    Dim stAppName As String
    stAppName = Me.DbPathName & "" & Me.DBName
    Set objAccAuto = New AccessAutomation
    DoCmd.Hourglass True
    With objAccAuto
    .CreateNewInstance = True
    .FileName = stAppName
    .Init
    .Hide
    End With
    Me.lboReportToChoose.RowSourceType = "EnumReports"
    With objAccAuto
    .AccessInst.CloseCurrentDatabase
    .Hide
    End With
    DoCmd.Hourglass False
    End Function

    Private Function AutoPrint()
    Dim stAppName As String
    Dim stDocName As String
    Dim varTable As Variant
    Dim i As Integer
    stAppName = Me.DbPathName & "" & Me.DBName
    Set objAccAuto = New AccessAutomation
    DoCmd.Hourglass True
    For Each varTable In Me!lboReportToChoose.ItemsSelected()
    stDocName = Me!lboReportToChoose.ItemData(varTable)
    With objAccAuto
    .CreateNewInstance = True
    .FileName = stAppName
    .Init
    .Hide
    .AccessInst.Application.DoCmd.OpenReport (stDocName), acViewNormal
    End With
    Next
    With objAccAuto
    .AccessInst.CloseCurrentDatabase
    .Hide
    End With
    DoCmd.Hourglass False
    End Function

    Thanks again

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

    Re: Enumerate External Reports

    Glad you found an answer! Then ADH is a superb resource for stuff like that.
    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
  •