I need to retreive a list of tables from an access database. I want to
do so via ADO using a SQL query.

For example, I have an access database with tables: cat, dog, chris,
bob. I want to perform an SQL query on this database that will return
these values: cat, dog, chris, bob.

I have tried using

SELECT * FROM MSysObjects
WHERE Type = 1
AND Name Not Like "MSys*"

but this does not work because read permission is denied.

How can this problem be solved?

---

Request for Question Clarification by hammer-ga on 27 May 2003 12:52 PDT

Have you tried the techniques in this article?

MSDN - Listing the Tables in an Access Database
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrlistingtablesinaccessdatabase.asp

Please let me know if this solves your problem.

- Hammer

---

Clarification of Question by chris572-ga on 02 Jun 2003 19:22 PDT

the second one works, thanks for your help.

---

Clarification of Question by chris572-ga on 02 Jun 2003 19:23 PDT

I dont see a button for accepting this 2nd post as the answer...

I'm glad the information I provided solved your problem. Here is the
link again.

MSDN - Listing the Tables in an Access Database 
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrlistingtablesinaccessdatabase.asp


Search Strategy:
site:msdn.microsoft.com listing tables Access

- Hammer

This is what you need to do:

1. Open your Access db
2. Go to Tools->Options and check the boxes to show hidden and system objects.
3. Go to Tools->Security->User & Group Permissions
4. Pick MSysObjects from the list
5. Check "Read Data" permission

That should do it.


This is my test code, run from an ASP page:
----
<%

Set dbh = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.Recordset")

dbh.Open "DSN=AccessTest"

sql = "SELECT * FROM MSysObjects WHERE Type = 1"
rst.Open sql, dbh

do until rst.eof
	response.write rst("Name") & "<BR>"
	rst.movenext
loop

%>


----

~ Joel