469,293 Members | 1,335 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,293 developers. It's quick & easy.

Query to get all the table names in a database

Hi guys!

What query should I use to retrieve a list of all the table names in an Access database? The effect would be similar to that of a simple "SHOW TABLES;" in MySql. To clear thing up, "SHOW TABLES;" does not work in Access(it says something like: "SELECT, INSERT, UPDATE or DELETE expected").

Thanks in advance.
Mar 7 '07 #1
5 18770
markmcgookin
648 Expert 512MB
Hi guys!

What query should I use to retrieve a list of all the table names in an Access database? The effect would be similar to that of a simple "SHOW TABLES;" in MySql. To clear thing up, "SHOW TABLES;" does not work in Access(it says something like: "SELECT, INSERT, UPDATE or DELETE expected").

Thanks in advance.
I don't think this is possible with an SQL query, as they need tables to be pointed to. However, I am sure there would be some simple VB code you could embedd in a form to the same outcome.
Mar 7 '07 #2
ADezii
8,800 Expert 8TB
Hi guys!

What query should I use to retrieve a list of all the table names in an Access database? The effect would be similar to that of a simple "SHOW TABLES;" in MySql. To clear thing up, "SHOW TABLES;" does not work in Access(it says something like: "SELECT, INSERT, UPDATE or DELETE expected").

Thanks in advance.
The following SQL will return all 'Non System' Tables:
Expand|Select|Wrap|Line Numbers
  1. SELECT MSysObjects.Name, MSysObjects.Type
  2. FROM MSysObjects
  3. WHERE MSysObjects.Name Not Like "MsyS*" AND MSysObjects.Type=1
  4. ORDER BY MSysObjects.Name;
Mar 7 '07 #3
markmcgookin
648 Expert 512MB
The following SQL will return all 'Non System' Tables:
Expand|Select|Wrap|Line Numbers
  1. SELECT MSysObjects.Name, MSysObjects.Type
  2. FROM MSysObjects
  3. WHERE MSysObjects.Name Not Like "MsyS*" AND MSysObjects.Type=1
  4. ORDER BY MSysObjects.Name;
Well that shut me up! Nice post ADezii
Mar 7 '07 #4
ADezii
8,800 Expert 8TB
Well that shut me up! Nice post ADezii
Thanks. It just happened to be one of those 'little things' that I remembered over the years (LOL).
Mar 7 '07 #5
Thanks a lot!
My best regards!
Mar 8 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by 'bonehead | last post: by
4 posts views Thread by Martin Lacoste | last post: by
13 posts views Thread by Maxi | last post: by
5 posts views Thread by Sam | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.