Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Question Unanswered: MS Access Data Dictionary

    I need to connect to a MS Access database and get a list of all available tables. In Oracle I'd do a "SELECT * FROM TABS" and in SQL Server a "SELECT * FROM Sys.Tables". How would I do this in Access?

    Any and all clues appreciated.

    Ed.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:
    Code:
    SELECT Name FROM MSysObjects WHERE Type = 1 OR Type = 4
    Type = 4 is for attached tables, ie. links to external tables.

    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    Excellent, thank you.

    Unfortunately, this only runs me into a brick wall. In order to select tables in Access, I apparently need to grant permissions to that table (and others) in the database. I can only do this in 2003, or in 2007 that does not use any enhancements and has been saved back into 2003. A lot of hoops to jump through just to get the datadictionary.

    I know in ADO.NET I can GetSchema using an OleDbConnection and it will return what I need, but can't use .NET. I'm using C++ and either ATL or ODBC. Does anyone have any ideas on how to get all the available tables without all the hoops?

    Once again, any and all clues greatly appreciated.

    Ed.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You don't need .NET to use ADO, it also exists as a more "traditional COM object. If you're using C++ the easiest way would probably access the database via RDO. See:
    Using ADO Databinding in Visual C++

    However if the security mechanisms have been activated in Access, you'll have to include a userid and a password in the connection string. See:
    ODBC DSN-Less

    Have a nice day!

  5. #5
    Join Date
    Mar 2009
    Location
    SLOVAKIA,Bratislava
    Posts
    70
    Try this
    Code:
        If ll_make_CAT Then
            CAT0.Tables.Refresh
            For It = 0 To CAT0.Tables.Count - 1
                lc_TT_TYPE = UCase$(CAT0.Tables(It).Type)
    lc_TT_NAME = Trim$(UCase$(CAT0.Tables(It).Name))
                GoSub F_CHECK_TT
            Next It
        Else
             Set RS0 = CNN0.OpenSchema(adSchemaTables)
            Do Until RS0.EOF
                lc_TT_TYPE = UCase$(RS0!TABLE_TYPE)
    lc_TT_NAME = Trim$(UCase$(RS0!TABLE_NAME))
                GoSub F_CHECK_TT
                RS0.MoveNext
            Loop
            RS0.Close
        End If
    ....
    F_CHECK_TT:
    If (Len(lc_TT_NAME) > 0) Then
            llAdd = False
    bISlinked = False
            Select Case lc_TT_TYPE
                Case "TABLE"
                    llAdd = True
                Case "LINK"
                    bISlinked = True
                Case "PASS-THROUGH"
                    bISlinked = True
            End Select
            If bLinkTablesToo And bISlinked Then
                llAdd = True
            End If
    ....
    return
    15 years db-programmer(dBase,FoxPro,MS Access 2002/2003),Symbian C++

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Or this:

    Code:
    Function GetSchemaADO()
    
        Dim cnn As ADODB.Connection
        Dim rstTableSchema As ADODB.Recordset
        Dim rstColumnsSchema As ADODB.Recordset
    
        Set cnn = New ADODB.Connection
        With cnn
            .Provider = "MSDASQL"
            .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\cf_sit.mdb;"
            Set rstTableSchema = .OpenSchema(adSchemaTables)
            Do Until rstTableSchema.EOF
                Set rstColumnsSchema = .OpenSchema(adSchemaColumns, Array(Empty, Empty, "" & rstTableSchema("TABLE_NAME")))
                With rstColumnsSchema
                    Do Until .EOF
                        Debug.Print .Fields("TABLE_NAME"),
                        Debug.Print .Fields("COLUMN_NAME"),
                        Debug.Print .Fields("COLUMN_DEFAULT"),
                        Debug.Print .Fields("COLUMN_NAME"),
                        Debug.Print .Fields("DATA_TYPE"),
                        Debug.Print .Fields("CHARACTER_MAXIMUM_LENGTH"),
                        Debug.Print .Fields("NUMERIC_PRECISION"),
                        Debug.Print .Fields("DESCRIPTION")
                        .MoveNext
                    Loop
                    .Close
                End With
                rstTableSchema.MoveNext
            Loop
            rstTableSchema.Close
            .Close
        End With
        Set rstColumnsSchema = Nothing
        Set rstTableSchema = Nothing
        Set cnn = Nothing
        
    End Function
    Have a nice day!

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Have you tried cycling through the TableDef object?
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •