MSysObjects - Can U modify The Contents???

[Proaccesspro]

I have a query that uses msysobjects to obtain the names of every report contained in a database. With that, I populate a listbox. The problem is, I guess at one time I created a couple of reports that I have since deleted. However, looks like they are stored in the msysobjects as temp files.....So when I run the app, the listbox contains 4 temp files....how do I get rid of the temp files???? I am working around it by excluding anything that starts with ~TMP as seen in the SQL below.....but how can I get to the MSysObjects and delete those temp files???

SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Name) Not Like "~Tmp*") AND ((msysobjects.Type)=-32764))
ORDER BY msysobjects.Name;

[Denburt]

As a long time MS Access user i would suggest leaving the system tables alone and use the reports collection.

Here is a snipit straight out of the help file. Hope it helps.

Sub AllReports()
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject
       For Each obj In dbs.AllReports
            ' Print name of obj.
            Debug.Print obj.Name    
    Next obj
End Sub
 

[missinglinq]

All tables that start with MSys are Read-only so, no, you can't do anything except look at them. Access is a pack rat and tends to kept everything in the attic, so to speak! That's why people run into the 750 controls/form limit with large/long standing apps. No one has that many controls on a single form, but once it's been on a form, even if it's deleted later, Access still counts it toward the limit.

My only suggestion would be to try the old open a new, blank db and import everything into it routine and see if that eleminates the temp tables. BTW, Access only has one file, the MDB or MDE file that holds everything! Everything else are objects!

Linq ;0)>

[Denburt]

After rereading your post I though I would dig up a routine I have been working on for filling a listbox with my reports. It isn't as clean as I would like yet but hopefully you might find some use for it. In the list box I set the rowsourcetype to GetReports I don't enter anything for the row source. In my case I dont want to view all reports only certain ones so I precede those with SHW and I am set. If you would like all reports remove that if statement and you should be fine.

Function GetReports(Fld As Control, id As Variant, row As Variant, col As Variant, Code As Variant) As Variant
  Static dbs() As String, Entries As Integer
  Dim ReturnVal As Variant
  Dim con As Container
  Dim db As Database
  Dim doc As Document
  Set db = CurrentDb
    ReturnVal = Null
    Select Case Code
        Case acLBInitialize
            Entries = 0
  Set con = db.Containers("Reports")
    ReDim dbs(con.Documents.Count)
    For Each doc In con.Documents
        Set doc = con.Documents(doc.Name)
        doc.Properties.Refresh
        'The Following restricts my records to the ones preceding the letters SHW
        If Left(doc.Name, 3) = "SHW" Then
                 dbs(Entries) = Right(doc.Name, Len(doc.Name) - 3) ' doc.Container                                    'doc.Properties("Caption")
            Entries = Entries + 1
        End If
    Next doc
                ReturnVal = Entries
        Case acLBOpen
            ReturnVal = Timer
        Case acLBGetRowCount
            ReturnVal = Entries
        Case acLBGetColumnCount
            ReturnVal = 1
        Case acLBGetColumnWidth
            ReturnVal = -1
        Case acLBGetValue
            ReturnVal = dbs(row)
Case acLBEnd
            Erase dbs
    End Select
    GetReports = ReturnVal
    End Function
 

If you have any questions or comments let me know.