• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 706
  • Last Modified:

ADP: Where is msysobjects info stored?

Access 2002
SQL Server 2000
ADP Project

I am creating a Microsoft Access project (ADP) file where all the data is stored on a back-end SQL Server (2000, not MSDE). I want to generate a list of all the forms or all the reports to populate a combo box. I know how to do it in an MDB database (msysobjects table), but I can't find where Access stores the info in an ADP environment.

(Is it possible it is stored in the CurrentProject.AllForms collection? If so, where is it persisted when the database is closed?)

Where is this info stored, and what would the code be to populated a combo box with the names of all the reports in my project?
0
rgrimm
Asked:
rgrimm
  • 3
1 Solution
 
Alan WarrenCommented:
Have a look at the CurrentProject.AllForms collection, CurrentProject.AllReports Collection etc..

See help: AccessObjectProperties Collection


Alan
0
 
Alan WarrenCommented:
AllReports Collection Example

The following example prints the name of each open AccessObject object in the AllReports collection.

Sub AllReports()
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject
    ' Search for open AccessObject objects in AllReports collection.
    For Each obj In dbs.AllReports
        If obj.IsLoaded = TRUE then
            ' Print name of obj.
            Debug.Print obj.Name
        End If
    Next obj
End Sub
0
 
dovholukCommented:
>Is it possible it is stored in the CurrentProject.AllForms collection?
as noted above by alanwarren, it definately IS...

>If so, where is it persisted when the database is closed?)
to my knowlege, you can not access forms collection when the file is not open (through access or other).  however, since you're creating a file, the forms are DEFINATELY persisited somewhere in that file, they're just not in a format that will be easily extractable...

>what would the code be to populated a combo box with the names of all the reports in my project?
again, alanwarren answered much of this for you above. here's how to populate a combo box called cboMyCombo with a button on a form called cmdMyButton. set the combo's

Private Sub cmdMyButton_Click()
   
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject
   
    ' Search for open AccessObject objects in AllReports collection.
    For Each obj In dbs.AllReports
        Me.cboMyCombo.RowSource = Me.cboMyCombo.RowSource & Chr(34) & obj.Name & Chr(34) & ";"
    Next obj

End Sub

please don't give me the points if you like this answer... alanwarren answered most of this above, i'm just "tidying up" to give you what you need if you don't feel all that confident with your coding...

enjoy

dovholuk
0
 
rgrimmAuthor Commented:
Many thanks to Alan et al for confirming my fears and pointing me in the right direction. Below is my final solution for filling a combobox with a sorted list of all the reports in an ADP project.

I pumped the report names I extracted from the AllReports collection through an ADO recordset so I could sort it easily.

Private Sub Form_Load()
Dim obj As AccessObject
Dim dbs As Object
Dim rsReports As ADODB.Recordset

   Set dbs = Application.CurrentProject
   Set rsReports = New ADODB.Recordset
   
   'Create ADO recordset to be a virtual table to store report info
   With rsReports
        With .Fields
            .Append "Name", adVarChar, 255
        End With
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .Open
    End With
   
   ' Search for report objects in AllReports collection.
   For Each obj In dbs.AllReports
        rsReports.AddNew
            rsReports!Name = obj.Name
        rsReports.Update
   Next obj
   
   'Sort recordset by the name field in ascending order
   rsReports.Sort = "Name ASC"
   rsReports.MoveFirst
   
   'Fill the combobox with the sorted report names
   cboReports.RowSourceType = "Value List"
   Do Until rsReports.EOF
        cboReports.AddItem rsReports!Name
        rsReports.MoveNext
    Loop
   
End Sub
0
 
Alan WarrenCommented:
Thanks rgrimm

Regards Alan

 
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now