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?

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)


"dc" wrote:

> 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?

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/...UBED1=ACCESS-L

"dc" <(E-Mail Removed)> wrote in message
news:E3BDB648-EC59-4620-8EA9-(E-Mail Removed)...
> 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?

Sorry in my haste to clean up the query, I missed a paren:

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

--
--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/...UBED1=ACCESS-L

"Roger Carlson" <NO-Rog3erc-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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/...UBED1=ACCESS-L
>
> "dc" <(E-Mail Removed)> wrote in message
> news:E3BDB648-EC59-4620-8EA9-(E-Mail Removed)...
> > 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?
>
>

ee
dc <(E-Mail Removed)> wrote in message
news:E3BDB648-EC59-4620-8EA9-(E-Mail Removed)...
> 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?