473,373 Members | 1,138 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,373 software developers and data experts.

MSysObjects - Can U modify The Contents???

132 100+
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;
Aug 29 '07 #1
3 7034
1,356 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1. Sub AllReports()
  2.     Dim obj As AccessObject, dbs As Object
  3.     Set dbs = Application.CurrentProject
  4.        For Each obj In dbs.AllReports
  5.             ' Print name of obj.
  6.             Debug.Print obj.Name    
  7.     Next obj
  8. End Sub
Aug 29 '07 #2
3,532 Expert 2GB
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)>
Aug 29 '07 #3
1,356 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1. Function GetReports(Fld As Control, id As Variant, row As Variant, col As Variant, Code As Variant) As Variant
  2.   Static dbs() As String, Entries As Integer
  3.   Dim ReturnVal As Variant
  4.   Dim con As Container
  5.   Dim db As Database
  6.   Dim doc As Document
  7.   Set db = CurrentDb
  8.     ReturnVal = Null
  9.     Select Case Code
  10.         Case acLBInitialize
  11.             Entries = 0
  12.   Set con = db.Containers("Reports")
  13.     ReDim dbs(con.Documents.Count)
  14.     For Each doc In con.Documents
  15.         Set doc = con.Documents(doc.Name)
  16.         doc.Properties.Refresh
  17.         'The Following restricts my records to the ones preceding the letters SHW
  18.         If Left(doc.Name, 3) = "SHW" Then
  19.                  dbs(Entries) = Right(doc.Name, Len(doc.Name) - 3) ' doc.Container                                    'doc.Properties("Caption")
  20.             Entries = Entries + 1
  21.         End If
  22.     Next doc
  23.                 ReturnVal = Entries
  24.         Case acLBOpen
  25.             ReturnVal = Timer
  26.         Case acLBGetRowCount
  27.             ReturnVal = Entries
  28.         Case acLBGetColumnCount
  29.             ReturnVal = 1
  30.         Case acLBGetColumnWidth
  31.             ReturnVal = -1
  32.         Case acLBGetValue
  33.             ReturnVal = dbs(row)
  34. Case acLBEnd
  35.             Erase dbs
  36.     End Select
  37.     GetReports = ReturnVal
  38.     End Function

If you have any questions or comments let me know.
Aug 29 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Anon | last post by:
I have a table with a cell. The cell's ID is created using a unique name that is held in m_UniqueCellName and the cell is created like so... document.write( "<TD ID="' + m_UniqueCellName +...
by: pierre.bru | last post by:
hi, I encounter troubles with frames :( witht he folowing script in the _top page function walk(_frames) { for(var i=0; i<_frames.length; i++) { var frame = _frames; alert(frame.name);
by: Frank Thiel | last post by:
Hello, we write into an MSACCESS 2.0 DB. These files are on a server. We get crashes and when I try to repair the corrupt DB I get messages like : one or more entries in MSysObjects (...
by: Simon Radford via AccessMonster.com | last post by:
I have a form which contains a field that collects the MSysObjects list of queries within the system. Also on this form there is a button that is told to apply the query chosen by the user to a...
by: Charles Sullivan | last post by:
I'm working on a program which has a "tree" of command line arguments, i.e., myprogram level1 ]] such that there can be more than one level2 argument for each level1 argument and more than one...
by: baumann.Pan | last post by:
when define char *p = " can not modify"; p ='b' ;is not allowed, but if you declare p as char p = "can modify"; p = 'b'; is ok? why?
by: darrel | last post by:
This is maybe a blue-sky wish, but I have to ask... Can .net access/modify the contents of an HTML tag on the page? For instance, in XML, one can grab and set the properties for a tag. In...
by: pnovreske | last post by:
I am dealing with a legacy VB6 app that has an Access backend. The db contains a linked table. I need to be able to edit the field (MSysObjects.Database) that contains the location of the...
by: Paul H | last post by:
Each time I run the user-level security wizard to create an mdw for my database I have noticed the following issue... Once I have run the wizard, I close the database and open the .mdw file I...
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.