Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

List all tables in an Access database

Posted on 2008-10-09
8
Medium Priority
?
2,302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
8 Comments
 
LVL 9

Accepted Solution

by:
jamesgu earned 500 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
Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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