How do you print out details of the file structure of a database?

[Guest]

I would like to be able to print out all the details of the database
structure. For example:
Name of Table
Name of Field
Data type
Length of Field
Validation check
etc.

 

[BruceM]

Tools > Analyze > Documenter. Be sure to click the Options buttons and look
at the choices there. I think it will do everything you need.

 

[Duane Hookom]

Once you have done this, you can get better reports using information found
at http://www.access.hookom.net/Documenter/.

 

[onedaywhen]

Tools > Analyze > Documenter.
I think it will do everything you need.

I tried this on a table with seven CHECK constraints, comprising three
column-level, one row-level and three table-level, and it only
identified one (a column-level one)

Jamie.

--

 

[BruceM]

Do you have a suggestion about what to do instead?

 

[onedaywhen]

Do you have a suggestion about what to do instead?

If you are asking me how to get a *full* list of CHECK constraints then
sorry, I don't have any code I can share but I can give you some hints.
Using the ADODB OpenSchema method, first use adSchemaTableConstraints
with the table name then filter the resulting recordset for
CONSTRAINT_TYPE = 'CHECK'; however, this only gets you the CHECK name
and definition. Use adSchemaCheckConstraints and the resulting
recordset for CONSTRAINT_NAME to get the table against which the CHECK
was defined (and column name if that matters to you, however for Jet
there seems to be no way of defining a column-level CHECK). Remember
that Jet, unlike other engines (including SQL Server), does allow
multiple tables to be referenced in a CHECK constraints, but only
changes to the table against which it was defined will cause the CHECK
to be invoked. There are a few of gotchas: for Access-created
Validation Rules it is not a simple 1:1 mapping between CONSTRAINT_NAME
values in each recordset: adSchemaTableConstraints
[<table>].[<column>].ValidationRule maps to adSchemaCheckConstraints
[<column>].ValidationRule; also, the CONSTRAINT_NAME in the
adSchemaTableConstraints rowset is sometimes terminated with a Chr$(0),
sometimes not.

If you are asking me how to evaluate software, you draw up a list of
requirements (as the OP has done) and see if they can be satisfied by
trialling the software (as I have done). I would expect that as a
prospect you have a better chance of influencing a provider other than
Microsoft to extend their existing functionality to match your
requirements.

Perhaps someone can post here if they know of a documenter that
includes all CHECK constraints? I cannot.

Jamie.

--

 

[BruceM]

I don't know what a CHECK Constraint is; all I know is that the method I
described has worked for me. Duane's link seems to provide some additonal
functionality, but I have not tested it.

I am not asking how to evaluate software, nor was the OP, from what I could
tell. The question was about producing details that I believe can be
produced through the Documenter. I doubt that any product is going to meet
all of my needs, and I am sure that in most cases I have little ability to
influence the provider to customize their product. I do wish MS was more
responsive to suggestions, but I wish the same thing of every provider.

Do you have a suggestion about what to do instead?

If you are asking me how to get a *full* list of CHECK constraints then
sorry, I don't have any code I can share but I can give you some hints.
Using the ADODB OpenSchema method, first use adSchemaTableConstraints
with the table name then filter the resulting recordset for
CONSTRAINT_TYPE = 'CHECK'; however, this only gets you the CHECK name
and definition. Use adSchemaCheckConstraints and the resulting
recordset for CONSTRAINT_NAME to get the table against which the CHECK
was defined (and column name if that matters to you, however for Jet
there seems to be no way of defining a column-level CHECK). Remember
that Jet, unlike other engines (including SQL Server), does allow
multiple tables to be referenced in a CHECK constraints, but only
changes to the table against which it was defined will cause the CHECK
to be invoked. There are a few of gotchas: for Access-created
Validation Rules it is not a simple 1:1 mapping between CONSTRAINT_NAME
values in each recordset: adSchemaTableConstraints
[<table>].[<column>].ValidationRule maps to adSchemaCheckConstraints
[<column>].ValidationRule; also, the CONSTRAINT_NAME in the
adSchemaTableConstraints rowset is sometimes terminated with a Chr$(0),
sometimes not.

If you are asking me how to evaluate software, you draw up a list of
requirements (as the OP has done) and see if they can be satisfied by
trialling the software (as I have done). I would expect that as a
prospect you have a better chance of influencing a provider other than
Microsoft to extend their existing functionality to match your
requirements.

Perhaps someone can post here if they know of a documenter that
includes all CHECK constraints? I cannot.

Jamie.

 

[onedaywhen]

I don't know what a CHECK Constraint is

You should CHECK out the Jet 4.0 'new' (circa 1999) features:

http://support.microsoft.com/default.aspx?scid=kb;en-us;275561

"One new feature added to the Jet CREATE TABLE syntax is Check
Constraints. This new SQL grammar allows the user to specify business
rules that can span more than one table..."

Just don't get too excited about the supposed ON UPDATE SET NULL syntax

all I know is that the method I
described has worked for me.

The OP mentioned 'Validation check' and I thought I should mention that
the documenter seems to have a (erroneous) hard-coded assumption that a
table will have at most one CHECK constraint.

Jamie.

--