Get List of tables in Access DB?

[hautnueil]

Hi all,

Is there a way to get a list of table names in an Access database using Cold Fusion?

I would like to be able to then loop through each table and output the column names using #QueryName.ColumnList#.

And then, of course the values using #Evaluate(ThisColumn)#

BTW: Happy New Year

 

[webmigit]

Geez, that'd be massive.. I don't think one query can get you EVERYTHING from a db.. There are easy ways to do it in leveled queries.. but with a db of 5 tables it'd look something like this.

Get Table Names
Get Table Data
Get Table Data
Get Table Data
Get Table Data
Get Table Data

Without knowing the fieldnames, I think its impossible to do aliasing so fields with the same name with overlap and you wouldn't know what table they belonged to.

I _think_ show tables; will work but...

SELECT Name FROM MSysObjects
WHERE Type=1 AND Flags=0

will work according to

http://sqlzoo.net/howto/source/s.pl.htm?file=tip137084&e=access

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[hautnueil]

I'm not sure what you mean when you refer to
"show tables"

I only want to return the results of one table at a time.
And I'm passing the final query to the database via FORM.variables

My current FORM asks for
First the TableName
Once provided, it prompts to Select the Columns to display
Then optionally offers to Restrict output and 2 sorts

The first element it asks for is the NAME OF THE TABLE.
<select name="TableName">
<option value="FirstTable">FirstTable
<option value="SecondTable">SecondTable
</select>

I would like the script to be 100% dynamic. So that as I add tables to the DB, the above menu gets populated with all the current table names.
I don't know how to get the list of all the table names in that Database. I don't even know if it's possible.

<cfquery name="GetTableNames" datasource="secret">
WHAT GOES HERE????? if this is the way at all?
</cfquery>

 

[philhege]

Look at ALFII's

SELECT Name FROM MSysObjects
WHERE Type=1 AND Flags=0


This should bring back a list of the user-defined tables in the MDB. Use the results of this query for your drop-down list.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[hautnueil]

The ALFII site was a great help. Especially after I read the directions to Tools/Options/View and ticked on System Objects

Problem is that the MSysObjects table belongs to the
owner = "Engine"
(my other tables owner = "Admin")
and ColdFusion returns this error:
[Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysObjects'.

I poked and poked in Access to see if I could change the permissions/owner on that particular table... but I did not find a way.

Any suggestions.

The only workaround I could think of, was to duplicate that table... which brings me back to square one... having to keep on top of newly created tables MANUALLY.

 

[philhege]

Look at the last entry in this post; it may give you an idea:

http://groups-beta.google.com/group..._doneTitle=Back+to+Search&&d#c1c2c208b65ea396

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[hautnueil]

Thank you Phil... too bad you had to be the bearer of bad news.