By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

Opening Schema

P: n/a
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_SCHEMA.TABLES" for all tables' schema
but it gives following error

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

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

HELP

Thanks
Regards
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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*******@hotmail.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_SCHEMA.TABLES" for all tables' schema
but it gives following error

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

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

HELP

Thanks
Regards


Nov 13 '05 #2

P: n/a
Have you tried using the OpenSchema method of the connection object.

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

Set loCon = Application.CurrentProject.Connection

Set loRst = loCon.OpenSchema(adSchemaTables, 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*******@hotmail.com> wrote in message
news:3c**************************@posting.google.c om...
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_SCHEMA.TABLES" for all tables' schema
but it gives following error

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

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

HELP

Thanks
Regards

Nov 13 '05 #3

P: n/a
thaks for replying BUT

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

and when i use
OpenSchema(adSchemaPrimaryKeys) it results out with this table
"MSysAccessObjects" and PK_NAME="AOIndex"

and when i tried to open "MSysAccessObjects" 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
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.