Need list of TABLE name.

[FastEdge]

Hello, I need to great a query that will return a list of all table names in a DB. I appreciate the help.

Ed.

 

[smedvid]

This is the recommended approach I use...
Dim db As Database
Dim tdf As TableDef
Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
Debug.Print tdf.Name
'Or store in a table for later use.
Next tdf
Set db = Nothing


Or you could view the system table "MSysObjects". You will need to use Tools->Options->View Tab->System Objects. Look for Type Code 6, I think.

htwh,




Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[FastEdge]

Thanks for the reply, but I don't think either suggestion is what I'm looking for. I could be wrong. Let me elaborate.

I'm developing in Cold Fusion. To get data from the DB I use Jet SQL within a CF query tag. The CF becomes almost irrelevant at this point. Normally it would look something like this.

<cfquery datasource="DB" name="DB_REC">
Select * from TABLE
Where Blah = Blah
</cfquery>

Now, within these parameters, how do I grab a list of table names? Any ideas?

Thanks again,
Ed

 

[smedvid]

u can try..

SELECT Name from MSysObjects WHERE TYPE=6;

Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[FastEdge]

Wow, would have worked except for permissions. So I guess the problem now is how do I change permissions on a DB that I can access via SQL only? It's a live database and replacing it would inevitably delete some data. So changes have to be made via SQL.

Thanks,
Ed