?
Solved

ADP: Where is msysobjects info stored?

Posted on 2003-03-12
5
Medium Priority
?
699 Views
Last Modified: 2013-12-05
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
Comment
Question by:rgrimm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8124791
Have a look at the CurrentProject.AllForms collection, CurrentProject.AllReports Collection etc..

See help: AccessObjectProperties Collection


Alan
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 750 total points
ID: 8124798
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
 
LVL 8

Expert Comment

by:dovholuk
ID: 8124902
>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
 

Author Comment

by:rgrimm
ID: 8136787
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8149293
Thanks rgrimm

Regards Alan

 
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question