Get Tables & column names--Access??

Posted on 2003-03-06
Medium Priority
Last Modified: 2007-12-19

     I am facing two problems currently. They are:--


1. i want a query through which i can get names of all the tables from access database.

I have used the query:

Select names from MSysObjects;

The query runs fine in access SQL sheet whereas my vb application gives the problem --"It says no read permission on MSysObjects (As returned by MS-Access)"


2. i want to get the names of the columns i.e. field names of a particular table through a query.

I know Oracle uses

describe <Table Name>

but what's in the access??

Thanks in advance.
Question by:girish_its
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 18

Expert Comment

ID: 8086503
You will have to use OpenSchema method of the connection object to achieve this.

Look at msdn.microsoft.com and serach for OpenSchema method and you will get full documentation along with the SchemaEnums that can be used to get the Column attributes.

Expert Comment

ID: 8086689

ADO ActiveX Data Objects
ADOX (msadox.dll) ActiveX Ext 2.x for DDL and Security


public aCon as ADODB.Connection
public axCat as ADOX.Catalog

private sub AllTables(sFullPathNameDatabase as String)
    Dim aTbl as ADOX.Table  

    Set aCon = New ADODB.Connection
    aCon.Provider = "Microsoft.Jet.OLEDB.4.0"
    aCon.Mode = adModeReadWrite
    Call aCon.Open(sFullPathNameDatabase)
    Set axCat = New ADOX.Catalog
    Set axCat.ActiveConnection = aCon

    For Each aTable In axCat.Tables
        Call GetTable(aTable.Name)

end sub

Private Sub GetTable(TabelNm As String)
    On Error GoTo Error_Handler
    Dim axTab As ADOX.Table
    Dim axKey As ADOX.Key
    Dim axCol As ADOX.Column
    Dim axProp As ADOX.Property
    Set axTab = axCat.Tables(TabelNm)
'    For Each axProp In axTab.Properties
'        Call WriteProperty(axProp)
'    Next
    For Each axCol In axTab.Columns
        Call WriteColumn(axCol)
'    For Each axKey In axTab.Keys
'        Call WriteKey(axKey)
'    Next
    axortTable = True
    Exit Sub
    Call MsgBox(Err.Number & vbCrLf & Err.Description, vbInformation, "GetTable")
End Sub

Private Sub WriteColumn(newItem As ADOX.Column)
    On Error GoTo Error_Handler
    debug.print "COLUMN|" & newItem.Name
    On Error Resume Next
    Call Err.Clear
    debug.print "Type|" & CStr(newItem.Type)
    'If exporting from WriteKey type > error 3219 occurs, only name can be exported, skip rest
    If Err.Number = 0 Then
        debug.print "DefinedSize|" & CStr(newItem.DefinedSize)
        debug.print "NumericScale|" & CStr(newItem.NumericScale)
        debug.print "Precision|" & CStr(newItem.Precision)
        debug.print "RelatedColumn|" & CStr(newItem.RelatedColumn)
        debug.print "SortOrder|" & CStr(newItem.SortOrder)
        On Error GoTo Error_Handler
 '       debug.print "COLUMNPROPERTY"
 '       Dim colProp As ADOX.Property
 '       For Each colProp In newItem.Properties
 '           Call WriteProperty(colProp)
 '       Next
 '       debug.print "ENDCOLUMNPROPERTY"
    End If
    debug.print "ENDCOLUMN"
    WriteColumn = True
    Exit Sub
    Call MsgBox(Err.Number & vbCrLf & Err.Description, vbInformation, "WriteColumn")
End Sub

Accepted Solution

lmckenzie earned 180 total points
ID: 8087170
Here is one way using the ADO OpenSchema mentioned by Sethi.  There are probably other ways but this worked on Nwind and Biblio as a test.

Add a DataEnvironment Designer to a new project and set the connection to your database.  Alternatively, add a reference to the ADO libraries and code the connection information.  Add a listbox and a command button to the form.  This code will list the tables in the database and the fields for each table underneath.

