Looping Through All Table Names in a DB

[SQLJoe]

I need to automatically load the contents of a number of tables into a single table. I'd like to set up a loop that iterates through a list of all the table names, tests each name to see if it follows a certain naming convention, and if it does, append the contents into another table. I can do all this if I can just get a list of all the tables loaded into an array or recordset. How can I do this?

 

[PWise]

open this recordset

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type) In (1,4)));
'1=table 4= linked table

 

[PHV]

I'd add 6:
SELECT Name FROM MSysObjects WHERE Type In (1,4,6)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SQLJoe]

This is good information, but I'm running this in a project, so I can't run a query like this because it tries to run it against sql server. What would be the code equivalent? Thanks!

 

[dhookom]

You could try something like:

SELECT *
FROM sys.Tables

Duane
Hook'D on Access
MS Access MVP

 

[SQLJoe]

Oops, please isregard my former comment. With all your help, I got it now. Thanks!