Subscribe Bookmark RSS Feed

Getting a list of tables from Access Database

cyrille_wagner

Community Trekker

Joined:

Sep 17, 2014

Hello,

I'm trying to create a userform in the application builder, using native functionnalities and why not, JSL. I have the ideas and now I need to go on...

Firstable, does anyone know how I can get a list of all the tables in my Access Database? Of course that list would be on the userform.

Thank you for your support, I'm starting manipulate JMP 11.

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Most databases have system views that you can write SQL queries against to get lists of all the tables in the database that match a WHERE clause, etc.  I don't have MS Access, but doing some Google searches, something like this may work for you:

SELECT MSysObjects.Name FROM MSysObjects WHERE (MSysObjects.Type = 1);

You might also try "WHERE (MSysObjects.Type = 6)" as the WHERE clause.  So you could use the JSL Open Database function to submit that query to MS Access, which would give you a JMP data table with the Access table names in it, which you could then iterate through using For Each Row.

Here are links to where I found the MS Access information:

ms access - sql statement to return all fields from all tables - Stack Overflow

Using MsysObjects

HTH,

Eric

4 REPLIES
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

http://www.jmp.com/software/whitepapers/pdfs/102427_jmp_odbc.pdf

I found this online and it offers step by step instructions for making a connection between a database and JMP and you can take it from there.

Regards

Uday

Best
Uday
cyrille_wagner

Community Trekker

Joined:

Sep 17, 2014

Thank you for your help.

This is a help file which allow you to connect to a database. I would like to create myself a list of all the table names available in my database.

something like:

x=list()

for each tabledef in my_database

x1<<get tabledef.name;

insert into (x, x1);

finally, it's a bit complicate. I would like to automatically create a SQL statement with what is stored in a JMP table. Almost done, I'm sure.... But the step before, I would like to allow users to easily store informations in that JMP table. He has to select in two lists, first the table in the database, then the caracteristic interresting him in this table. Then by clicking "OK" his choice is stored in the JMP table. I can compil then a SQL Statement to get datas from database for all the choices stored in that JMP table.

Hope I am clear... ambitious?

Solution

Most databases have system views that you can write SQL queries against to get lists of all the tables in the database that match a WHERE clause, etc.  I don't have MS Access, but doing some Google searches, something like this may work for you:

SELECT MSysObjects.Name FROM MSysObjects WHERE (MSysObjects.Type = 1);

You might also try "WHERE (MSysObjects.Type = 6)" as the WHERE clause.  So you could use the JSL Open Database function to submit that query to MS Access, which would give you a JMP data table with the Access table names in it, which you could then iterate through using For Each Row.

Here are links to where I found the MS Access information:

ms access - sql statement to return all fields from all tables - Stack Overflow

Using MsysObjects

HTH,

Eric

cyrille_wagner

Community Trekker

Joined:

Sep 17, 2014

Thank you Eric,

just to be very clear and more complete, I then used this one:

"SELECT MSysNameMap.Name FROM MSysNameMap WHERE (MSysNameMap.Type = 1)"

and then:

for each row (List1 << append(:Name))

--> works perfect!

Thank you again.