Get Tables & column names--Access??

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.

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

Like:

'Declarations
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)
    Next

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)
    Next
   
'    For Each axKey In axTab.Keys
'        Call WriteKey(axKey)
'    Next
   
    axortTable = True
    Exit Sub
   
Error_Handler:
   
    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
    '
Error_Handler:
    '
    Call MsgBox(Err.Number & vbCrLf & Err.Description, vbInformation, "WriteColumn")
    '
End Sub

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
    Me.Repaint
        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
                    Else
                        ListString = DBSusing.TableDefs(Tbl.Name).Name
                    End If
                    Me.Repaint
                    DoEvents
                End If
            End If
            End If
        Next
    'finaly set equal to string
    Me.Flag.RowSourceType = "value list"
    Me.Flag.RowSource = ListString
    Me.SetFocus
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
    Me.Repaint
        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
                    Else
                        ListString = Tbl.Fields(Fld.Name).Name
                    End If
                    Me.Repaint
                    DoEvents
        Next
    '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
    Me.SetFocus
End Sub