Query to populate combobox with names of tables, only if they contain data

I just came up with an interesting trick that may be useful to someone else.



I have a form with a combobox, populated by names of tables in the database.
The intent is to let the user select a table that is then used for
subsequent inquiries. What was making the whole process a little inelegant
was that occasionally, some of the tables would be empty. There is no point
in offering a particular table at all, if there is no data in it for the
user to examine. What I wanted was a way to make the combobox display only
those tables that have at least one record.



It would be a fairly routine exercise to do all this with VBA and populate
the combobox in code, but I was already using a query to populate the
combobox directly and didn't want to redo the whole thing, besides which, I'm
always looking for new ways to do stuff.



The source for the combobox was this query:



SELECT Name

FROM MSysObjects

WHERE Type=4;



The combobox displays the names of all the linked tables in the database,
whether or not they contain data.



To make the combobox display only those tables that have some data, I
created a few auxiliary queries and joined the results.



First, I made individual queries for each linked table that return a literal
of one record, the table name, if the table contains data. The queries are
named "First of " and the source table name, here [First of
data-chiroptera]:



SELECT "data-chiroptera" AS TableName

FROM [data-chiroptera]

HAVING First(druh) Is Not Null;



This returns one record, one field, with the literal "data-chiroptera" if
there is anything in the [druh] field (a required field in the table) and an
empty result set otherwise. There is one such query for every linked table.



Next I made a union query, named [Union of Firsts], to join all these
one-field queries into a single recordset:



SELECT TableName

FROM [First of 2006-dotazniky]



UNION



SELECT TableName

FROM [First of data-chiroptera]



UNION



SELECT TableName

FROM [First of data-mammalia];



Finally, I modified the source query for the combobox to use this query:



SELECT MSysObjects.Name

FROM MSysObjects INNER JOIN

[Union of Firsts] ON MSysObjects.Name = [Union of Firsts]. TableName

WHERE MSysObjects.Type=4;



This populates my combobox with only the names of those tables that have
data, since the union query has a record for a table only if the table
contains data, and this last join matches only the linked tables whose names
appear in the union query. Simple, quick and no VBA. This is a static
configuration, so I can create it once and leave it alone, but it would be
simple enough to create the "First" and "Union" queries in code if the
linked tables were going to change.



Pete


--
This e-mail address is fake, to keep spammers and their address harvesters
out of my hair. If you want to get in touch personally, I am 'pdanes' and I
use yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.

On Fri, 20 Nov 2009 14:37:06 +0100, "Skru spammers"
wrote:

Here is an alternative with a few lines of VBA. Set your combobox to
have two columns, both visible. The first column is the bound column.
The RowSourceType is set to ValueList. Then in the Form_Load write:
Me.myCombo.RowSource = GetTableInfo()

And in some standard module you have:
Public Function GetTableInfo() As String
Dim strData As String
Dim td As TableDef
For Each td In CurrentDb.TableDefs
strData = strData & td.Name & ";" & GetRecordCount(td.Name) &
";"
Next td
GetTableInfo = strData
End Function

Private Function GetRecordCount(ByVal strTable As String) As Long
Dim lngCount As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenTable,
dbReadOnly)
If Not rs.EOF Then rs.MoveLast
lngCount = rs.RecordCount
rs.Close
Set rs = Nothing
GetRecordCount = lngCount
End Function

If you don't like to show MSys* tables, change the code to:
For Each td In CurrentDb.TableDefs
If td.Attributes And dbSystemObject Then
'no need to display system objects
Else
strData = strData & td.Name & ";" &
GetRecordCount(td.Name) & ";"
End If
Next td

-Tom.
Microsoft Access MVP


I just came up with an interesting trick that may be useful to someone else.



I have a form with a combobox, populated by names of tables in the database.
The intent is to let the user select a table that is then used for
subsequent inquiries. What was making the whole process a little inelegant
was that occasionally, some of the tables would be empty. There is no point
in offering a particular table at all, if there is no data in it for the
user to examine. What I wanted was a way to make the combobox display only
those tables that have at least one record.



It would be a fairly routine exercise to do all this with VBA and populate
the combobox in code, but I was already using a query to populate the
combobox directly and didn't want to redo the whole thing, besides which, I'm
always looking for new ways to do stuff.



The source for the combobox was this query:



SELECT Name

FROM MSysObjects

WHERE Type=4;



The combobox displays the names of all the linked tables in the database,
whether or not they contain data.



To make the combobox display only those tables that have some data, I
created a few auxiliary queries and joined the results.



First, I made individual queries for each linked table that return a literal
of one record, the table name, if the table contains data. The queries are
named "First of " and the source table name, here [First of
data-chiroptera]:



SELECT "data-chiroptera" AS TableName

FROM [data-chiroptera]

HAVING First(druh) Is Not Null;



This returns one record, one field, with the literal "data-chiroptera" if
there is anything in the [druh] field (a required field in the table) and an
empty result set otherwise. There is one such query for every linked table.



Next I made a union query, named [Union of Firsts], to join all these
one-field queries into a single recordset:



SELECT TableName

FROM [First of 2006-dotazniky]



UNION



SELECT TableName

FROM [First of data-chiroptera]



UNION



SELECT TableName

FROM [First of data-mammalia];



Finally, I modified the source query for the combobox to use this query:



SELECT MSysObjects.Name

