Get table names from MSAccess Datasource

[Steven Smith]

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

[Karl E. Peterson]

Re: Get table names from MSAccess Datasource

Steven Smith wrote:[color=blue]
> 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?[/color]

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.
--

[Steven Smith]

Re: Get table names from MSAccess Datasource

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" <karl@mvps.or g> wrote in news:e210ej$3im $1
@emma.aioe.org:
[color=blue]
> Steven Smith wrote:[color=green]
>> 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?[/color]
>
> 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.[/color]

[Ralph]

Re: Get table names from MSAccess Datasource


"Steven Smith" <sluke4@webbox. com> wrote in message
news:TuY0g.2741 $u27.1398@twist er.nyroc.rr.com ...[color=blue]
> Thanks Karl... I would've preferred ADO or SQL, but since it's only for[/color]
one[color=blue]
> query in my program, this'll work just fine!
>
>
> "Karl E. Peterson" <karl@mvps.or g> wrote in news:e210ej$3im $1
> @emma.aioe.org:
>[color=green]
> > Steven Smith wrote:[color=darkred]
> >> 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?[/color]
> >
> > 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.[/color]
>[/color]

Sub ListTablesADO() Dim cnn As ADODB.Connectio n Set cnn = New
ADODB.Connectio n 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=...;Databa se=...;", "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("COLU MN_NAME") rsColumns.MoveN ext Loop
rsTables.MoveNe xt LoopEnd SubSub ListTablesADOX( ) Dim cnn As
ADODB.Connectio n Set cnn = New ADODB.Connectio n 'Open connection you
want To get database objects cnn.Provider = "MSDASQL" ' whatever you
need... cnn.Open "DSN=...;Databa se=...;", "UID", "PWD" 'Create catalog
object Dim Catalog As New ADOX.Catalog Set Catalog.ActiveC onnection = 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

[Ralph]

Re: Get table names from MSAccess Datasource


"Ralph" <nt_consulting6 4@yahoo.com> wrote in message
news:R8adnUFZkt lVcdnZnZ2dnUVZ_ uudnZ2d@arkansa s.net...[color=blue]
>
> "Steven Smith" <sluke4@webbox. com> wrote in message
> news:TuY0g.2741 $u27.1398@twist er.nyroc.rr.com ...[color=green]
> > Thanks Karl... I would've preferred ADO or SQL, but since it's only for[/color]
> one[color=green]
> > query in my program, this'll work just fine!
> >
> >
> > "Karl E. Peterson" <karl@mvps.or g> wrote in news:e210ej$3im $1
> > @emma.aioe.org:
> >[color=darkred]
> > > 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[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > offer a choice of tables. And, within each TableDef, you can loop the
> > > Fields collection.[/color]
> >[/color]
>
> Sub ListTablesADO() Dim cnn As ADODB.Connectio n Set cnn = New
> ADODB.Connectio n 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=...;Databa se=...;", "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 ,[/color]
_[color=blue]
> Array(Empty, Empty, "" & rsTables("TABLE _NAME"))) Do While Not
> rsColumns.EOF Debug.Print rsTables("TABLE _NAME") & ", " & _
> rsColumns("COLU MN_NAME") rsColumns.MoveN ext Loop
> rsTables.MoveNe xt LoopEnd SubSub ListTablesADOX( ) Dim cnn As
> ADODB.Connectio n Set cnn = New ADODB.Connectio n 'Open connection you
> want To get database objects cnn.Provider = "MSDASQL" ' whatever you
> need... cnn.Open "DSN=...;Databa se=...;", "UID", "PWD" 'Create catalog
> object Dim Catalog As New ADOX.Catalog Set Catalog.ActiveC onnection =[/color]
cnn[color=blue]
> 'List tables And columns Dim tbl As ADOX.Table Dim col As ADOX.Column[/color]
For[color=blue]
> Each tbl In Catalog.Tables Debug.Print tbl.Name For Each col In
> tbl.Columns Debug.Print col.Name Next NextEnd Sub
>[/color]

Sorry, don't know what happened to the paste...

Sub ListTablesADO()
Dim cnn As ADODB.Connectio n:
Set cnn = New ADODB.Connectio n
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=...;Databa se=...;", "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("COLU MN_NAME")
rsColumns.MoveN ext
Loop
rsTables.MoveNe xt
Loop
End Sub

Sub ListTablesADOX( )
Dim cnn As ADODB.Connectio n
Set cnn = New ADODB.Connectio n
'Open connection you want To get database objects
cnn.Provider = "MSDASQL" ' whatever you need...
cnn.Open "DSN=...;Databa se=...;", "UID", "PWD"
'Create catalog object
Dim Catalog As New ADOX.Catalog
Set Catalog.ActiveC onnection = 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