selecting table names

[srik...@gmail.com]

Is there any query to get all the table names in an mdb file. i.e
similar to select * from information_schema.tables will fetch all the
tables names in a database in SQL Server.

[Nikos Yannacopoulos]

The table you are looking for in Access is MSysObjects. This query:

SELECT Name FROM MSysObjects
WHERE Type = 1 AND NAme Not Like "MSys*"

will return all native Access (Jet) tables. You might also want to
include type 6 (linked tables). Have a look at the table (Tools >
Options, tab View, click Hidden Objects) to see all object types.

HTH,
Nikos

[srik...@gmail.com]

Thanks a lot Nikos

[Douglas J Steele]

In addition, type 4 is the tables linked using ODBC.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Nikos Yannacopoulos" <nyannacoRE...@in.gr> wrote in message
news:uQfrQgxp...@TK2MSFTNGP03.phx.gbl...

[srik...@gmail.com]

Is it possible to get the table constraints like PK, FK details. Will
"msysrelationships" table help if so what are its columns.

[Pieter Wijnen]

Yes
Ccolumn
Grbit
Icolumn
Szcolumn
Szobject
Szreferencedcolumn
Szreferencedobject
Szrelationship

Field Order
???
???
Child Column
Child Table
Parent Column
Parent Table
Relation Name

HTH

Pieter

<srik...@gmail.com> wrote in message
news:1152878385.0...@i42g2000cwa.googlegroups.com...

[Douglas J Steele]

I don't believe there's any way to query that information from the system
tables. You need to use DAO or ADOX.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<srik...@gmail.com> wrote in message
news:1152878385.0...@i42g2000cwa.googlegroups.com...

[Pieter Wijnen]

Yes
Ccolumn
Grbit
Icolumn
Szcolumn
Szobject
Szreferencedcolumn
Szreferencedobject
Szrelationship

Field Order
???
???
Child Column
Child Table
Parent Column
Parent Table
Relation Name

HTH

Pieter

<srik...@gmail.com> wrote in message
news:1152878385.0...@i42g2000cwa.googlegroups.com...

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4231 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

[Jamie Collins]

Douglas J Steele wrote:
> > Is it possible to get the table constraints like PK, FK details.
>

> I don't believe there's any way to query that information from the system
> tables. You need to use DAO or ADOX.

ADODB may be more appropriate, considering the OP is asking about
querying system tables, because the ADODB.Connection object's
OpenSchema method fetches a *recordset* of schema information:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthopenschema.asp

This is an alternative approach to traversing and object model's
collections with DAO/ADOX. In fact, it is my understanding that ADOX
uses OpenSchema under the covers anyhow:

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

Also a recordset can be more flexible than a Collection e.g. you can
Filter, Sort, GetString, GetRows, etc.

Jamie.

--

[Douglas J Steele]

"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1152884042....@m73g2000cwd.googlegroups.com...

Yeah, you're right. I have that in my "stock answers" at home, but I'm not
at home right now...

[srik...@gmail.com]

Thanks a lot for all your suggestions. As am reading via java,
selecting
MSysRelationships helped me in identifying the relationships. However
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. I can get the column name, its data type, however the cloumn
constraints are essential to generate the CREATE TABLE script. Any
pointers will be greatfully received.

[Jamie Collins]

srik...@gmail.com wrote:
> 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.

--

[srik...@gmail.com]

Thank Jamie for the info. Is there any third party tool which java
based