Link to home
Start Free TrialLog in
Avatar of sviksna
sviksna

asked on

Geting list of table names

Hello, Experts!

I have a qwestion.
I have a main.mdb on server with some tables, but i don't know... exactly what tables. So i would like to know, is there any way, how to get list of them from the data base file.

thank you.
Avatar of clockwatcher
clockwatcher

You can retrieve the table names from an access database with the following query against the msysobjects system table.

SELECT NAME FROM MSYSOBJECTS WHERE TYPE=1 AND NAME NOT LIKE 'MSYS%'

I'm pretty sure you have to make sure you set some options in the .mdb before you can see the MSYSOBJECTS table under User and Groups permissions.
Nope.  You can query and return results against it whether you can actually see the table listed in your Access database or not.  

If you really want to see the system tables listed with all the user-defined tables in the database window for some reason-- I can't really think of a good one besides general interest-- click Tools, Options, View, Show System Tables.  Again, it's not necessary to do the above to run a query against it.

The ASP code would look like this:

<%
set rs = server.createobject("ADODB.Recordset")

rs.open "SELECT NAME FROM MSYSOBJECTS WHERE TYPE=1 AND NAME NOT LIKE 'MSYS%'", myConn

do while not rs.eof
  response.write rs("NAME") & "<BR>"
  rs.movenext
loop

rs.close
set rs = nothing
%>
Take a look at this, It has it all!!

HOWTO: Use the ADO OpenSchema Method in Visual Basic
http://support.microsoft.com/support/kb/articles/Q186/2/46.ASP

for example,
'open cn using cn.open connectstring

Set rs = cn.OpenSchema(adSchemaTables)
   While Not rs.EOF
      Response.Write(rs!TABLE_NAME)
      rs.MoveNext
   Wend

Avatar of sviksna

ASKER

Greetings, all.
Thank you for your answers, but
all your answers aren't based on thing i wanted. I meant, i have a database file main.mdb, so i need to get list of tables from this file. Not something mistic (i don't need even qweries, reports and etc, just tables from the first page). Hope, this time you can answer corectly and help me to solve this problem.

Yours sincirely,
Sandis Viksna
The above are the WYAS to do the task that you are trying to do!

You will have to open the database using Core ODBC, ADO, RDO, DAO, or any other interface, you WILL have to query the database to get the damn list.

(you can use ADO and the OpenSchema method!, one solution)

Or else, you will have to heck into the .mdb (which is a binary file) and get the list of tables!

you could use vb's open statement to open the file (that is if you know where exactly all the tables are listed!!!)


sviksna,
They gave U the answer !!!
clockwatcher's answer will work. It will only list the tables. Type 1 means tables, and the NOT LIKE excludes the system tables.
Avatar of sviksna

ASKER

Ok, i will try.
ASKER CERTIFIED SOLUTION
Avatar of JOK
JOK

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
oops, don't know why the .MoveFirst got moved.

Also, you would have to change RegDSN to whatever you are using.
Sorry, my answer was too mystic for you.  Yet, you accept JOK's identical answer a day later--  I guess the real key was to mention that you might want to actually try the code.  :-)
clockwatcher, you are right! She/He should accept your answer (nothing against you JOK though)
clockwatcher started the answer and had the critical part. But when I looked again at the number of points, I thought s/he deserved a bit more. Seemed like someone starting out, so...

JOK:

If s/he had simply cut and pasted the code I posted the first time into an empty file, it would have worked fine.  The problem was that sviksna didn't even bother to try.

"Thanks, but that doesn't relate to what I want to do" ???

Even if you didn't understand-- and didn't bother to try-- the system table method I posted, how could you possibly say that about the OpenSchema method posted by manojamin.  Unless, you also didn't even bother to check the accompanying link he included.

Experts-Exchange seems like it should be a two-way thing.  If you're not even going to try the solutions offered, then what am I doing here wasting my time trying to help.

Nothing against you for getting the points and sviskna is more than welcome to give them to whomever s/he wants--  Yeah, I feel slightly cheated about them, but what's a 200 point question here and there.  I'm really more upset that someone rejects an answer without even bothering to try it out.
clockwatcher,

Most of us have been there. I've felt cheated more than once too.

Forgot about the code you had posted. If I had remembered when I posted mine, would have instead just added a couple of lines to open the connection.
Avatar of sviksna

ASKER

Ok, i know, it was stupid, but sorry.
Avatar of sviksna

ASKER

So, there comes an error
dont know why:

Microsoft][ODBC Microsoft Access 97 Driver] Record(s) can't be read; no read permission on 'MSYSOBJECTS'.

what does it means?
I used code..
<%
oSource = "SELECT * FROM MSYSOBJECTS WHERE TYPE=1 AND NAME NOT LIKE 'MSYS%';"
dim DBCS

DBCS = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../news.mdb") & ";"

Set oRs = Server.CreateObject("ADODB.Recordset")

oRs.Open oSource, DBCS, adOpenDynamic, adLockPessimistic, adCmdText
Do While Not oRs.EOF
  response.write oRs.Fields("Name")
  oRs.MoveNext
Loop
oRs.Close
Set oRs = Nothing
%>

So, what the permissions happens there?
Avatar of sviksna

ASKER

i tried another way. but nothing:((
help please.
Open up the DB in Access, go to Tools>Options and the view tab. Make sure Hidden and System objects are selected.

Go to Tools>Security>User and Group Permissions.

Under the permission tab, select tables in the Object type drop down. Make sure Read Design, Read Data and Administer are checked (I think Read Data is all you really need).
Avatar of sviksna

ASKER

sorry, but it don't wana work:((

so, help me please. I am so helples in this situation? maybe i should use login/password for doing that? Or i can use default.. non login, non pass... ???
(i haven't any passwords on tables, so help me@!!)
tell me please, thnx.