Table name through query

[navin]

Hi All,

I wanted to know if its possible to find the names of all the tables
in a database. i remember in Oracle, query 'Select * from desc;"
returns all the table name which have been created.

Is it possible in access too?

Thanks,
Navin

 

[Graham R Seach]

Navin,

Try this:

SELECT [Name], [Type]
FROM MSysObjects WHERE ([Type] = 1 OR [Type] = 4)
AND (Left([Name],4) <> "MSys" AND Left([Name],1) <> "~")
ORDER BY [Name]

Those tables with Type = 4 are linked tables.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Douglas J. Steele]

Actually, I think the linked tables are [Type] = 6. [Type] = 4 are
"connected" tables (those linked through ODBC)

That means you might want to use:

SELECT [Name], [Type]
FROM MSysObjects WHERE ([Type] IN (1, 4, 6))
AND (Left([Name],4) <> "MSys" AND Left([Name],1) <> "~")
ORDER BY [Name]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Navin,

Try this:

SELECT [Name], [Type]
FROM MSysObjects WHERE ([Type] = 1 OR [Type] = 4)
AND (Left([Name],4) <> "MSys" AND Left([Name],1) <> "~")
ORDER BY [Name]

Those tables with Type = 4 are linked tables.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hi All,

I wanted to know if its possible to find the names of all the tables
in a database. i remember in Oracle, query 'Select * from desc;"
returns all the table name which have been created.

Is it possible in access too?

Thanks,
Navin

 

[navin]

Thanks,

This is what i wanted. However, my requirement is to use return the
values given by this query into an excel sheet. is that possible.

i am connecting to my database from an excel sheet.

Thank you for all your help.

Navin

Navin,

Try this:

SELECT [Name], [Type]
FROM MSysObjects WHERE ([Type] = 1 OR [Type] = 4)
AND (Left([Name],4) <> "MSys" AND Left([Name],1) <> "~")
ORDER BY [Name]

Those tables with Type = 4 are linked tables.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hi All,

I wanted to know if its possible to find the names of all the tables
in a database. i remember in Oracle, query 'Select * from desc;"
returns all the table name which have been created.

Is it possible in access too?

Thanks,
Navin- Hide quoted text -

- Show quoted text -

 

[Guest]

How are you connecting from Excel? You could use MS Query and use the query
which Douglas and Graham suggested.

If you are getting there through VBA you can also use the select statement
as mentioned before.

Maurice

Thanks,

This is what i wanted. However, my requirement is to use return the
values given by this query into an excel sheet. is that possible.

i am connecting to my database from an excel sheet.

Thank you for all your help.

Navin

Navin,

Try this:

SELECT [Name], [Type]
FROM MSysObjects WHERE ([Type] = 1 OR [Type] = 4)
AND (Left([Name],4) <> "MSys" AND Left([Name],1) <> "~")
ORDER BY [Name]

Those tables with Type = 4 are linked tables.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hi All,

I wanted to know if its possible to find the names of all the tables
in a database. i remember in Oracle, query 'Select * from desc;"
returns all the table name which have been created.

Is it possible in access too?

Thanks,
Navin- Hide quoted text -

- Show quoted text -