"Ralph" <nt*************@yahoo.com> wrote in message
news:R8******************************@arkansas.net ...
"Steven Smith" <sl****@webbox.com> wrote in message
news:Tu*****************@twister.nyroc.rr.com... Thanks Karl... I would've preferred ADO or SQL, but since it's only for one query in my program, this'll work just fine!
"Karl E. Peterson" <ka**@mvps.org> wrote in news:e210ej$3im$1
@emma.aioe.org:
Steven Smith wrote:
> I have a vb6 (not .NET) program using MS Access as the backend. As
> part of an import form, I need to allow the user to select the table
> containing the data to be imported. How can I populate a combo with
> this information? Then, once this value is selected, how can I
> populate other combos with the column names of the selected table?
Iterate the TableDefs collection looking at each item's Name property
to offer a choice of tables. And, within each TableDef, you can loop the
Fields collection.
Sub ListTablesADO() Dim cnn As ADODB.Connection Set cnn = New
ADODB.Connection Dim rsTables As ADODB.Recordset Dim rsColumns As
ADODB.Recordset 'Open connection you want To get database objects
cnn.Provider = "MSDASQL" ' whatever needed... cnn.Open
"DSN=...;Database=...;", "UID", "PWD" 'Get all database tables. Set
rsTables = cnn.OpenSchema(adSchemaTables) Do While Not rsTables.EOF
'Get all table columns. Set rsColumns = cnn.OpenSchema(adSchemaColumns,
_ Array(Empty, Empty, "" & rsTables("TABLE_NAME"))) Do While Not
rsColumns.EOF Debug.Print rsTables("TABLE_NAME") & ", " & _
rsColumns("COLUMN_NAME") rsColumns.MoveNext Loop
rsTables.MoveNext LoopEnd SubSub ListTablesADOX() Dim cnn As
ADODB.Connection Set cnn = New ADODB.Connection 'Open connection you
want To get database objects cnn.Provider = "MSDASQL" ' whatever you
need... cnn.Open "DSN=...;Database=...;", "UID", "PWD" 'Create catalog
object Dim Catalog As New ADOX.Catalog Set Catalog.ActiveConnection =
cnn 'List tables And columns Dim tbl As ADOX.Table Dim col As ADOX.Column
For Each tbl In Catalog.Tables Debug.Print tbl.Name For Each col In
tbl.Columns Debug.Print col.Name Next NextEnd Sub
Sorry, don't know what happened to the paste...
Sub ListTablesADO()
Dim cnn As ADODB.Connection:
Set cnn = New ADODB.Connection
Dim rsTables As ADODB.Recordset
Dim rsColumns As ADODB.Recordset
'Open connection you want To get database objects
cnn.Provider = "MSDASQL" ' whatever needed...
cnn.Open "DSN=...;Database=...;", "UID", "PWD"
'Get all database tables. Set
rsTables = cnn.OpenSchema(adSchemaTables)
Do While Not rsTables.EOF
'Get all table columns.
Set rsColumns = cnn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, "" & rsTables("TABLE_NAME")))
Do While Not rsColumns.EOF
Debug.Print rsTables("TABLE_NAME") & ", " & _
rsColumns("COLUMN_NAME")
rsColumns.MoveNext
Loop
rsTables.MoveNext
Loop
End Sub
Sub ListTablesADOX()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
'Open connection you want To get database objects
cnn.Provider = "MSDASQL" ' whatever you need...
cnn.Open "DSN=...;Database=...;", "UID", "PWD"
'Create catalog object
Dim Catalog As New ADOX.Catalog
Set Catalog.ActiveConnection = cnn
'List tables And columns
Dim tbl As ADOX.Table
Dim col As ADOX.Column
For Each tbl In Catalog.Tables
Debug.Print tbl.Name
For Each col In tbl.Columns
Debug.Print col.Name
Next
Next
End Sub
