Fill a Combo Box with Table Names

[Keith Ratner]

I discovered an alternate method for accomplishing this task (see
http://support.microsoft.com/?kbid=210311) by utilizing hidden system
tables.

First, create the following query:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type = 6
ORDER BY MSysObjects.Name;

Save the query definition. In my example, I've named the query
"sysquery_listtables."

Set the combobox RowSource to the saved query.

In my application, I set the combobox value to "" on Form_Open to prevent an
initial value selection.

 

[Nikos Yannacopoulos]

Keith,

Note that object type 6 will return linked tables only. To return the
local tables, you need to filter on type 1, or either 1 or 6 for both
local and linked.

Also, you might want to take this a step further by filtering on table
name not starting with MSys, so as to exclude the system tables:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (MSysObjects.Type = 1 OR MSysObjects.Type = 6)
AND MSysObjects.Name NOT LIKE "MSys*"
ORDER BY MSysObjects.Name;

HTH,
Nikos

 

[Keith Ratner]

Nikos,

That slipped by me. Thanks!

Keith

Keith,

Note that object type 6 will return linked tables only. To return the
local tables, you need to filter on type 1, or either 1 or 6 for both
local and linked.

Also, you might want to take this a step further by filtering on table
name not starting with MSys, so as to exclude the system tables:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (MSysObjects.Type = 1 OR MSysObjects.Type = 6)
AND MSysObjects.Name NOT LIKE "MSys*"
ORDER BY MSysObjects.Name;

HTH,
Nikos

 

[Douglas J. Steele]

I believe you also need to filter on type 4 in order to pick up tables that
are linked using ODBC.