December 13th, 2012, 03:48 PM
'Metadata report from access'
Is there are way to generate a list of all the objects created in my Access DB, in 'report forma report', or that I could transfer to, maybe, Word.
Tried searching for 'metadata report' in helps screens and forums, though in truth not sure what it would be called.
December 13th, 2012, 05:04 PM
What is 'report forma report'?
What version of Access?
Access 2007 ribbon: DatabseTools>DatabaseDocumenter
Will generate a very large report and runs very slow and outputs only to printer.
Unhide system tables. Look at MSysObjects table. You can build Access report of these records or export to Word. But DO NOT modify the records.
December 13th, 2012, 05:06 PM
Which version of Access are you using. The instructions for accessing the hidden system tables is slightly different depending on the version of Access you are using. But briefly you need to unhide the system tables - locate the MSysObjects table and note the fields you need to query. Create a query and use this query as your record source for a report. Make sure to hide these tables after you finish
Office 2010, 2007, 2002
If I helped you, then click "give rep" button in the lower left corner.
December 13th, 2012, 07:00 PM
You might also have some luck with the msysNameMap table. it lists all of the database objects and a type ID. You can use the type id to sort them on a report. (basically the same thing as the MsysObjects table, but with less data in it to accidently mess up.)
December 16th, 2012, 11:52 AM
Thanks guys, I've been able to build a query from MSysObjects to give me what I needed
February 27th, 2014, 01:38 PM
In at least Microsoft Access 2007 and 2013, it has been my experience that some objects do not show up in the msysNameMap table so use MSysObjects instead. You might better try this code adding any other item you do not want to include in the Not IN items are use other criteria to find just reports, forms etc. Having strict naming conventions helps too, for example reports always begin with Rpt_, forms always begin with "frm" for example:
select name from MSysObjects
where mid(name,1,4) NOT IN ("~sq_","~TMP","MSys")
order by name