471,092 Members | 1,890 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Get table names from MSAccess Datasource

Hello all,

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?
Thanks,

Steven Smith

Apr 17 '06 #1
4 23777
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.
--
Apr 17 '06 #2
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.


Apr 18 '06 #3

"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
Apr 18 '06 #4

"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

Apr 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Paul | last post: by
4 posts views Thread by robboll | last post: by
6 posts views Thread by Steven Smith | last post: by
4 posts views Thread by Gregory Gadow | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.