Documenter into CSV or Excel

[Bill]

Hi All,

Does anyone know of a sort of database documenter utility that will dump all
the names of my tables and queries etc into something like Excel. I'm not
looking for properties just names. The database documenter that is contained
within Access is not suitable because of the way the report is formatted.

Regards.
Bill.

 

[Douglas J. Steele]

If all you want is a list of table and query names, you can create a query
based on the (normally hidden) MSysObjects table and export the query to
Excel.

SELECT [Type], [Name]
FROM MSysObjects
WHERE [Type] In (1,4,5,6)
AND Left([Name], 4) <> "MSys"
ORDER BY [Type], [Name]

Type 1 is Access tables in the current database, type 4 is tables linked
using ODBC, type 5 is queries, and type 6 are other linked tables.

 

[Bill]

Many thanks Douglas, that will save me loads of time.

As ever a spin-off question!

Is there a way of getting the name of the database file (and path if
possible) into the listing. I notice that the table contains the name of any
linked databases but cannot spot the its own name.

Regards.
Bill.

If all you want is a list of table and query names, you can create a query
based on the (normally hidden) MSysObjects table and export the query to
Excel.

SELECT [Type], [Name]
FROM MSysObjects
WHERE [Type] In (1,4,5,6)
AND Left([Name], 4) <> "MSys"
ORDER BY [Type], [Name]

Type 1 is Access tables in the current database, type 4 is tables linked
using ODBC, type 5 is queries, and type 6 are other linked tables.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi All,

Does anyone know of a sort of database documenter utility that will dump
all the names of my tables and queries etc into something like Excel. I'm
not looking for properties just names. The database documenter that is
contained within Access is not suitable because of the way the report is
formatted.

Regards.
Bill.

 

[Douglas J. Steele]

Write a small function that returns the database name, and include that
function in your query.

For example, I wrote:

Function DatabaseName() As String
DatabaseName = CurrentDb.Name
End Function

and then used

SELECT [Type], [Name], DatabaseName() As WhichDatabase
FROM MSysObjects
WHERE [Type] In (1,4,5,6)
AND Left([Name], 4) <> "MSys"
ORDER BY [Type], [Name]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Many thanks Douglas, that will save me loads of time.

As ever a spin-off question!

Is there a way of getting the name of the database file (and path if
possible) into the listing. I notice that the table contains the name of
any linked databases but cannot spot the its own name.

Regards.
Bill.

If all you want is a list of table and query names, you can create a
query based on the (normally hidden) MSysObjects table and export the
query to Excel.

SELECT [Type], [Name]
FROM MSysObjects
WHERE [Type] In (1,4,5,6)
AND Left([Name], 4) <> "MSys"
ORDER BY [Type], [Name]

Type 1 is Access tables in the current database, type 4 is tables linked
using ODBC, type 5 is queries, and type 6 are other linked tables.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi All,

Does anyone know of a sort of database documenter utility that will dump
all the names of my tables and queries etc into something like Excel.
I'm not looking for properties just names. The database documenter that
is contained within Access is not suitable because of the way the report
is formatted.

Regards.
Bill.