Query to list table names? 2

[estafford]

Is it possible to return a list of tables from an access 2000 database using a SQL statement?

Thanks
ES

 

[gol4]

That info is covered in this faq

faq181-690

 

[Rolliee]

star form rollie - gol4

 

[fabry]

Can you write me the correct address of the FAQ about listing all the tables of an Access db with a SQL query? The page you linked doesn't exist.
Thanks
Fabrizio

 

[MichaelRed]

fabry,

It should work. MSysObjects is a (Hidden) system table which includes the object (including table) names. It is a 'documented' object, which impllies that it has been, is, and will continue to be in Ms. A for a while.

To get to the faq, just click on the link,

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[estafford]

I wasn't able to create the list of tables using the MSysObjects system table. I kept getting "no read access privledges" errors using ASP.

But after 2 and 1/2 days of research, I learned about the adox catalog object and was able to write a javascript to list the tables exactly the way I needed.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscadoapireference.asp

Ed

 

[fabry]

can you show me how to check if a table exists on my Access db? I looked at the page you linked about ADOX Catalog Object, but I found it very difficult for me... I'm sorry.
Thanks
Fabry

 

[estafford]

the ADOX info is scattered and I had to sort through it to get what I needed. I was then able to write the script I needed from that. It's all in VB and I prefer javascript so there was a lot of translation.

This is a javascript example for you question.(server side)

function findTable(tblname){
var Catalog = Server.CreateObject("ADOX.Catalog&quot
var CatConn = Server.CreateObject("ADODB.connection&quot
CatConn.open(-your connection string.mdb-)
Catalog.activeConnection = CatConn
var tbl = Catalog.tables

for(i=0; i<tbl.count; i++){
if(tbl(i).Type == &quot;TABLE&quot; && tbl(i).Name == tblname){
tblexists = true;
break;
}
}

CatConn.close()
CatConn = null
Catalog = null
}
}

Ed