FROM MSysObjects INNER JOIN

[Union of Firsts] ON MSysObjects.Name = [Union of Firsts]. TableName

WHERE MSysObjects.Type=4;



This populates my combobox with only the names of those tables that have
data, since the union query has a record for a table only if the table
contains data, and this last join matches only the linked tables whose names
appear in the union query. Simple, quick and no VBA. This is a static
configuration, so I can create it once and leave it alone, but it would be
simple enough to create the "First" and "Union" queries in code if the
linked tables were going to change.



Pete

Hi Tom,

that's a usable method, but in reading the code, I see you're displaying all
the tables, with their record counts. That can certainly be useful, but in
my case, I wanted to not display the names of empty tables at all, so I
would need to add a test to not include the cases where GetRecordCount
returns zero. Also, when you use a value list, you're rather sharply limited
in length. My app has only a few tables, so it would be fine, but a longer
list might run into problems. Regardless, thank you, another one for the
toolbox.

Of course, once you go to VBA, there are any number of ways to accomplish
the task. The cute thing about the method I posted was that it
does -everything- with SQL, which is my preference, whenever possible. It's
easier to read, easier to troubleshoot, faster to execute, inherently
modular and much less prone to obscure logic bugs. Nothing against VBA, I
use it constantly, but my personal preference is to do as much as possible
with database properties and queries, and only code what can't be done any
other way.

And finally, your version can also be expressed in SQL. Simply change my
"First of" queries to "Count of" queries, containing the following SQL:

SELECT "data-chiroptera" AS TableName, Count(Druh) as RecordCount
FROM [data-chiroptera];

or if you want to exclude empty tables:

SELECT "data-chiroptera" AS TableName, Count(Druh) as RecordCount
FROM [data-chiroptera]
HAVING Count(Druh)0;

and everthing else works the same.

Pete



"Tom van Stiphout" píse v diskusním príspevku
...
On Fri, 20 Nov 2009 14:37:06 +0100, "Skru spammers"
wrote:

Here is an alternative with a few lines of VBA. Set your combobox to
have two columns, both visible. The first column is the bound column.
The RowSourceType is set to ValueList. Then in the Form_Load write:
Me.myCombo.RowSource = GetTableInfo()

And in some standard module you have:
Public Function GetTableInfo() As String
Dim strData As String
Dim td As TableDef
For Each td In CurrentDb.TableDefs
strData = strData & td.Name & ";" & GetRecordCount(td.Name) &
";"
Next td
GetTableInfo = strData
End Function

Private Function GetRecordCount(ByVal strTable As String) As Long
Dim lngCount As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenTable,
dbReadOnly)
If Not rs.EOF Then rs.MoveLast
lngCount = rs.RecordCount
rs.Close
Set rs = Nothing
GetRecordCount = lngCount
End Function

If you don't like to show MSys* tables, change the code to:
For Each td In CurrentDb.TableDefs
If td.Attributes And dbSystemObject Then
'no need to display system objects
Else
strData = strData & td.Name & ";" &
GetRecordCount(td.Name) & ";"
End If
Next td

-Tom.
Microsoft Access MVP


I just came up with an interesting trick that may be useful to someone
else.



I have a form with a combobox, populated by names of tables in the
database.
The intent is to let the user select a table that is then used for
subsequent inquiries. What was making the whole process a little inelegant
was that occasionally, some of the tables would be empty. There is no
point
in offering a particular table at all, if there is no data in it for the
user to examine. What I wanted was a way to make the combobox display only
those tables that have at least one record.



It would be a fairly routine exercise to do all this with VBA and populate
the combobox in code, but I was already using a query to populate the
combobox directly and didn't want to redo the whole thing, besides which,
I'm
always looking for new ways to do stuff.



The source for the combobox was this query:



SELECT Name

FROM MSysObjects

WHERE Type=4;



The combobox displays the names of all the linked tables in the database,
whether or not they contain data.



To make the combobox display only those tables that have some data, I
created a few auxiliary queries and joined the results.



First, I made individual queries for each linked table that return a
literal
of one record, the table name, if the table contains data. The queries are
named "First of " and the source table name, here [First of
data-chiroptera]:



SELECT "data-chiroptera" AS TableName

FROM [data-chiroptera]

HAVING First(druh) Is Not Null;



This returns one record, one field, with the literal "data-chiroptera" if
there is anything in the [druh] field (a required field in the table) and
an
empty result set otherwise. There is one such query for every linked
table.



Next I made a union query, named [Union of Firsts], to join all these
one-field queries into a single recordset:



SELECT TableName

FROM [First of 2006-dotazniky]



UNION



SELECT TableName

FROM [First of data-chiroptera]



UNION



SELECT TableName

FROM [First of data-mammalia];



Finally, I modified the source query for the combobox to use this query:



SELECT MSysObjects.Name

FROM MSysObjects INNER JOIN

[Union of Firsts] ON MSysObjects.Name = [Union of Firsts]. TableName

WHERE MSysObjects.Type=4;



This populates my combobox with only the names of those tables that have
data, since the union query has a record for a table only if the table
contains data, and this last join matches only the linked tables whose
names
appear in the union query. Simple, quick and no VBA. This is a static
configuration, so I can create it once and leave it alone, but it would be
simple enough to create the "First" and "Union" queries in code if the
linked tables were going to change.



Pete