How to get the database structure?

[pbensky]

I am setting up a facility to extract data from Access into another database (4th Dimension) via ODBC. I need to be able to find out information about the database structure via SQL queries - the table and field names. But I've looked through the JetSql online docs and there does not appear to be a way to get this informaiton. Please tell me I've missed something! It IS possible, isn't it?

TIA

Pat

 

[mp9]

For table names query MSysObejcts e.g.

SELECT [Name] FROM MSysObjects WHERE Type=1;

For more on this:

http://www22.brinkster.com/accessory/queries/003.shtml

 

[pbensky]

Thanks for your reply. Unfortunately I can't seem to get it to work! The result I get is three rows containing the following data:

MSysObjects
MSysACEs
MSysQueries

Here is my SQL statement:

SELECT [Name] FROM MSysObjects WHERE Type =1

which seems to be exactly as you suggested.

I also tried:

SELECT Name FROM MSysObjects WHERE Type =1

Same result.

In my test database there is only one table so it shoud return one row containing that table name (products), right?

Any ideas what I might be doing wrong?

Thanks for your help

Pat

 

[PHV]

And what about this ?
SELECT * FROM MSysObjects
WHERE Type In (1,4,6) AND Name Like 'MSys*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mp9]

Is your table linked? If so, then the Type value to check for is 6, as per the link my first post, and as per PHV's suggestion. (PHV - what is type 4, out of interest?)

If your not happy with the results you're getting from this query, and your table is definitely called "Products", try SELECT Type FROM MSysObjects WHERE [Name]="Products" and see what you get.

 

[PHV]

OOps, sorry for the typo :~/
SELECT * FROM MSysObjects
WHERE Type In (1,4,6) AND Name [!]Not [/!]Like 'MSys*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pbensky]

This:

SELECT * FROM MSysObjects
WHERE Type In (1,4,6) AND Name Not Like 'MSys*'

gets me one row containing "251658241"

No, it isn't a linked table.

SELECT Type FROM MSysObjects WHERE [Name]="Products" gets me nothing (no rows in the result).

Any more suggestions?
Thanks

 

[mp9]

Is there actually a table called Products in your db?

What does SELECT COUNT(*) FROM Products return?

 

[dhookom]

The recommendations from others should get you the table names. However, you asked for "database structure via SQL queries - the table and field names". You can't get this with a query. You would need code like contained in the Documenter.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]