Solved

List all tables in an Access database

Posted on 2008-10-09
8
2,288 Views
Last Modified: 2013-11-29
Is there a way I can list all the tables in an Access database using a SQL query?
SImilar to the SQL Server:
select TABLE_NAME from INFORMATION_SCHEMA.TABLES

But something that will work on Access
0
Comment
Question by:MattWilkinson
  • 3
  • 2
8 Comments
 
LVL 9

Accepted Solution

by:
jamesgu earned 125 total points
ID: 22680344
SELECT *
FROM MsysObjects
where type = 1
0
 

Author Comment

by:MattWilkinson
ID: 22680416
I get the following error when I run that query

Record(s) cannot be read; no read permission on 'MsysObjects'.
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22680460
you may have to login as an admin or granted read permission on system tables
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 125 total points
ID: 22680476
select [name] from msysobjects
where [name] not like 'msys*' and type=1
0
 

Author Comment

by:MattWilkinson
ID: 22680500
How do I login as admin on an access database?

I am local admin on the machine (db file is on c:\ drive) with no password set
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22686904
in access, go to tools\security\user and group permissions

check what is the current user you're using
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question