Pulling ERP Tables/Fields into Access to create a "dictionary"

[EdAROC]

Most of my job is to create reports for managers and others, retrieving the data from our corporate database (Microsoft SQL).

I can open SQL Server Enterprise Manager and look at all the tables (and fields).

What I'd like to do is create a report that lists each table and the fields for each table, including the field type and precision/scaling.

Of course, I don't want to have to manually link each table. Some automated process would be terrific. (This way when the ERP vendors change the database (add/remove tables) I don't have to go through and update my links. I'm guessing something to the effect of beginning by using the Export feature in SQL Server Enterprise Manager (creates text file of the tables) and then, in Access, having a module/procedure - series of steps - to read the file, create the links, suck in the tables and fields (build a table in Access?) then generate the report.
Phew!

 

[dhookom]

Try create a Pass-Through query with SQL of

SP_columns '%'

This retrieves 19 columns of information about your table structures

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]