Solved

List all tables in an Access database

Posted on 2008-10-09
8
2,286 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

770 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