How can I get a listing of all tables in Access

D

dc

I am trying to list all the table names in a Access 2002 database and
subsequently run a query against a subset of the tables. Is there a way to
list the tables to file?
 
K

Kim

This will give you a recordset of the tables

strSql = "Select Name from MSysObjects "
strSql = strSql & "WHERE (((IIf(Left([Name],4)='Msys',0,1))=1) AND
((Type)=1));"

Set rs = CurrentDB.OpenRecordset(strSql)
 
R

Roger Carlson

You can get a listing of tables with a simple query:

SELECT Name
FROM msysobjects
WHERE Name Not Like "Msys*" AND Type=1 Or Type)=6;

MSysObjects is a hidden table in Access that keeps track of all the objects
in the Database Window (tables, forms, reports, etc). Type 1 are native
tables. Type 6 are linked tables. The SQL above also assumes you don't
want to see the other "system" tables in your list, so excludes anything
starting with MSys.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Top