Access 2013 MetaData and Schema into .csv format

Question

Could anyone share where to find information on exporting the metadata from an Access 2013 .accdb file to a spreadsheet format for analysis?

The Database Documenter feature allows printing of all of this information in a clearly readable format, but it makes for difficult analysis across more than one database. For example, this report is 32 pages for the Northwind database, and ends up being much longer for some custom databases.

I am particularly interested in comparing statistics on numerous databases I interact with, to show quickly and clearly how many custom objects (tables, reports, forms, etc.) and how many records there are for each custom table.

Any method will do, whether it is something built in, or a reference to obtaining the information using the object model.

Answer 1

I found this https://support.office.com/en-us/article/Export-data-to-Excel-438F9F87-BF96-4BD1-BE0B-7CE4045FF194 which gives what I need on an object by object basis.

I just need to figure out how to generate this using code inside the application, then I can automate it to generate for all of the custom objects in the database. If anyone sees this and knows of good examples of how to do this, please share.

Answer 2

Hi dcm_ebb,

you want metadata of an Access Database.

but here I want to tell you that it contains lots of information. you have to do lots of codding.

if Database Documenter is not suitable for you then you can try different third party tools and software are available on the internet to get the metadata of Access Database.

if you want some example of code then please see the query below. it will give you details regarding Form, Reports, Macros, modues etc.

SELECT IIf([MSysObjects].[Type]=1,'TABLE',
 IIf([MSysObjects].[Type]=5,'QUERY',
 IIf([MSysObjects].[Type]=-32768,'FORM',
 IIf([MSysObjects].[Type]=-32764,'REPORT',
 IIf([MSysObjects].[Type]=-32766,'MACRO',
 IIf([MSysObjects].[Type]=-32761,'MODULE',
 IIf([MSysObjects].[Type]=-32756,'PAGE',
 IIf([MSysObjects].[Type]=8,'RELATIONSHIPS',
 IIf([MSysObjects].[Type]=6,'TABLE LINKED','Unknown'))))))))) AS ObjectType,
 MSysObjects.Name, MSysObjects.Type
 FROM MSysObjects
 WHERE (((MSysObjects.Name) Not Like "~*"
 And (MSysObjects.Name) Not Like "Msys*")
 AND ((MSysObjects.Type) Not In (2,3)))
 ORDER BY IIf([MSysObjects].[Type]=1,'TABLE',
 IIf([MSysObjects].[Type]=5,'QUERY',
 IIf([MSysObjects].[Type]=-32768,'FORM',
 IIf([MSysObjects].[Type]=-32764,'REPORT',
 IIf([MSysObjects].[Type]=-32766,'MACRO',
 IIf([MSysObjects].[Type]=-32761,'MODULE',
 IIf([MSysObjects].[Type]=-32756,'PAGE',
 IIf([MSysObjects].[Type]=8,'RELATIONSHIPS',
 IIf([MSysObjects].[Type]=6,'TABLE LINKED','Unknown'))))))))),
 MSysObjects.Name;

First you need to execute this query with the help of VBA and then you can try to export the data to Excel .Csv File.

As you already know how you can export your data to Excel .CSV File. here I did not mentioned that .

you can also try to run this this query using user interface.

Hope this helps you to get clear idea regarding your issue.

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.