ADOX - Table list to listbox or dropdown?

[SusanV]

Hi all,

I have the following sub which works fine to the immediate window, but now I
need to actually DO something with it <grin>. I'd like to populate a listbox
so that users can select the tables they would like to use, but I can't seem
to wrap my head around how to go about it - do I need to dump it into a temp
table then use that as my source or can this be done in a more direct
fashion?

Thanks in advance!

Susan

code:
''''''''''''''''''''''''''''''''''''''''
Private Sub btnGetTableList_Click()
Dim cnx As ADODB.Connection
Dim catDB As ADOX.Catalog
Dim tblList As ADOX.Table
Dim strDBPath As String
Set cnx = CurrentProject.Connection
Set catDB = New ADOX.Catalog
strDBPath = "E:\PMO_Gen\dev\"
catDB.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source= '" & strDBPath & "Pivot_Concat.mdb';"
For Each tblList In catDB.Tables
If tblList.Type = "TABLE" Then
Debug.Print tblList.Name & vbTab & tblList.Type
End If
Next
Set catDB = Nothing
cnx.Close
End Sub
''''''''''''''''''''''''''''''''''''''''

 

[Dirk Goldgar]

Hi all,

I have the following sub which works fine to the immediate window,
but now I need to actually DO something with it <grin>. I'd like to
populate a listbox so that users can select the tables they would
like to use, but I can't seem to wrap my head around how to go about
it - do I need to dump it into a temp table then use that as my
source or can this be done in a more direct fashion?

Thanks in advance!

Susan

code:
''''''''''''''''''''''''''''''''''''''''
Private Sub btnGetTableList_Click()
Dim cnx As ADODB.Connection
Dim catDB As ADOX.Catalog
Dim tblList As ADOX.Table
Dim strDBPath As String
Set cnx = CurrentProject.Connection
Set catDB = New ADOX.Catalog
strDBPath = "E:\PMO_Gen\dev\"
catDB.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source= '" & strDBPath & "Pivot_Concat.mdb';"
For Each tblList In catDB.Tables
If tblList.Type = "TABLE" Then
Debug.Print tblList.Name & vbTab & tblList.Type
End If
Next
Set catDB = Nothing
cnx.Close
End Sub
''''''''''''''''''''''''''''''''''''''''

To do it using code like what you've posted, you'd need to create a
user-defined rowsource function. This is a function that has a special
format, with prescribed arguments and return values. Details may be
found in the help file.

However, for a Jet database you can also write a simple query that
selects the table names from one of the hidden, system tables. For all
local (non-linked) tables, such a query would look like this:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~")
AND ((Left$([Name],4))<>"Msys")
AND ((MsysObjects.Type)=1)))
ORDER BY MsysObjects.Name;

If you want to include all linked tables, too, you'd modify that to
select a couple of other types:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~")
AND ((Left$([Name],4))<>"Msys")
AND ((MsysObjects.Type) In (1,4,6)))
ORDER BY MsysObjects.Name;

So you could use either of these SQL statements as the rowsource for
your list box.

 

[Susan V]

Thank you Dirk - yes, this is Jet and I believe the query will work
beautifully!

You guys ROCK!

;-)

Susan

Hi all,

I have the following sub which works fine to the immediate window,
but now I need to actually DO something with it <grin>. I'd like to
populate a listbox so that users can select the tables they would
like to use, but I can't seem to wrap my head around how to go about
it - do I need to dump it into a temp table then use that as my
source or can this be done in a more direct fashion?

Thanks in advance!

Susan

code:
''''''''''''''''''''''''''''''''''''''''
Private Sub btnGetTableList_Click()
Dim cnx As ADODB.Connection
Dim catDB As ADOX.Catalog
Dim tblList As ADOX.Table
Dim strDBPath As String
Set cnx = CurrentProject.Connection
Set catDB = New ADOX.Catalog
strDBPath = "E:\PMO_Gen\dev\"
catDB.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source= '" & strDBPath & "Pivot_Concat.mdb';"
For Each tblList In catDB.Tables
If tblList.Type = "TABLE" Then
Debug.Print tblList.Name & vbTab & tblList.Type
End If
Next
Set catDB = Nothing
cnx.Close
End Sub
''''''''''''''''''''''''''''''''''''''''

To do it using code like what you've posted, you'd need to create a
user-defined rowsource function. This is a function that has a special
format, with prescribed arguments and return values. Details may be
found in the help file.

However, for a Jet database you can also write a simple query that
selects the table names from one of the hidden, system tables. For all
local (non-linked) tables, such a query would look like this:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~")
AND ((Left$([Name],4))<>"Msys")
AND ((MsysObjects.Type)=1)))
ORDER BY MsysObjects.Name;

If you want to include all linked tables, too, you'd modify that to
select a couple of other types:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~")
AND ((Left$([Name],4))<>"Msys")
AND ((MsysObjects.Type) In (1,4,6)))
ORDER BY MsysObjects.Name;

So you could use either of these SQL statements as the rowsource for
your list box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)