List Tables in a List Box

How do I list the tables that are in my application in a list box?

Thanks.
Buck_BeasomDatabase DesignerAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Mod to not show MSys tables (system tables):


SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((InStr(1,[Name],"MSys")=0)=True))
ORDER BY MSysObjects.Name;

To included Linked Tables:

SELECT MSysObjects.Name, IIf([Type]=6,"Yes","") AS Linked
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=6) AND ((InStr(1,[Name],"MSys")=0)=True))
ORDER BY MSysObjects.Name;

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Make this the Row Source of the List Box:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name;

mx
0
 
Jeffrey CoachmanMIS LiasonCommented:
<*No Points wanted*>
You can revise the SQL MX posted to exclude system tables with something like this:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type=1 AND Left(MSysObjects.Name,4)<>"msys"
ORDER BY MSysObjects.Name;
0
 
Buck_BeasomDatabase DesignerAuthor Commented:
Both your solutions were helpful!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.