topic Re: Getting a list of tables from Access Database in Discussions

Getting a list of tables from Access Database

cyrille_wagner — Thu, 18 Sep 2014 07:10:03 GMT

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.

Re: Getting a list of tables from Access Database

uday_guntupalli — Thu, 18 Sep 2014 16:24:24 GMT

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

Re: Getting a list of tables from Access Database

cyrille_wagner — Thu, 18 Sep 2014 17:32:03 GMT

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?

Re: Getting a list of tables from Access Database

Eric_Hill — Fri, 19 Sep 2014 12:17:17 GMT

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

Re: Getting a list of tables from Access Database

cyrille_wagner — Wed, 15 Oct 2014 13:28:03 GMT

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.

Re: Getting a list of tables from Access Database

jarryhead — Fri, 18 Dec 2020 05:47:23 GMT

Make a module in access and paste this code.
Run in debug window
Copy the generated table list in the immediate window.

Public Sub List_fields_in_tables()
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then
            Debug.Print
            Debug.Print
            Debug.Print tdf.Name
            Debug.Print "----------------------------------------"
    
            
            For Each fld In tdf.Fields
                Debug.Print fld.Name & Space(20 - Len(fld.Name)) & fld.Size & Space(10 - Len(Str(fld.Size))) & FDataType(fld.Type)
        
            Next fld
        End If
    Next tdf

End Sub

Private Function FDataType(intType As Integer) As String

    Dim RetString As String
    
    Select Case intType
        Case 1
            RetString = "Boolean"
        Case 8
            RetString = "Date"
        Case 3
            RetString = "Integer"
            
        Case 7
            RetString = "Double"
        
        Case 10
            RetString = "String"
            
        Case 4
            RetString = "Long Integer"
        
        Case Else
            RetString = "Unknown"
            
    End Select
    
    FDataType = RetString

End Function