how to enumarting all forms, reports, queries....

[SF]

Hi,

Is there a way to enumeration forms, queries, reports in one database?

SF

 

[John W. Vinson]

Hi,

Is there a way to enumeration forms, queries, reports in one database?

SF

One way is Tools... Analyze... Documenter.

Or, take a look at the hidden system table MSysObjects. It contains them all.

Not clear just what you mean by "enumerate"...

 

[Tom Wickerath]

Hi SF,

You can run some queries against the MSys table:

Forms:

SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Type)=-32768))
ORDER BY msysobjects.Name;


Queries:

SELECT [msysobjects].[Name]
FROM msysobjects
WHERE ((([msysobjects].[Type])=5) And ((Left([Name],1))<>"~"))
ORDER BY [msysobjects].[Name];


Reports:

SELECT msysobjects.Name AS Reports
FROM msysobjects
WHERE (((msysobjects.Type)=-32764))
ORDER BY msysobjects.Name;

You can also use VBA code to enumerate all queries, forms and reports.
Access MVP Arvin Meyer has code that does that, here:

http://www.datastrat.com/Code/DocDatabase.txt

You would simply substitute using Application.SaveAsText for something like
Debug.Print doc.name (for forms and reports) and Debug.Print
dbs.QueryDefs(i).Name for the queries.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[SF]

Long time back, I have seen a function/sub that listed all Forms or report
thru the enumeration of domunets in the container. I think It was when
Access 2000 or 2002 release.

My aim is to write a function to delete all forms and reports

SF

 

[John W. Vinson]

Long time back, I have seen a function/sub that listed all Forms or report
thru the enumeration of domunets in the container. I think It was when
Access 2000 or 2002 release.

My aim is to write a function to delete all forms and reports

Sounds rather brutal!!! Why not create a new database (with the CreateDatabase
method if you want to do it from code) and import what little you want to
save?

That said, see the VBA help for the AllForms and AllReports collections. You
could loop through them deleting each member. Compact the database when you're
done!

 

[John Spencer]

If all you want to do is DELETE all the forms or all the reports you can step
through the documents in the container in reverse order.

Make sure you have a backup before you do this.

UNTESTED code follows

Public Sub sDeleteAllForms()
Dim i As Long

For i = CurrentProject.AllForms.Count - 1 To 0 Step -1
DoCmd.DeleteObject acForm, CurrentProject.AllForms(i).Name
Next i

End Sub

You can do the same thing with containers. The trick is to step through the
collection from the highest number to the lowest number so that the reordering
of the collection does not affect the position of the next object to be deleted.

The equivalent code using containers would be

Dim db As DAO.Database
Dim C as Container
Dim i as Long
Set db = CurrentDb()
Set c = db.Containers("Forms")
For i = c.Documents.Count - 1 To 0 Step -1
DoCmd.DeleteObject acForm, c.Documents(i).Name
Next i

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Long time back, I have seen a function/sub that listed all Forms or report
thru the enumeration of domunets in the container. I think It was when
Access 2000 or 2002 release.

My aim is to write a function to delete all forms and reports

SF

 

[John Mishefske]

One way is Tools... Analyze... Documenter.

Or, take a look at the hidden system table MSysObjects. It contains them all.

Not clear just what you mean by "enumerate"...

Perhaps this is what you are looking for:

Public Function ListAllForms()

Dim accobj As AccessObject

For Each accobj In CurrentProject.AllForms
Debug.Print accobj.name
Next

End Function

--
John Mishefske, Microsoft MVP 2007 - 2009
UtterAccess Editor
Tigeronomy Software
web: http://www.tigeronomy.com
email: sales ~at~ tigeronomy.com