Opening Schema

[Atif]

Hi

I am using MS Access 2000 and want to open the Schema to know about
Table's structures like column names, data types, primary keys etc.

In SQL Server, we use OpenSchema method of Connection object,
in Oracle, we use "desc table" query,
in MySQL, we use "describe table" query,
in MS Access, we use ???????.

I found a method in this groups to use following query
"SELECT * FROM INFORMATION_SCH EMA.TABLES" for all tables' schema
but it gives following error

"could not find file 'C:\Program Files\Microsoft Visual Studio\VB98\
INFORMATION_SCH EMA.mdb'"

Does this database file gets installed by default or require custom
configuration or this is a wrong method??

HELP

Thanks
Regards

[Steve Jorgensen]

Jet does not provide for access to a textual representation of the schema. I
don't know if it provides API access via ADOX yet (try it and find out), but
it does privovide API access via DAO, and I've used it in real world
applications before.

Look at the DAO help or on MSDN for more details. You'll see that there are
collections of Tabledefs, Querydefs, Relations, etc. You might have to look
at the Containers and Documents collections to get some of the information you
may need, but it's all there in one form or another.

On 26 Jul 2004 02:39:25 -0700, at*******@hotma il.com (Atif) wrote:

Hi

I am using MS Access 2000 and want to open the Schema to know about
Table's structures like column names, data types, primary keys etc.

In SQL Server, we use OpenSchema method of Connection object,
in Oracle, we use "desc table" query,
in MySQL, we use "describe table" query,
in MS Access, we use ???????.

I found a method in this groups to use following query
"SELECT * FROM INFORMATION_SCH EMA.TABLES" for all tables' schema
but it gives following error

"could not find file 'C:\Program Files\Microsoft Visual Studio\VB98\
INFORMATION_SC HEMA.mdb'"

Does this database file gets installed by default or require custom
configuratio n or this is a wrong method??

HELP

Thanks
Regards

[Terry Kreft]

Have you tried using the OpenSchema method of the connection object.

e.g.
Function testschema()
Dim loCon As ADODB.Connectio n
Dim loRst As ADODB.Recordset

Set loCon = Application.Cur rentProject.Con nection

Set loRst = loCon.OpenSchem a(adSchemaTable s, Array(Empty, Empty, Empty,
"TABLE"))

With loRst
Do Until .EOF
Debug.Print .Fields("TABLE_ NAME")
.MoveNext
Loop
.Close
End With
Set loRst = Nothing
loCon.Close
Set loCon = Nothing

End Function

--
Terry Kreft
MVP Microsoft Access
"Atif" <at*******@hotm ail.com> wrote in message
news:3c******** *************** ***@posting.goo gle.com...

Hi

I am using MS Access 2000 and want to open the Schema to know about
Table's structures like column names, data types, primary keys etc.

In SQL Server, we use OpenSchema method of Connection object,
in Oracle, we use "desc table" query,
in MySQL, we use "describe table" query,
in MS Access, we use ???????.

I found a method in this groups to use following query
"SELECT * FROM INFORMATION_SCH EMA.TABLES" for all tables' schema
but it gives following error

"could not find file 'C:\Program Files\Microsoft Visual Studio\VB98\
INFORMATION_SCH EMA.mdb'"

Does this database file gets installed by default or require custom
configuration or this is a wrong method??

HELP

Thanks
Regards

[Atif]

thaks for replying BUT

OpenSchema(adSc hemaTables) gives Tables' names, datatypes and other
things except PrimaryKeys

and when i use
OpenSchema(adSc hemaPrimaryKeys ) it results out with this table
"MSysAccessObje cts" and PK_NAME="AOInde x"

and when i tried to open "MSysAccessObje cts" it gives binary data(as i
think) which is not understandable.

What i require is to know what are the primary keys of a table.

Thanks
Regards