' ADO OpenSchema
Option Explicit

Private Sub Command1_Click()

Dim rstTables As ADODB.Recordset
Dim rstFields As ADODB.Recordset
Dim cmdGetFields As ADODB.Command
Dim intCnt As Integer

On Error GoTo errProc


Set rstTables = DataEnvironment1.Connection1.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
'Fetch only Tables
Set cmdGetFields = New ADODB.Command
cmdGetFields.ActiveConnection = DataEnvironment1.Connection1



Do Until rstTables.EOF

If Left(rstTables.Fields("TABLE_NAME"), 4) <> "MSys" Then
'Filter out Access System Tables
  List1.AddItem rstTables.Fields("TABLE_NAME")
  cmdGetFields.CommandText = "Select TOP 1 * from " & "[" & rstTables.Fields("TABLE_NAME") & "]"
  Set rstFields = cmdGetFields.Execute
  For intCnt = 0 To rstFields.Fields.Count - 1
    List1.AddItem (vbTab & rstFields.Fields(intCnt).Name)

End If




Set rstTables = Nothing
Set rstFields = Nothing
Set cmdGetFields = Nothing

Exit Sub


MsgBox Err.Number & "->" & Err.Description, vbCritical, "Error From Command1_Click"

End Sub

Expert Comment

ID: 8087348
this is a module i wrote in Access using ADO 3.6 library, im sure you could do exactly the same from VB using the library just change some of the syntax, the form_load populates a list box with all the table names in the database apart from the access system tables, and once you click on one of the tables in th list box the "getfields" routine populates a drop down with the fields within the selected table.

Like i said providing you make a valid connection to the database through ADO 3.6 this code can be adapted... Have a play. If you want the database i have with the original code let me know.

Private Sub Form_Load()
    Dim Tbl As DAO.TableDef
    Dim ListString As Variant

    ListString = ""
    Set DBSusing = CurrentDb    'set database = to the open one
    Me.Visible = True
        For Each Tbl In DBSusing.TableDefs
            If Asc(Left(DBSusing.TableDefs(Tbl.Name).Name, 1)) > 65 And Asc(Left(DBSusing.TableDefs(Tbl.Name).Name, 1)) < 122 _
                Or IsNumeric(Left(DBSusing.TableDefs(Tbl.Name).Name, 1)) Then
            If Left(DBSusing.TableDefs(Tbl.Name).Name, 4) <> "MSys" Then
                If Asc(Left(DBSusing.TableDefs(Tbl.Name).Name, 1)) > 97 Or Asc(Left(DBSusing.TableDefs(Tbl.Name).Name, 1)) < 90 Then
                    'now make sure there is no space at the top on the display box i.e. no semicolon
                    'as the first element in the rowsource
                    If ListString <> "" Then
                        ListString = ListString & ";" & DBSusing.TableDefs(Tbl.Name).Name
                        ListString = DBSusing.TableDefs(Tbl.Name).Name
                    End If
                End If
            End If
            End If
    'finaly set equal to string
    Me.Flag.RowSourceType = "value list"
    Me.Flag.RowSource = ListString
End Sub

Private Sub GetFields()
    Dim Fld As DAO.Field, Tbl As DAO.TableDef
    Dim ListString As Variant
    Set DBSusing = CurrentDb
    Set Tbl = DBSusing.TableDefs(TblName)
    ListString = ""
    Me.Visible = True
        For Each Fld In Tbl.Fields
                    'now make sure there is no space at the top on the display box i.e. no semicolon
                    'as the first element in the rowsource
                    If ListString <> "" Then
                        ListString = ListString & ";" & Tbl.Fields(Fld.Name).Name
                        ListString = Tbl.Fields(Fld.Name).Name
                    End If
    'finaly set equal to string
    Me.field1 = ""
    Me.field2 = ""
    Me.field1.RowSourceType = "value list"
    Me.field1.RowSource = ListString
    Me.field2.RowSourceType = "value list"
    Me.field2.RowSource = ListString
End Sub


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question