is it possible to get the column constaints like NULL CHECK, Precision
of the DataTYPE of the column, validation rule applied for the column
etc. Basically I need to generate CREATE TABLE script by reading the
mdb file.
The ADODB OpenSchema method can again use used for these purposes. For
the kind of schema information that is available in theory, take a look
at the SchemaEnum ADO enumeration:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstschemaenum.asp
I say 'in theory' because the OLE DB provider for Jet 4.0 does not
support all the rowsets and others are only available on a
table-by-table basis (rather than at the schema level). Some have
quirks.
As a good example, take CHECK constraints. 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
the seems to be no way of definig 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,
adSchemaTableConstraints is sometimes terminated with a Chr$(0),
sometimes not.
Hopefully this will give the impression that writing a program to
generate a CREATE TABLE script wouldn't be a minor task (my pet project
is circa 8K lines of VBA and counting). Also consider that some
Access-only properties do not show up in the schema rowsets.
Your best approach may be to purchase a third party tool which does all
this out of the box.
Jamie.
--