List of Tables

[monsjic]

I have written a module that prompts the user to input the name of the table it is supposed to operate on. Is there a way to display a list of the tables to the user instead? I would like to do this so that I can do away with the error handling for invalid input. I know I could enumerate through the tabledefs collection and put them in a listbox, but I was wondering if there was a built in function that Microsoft provided. Why reinvent the wheel?

Thanks for any help

 

[jebry]

Hi!

Use this SQL as the row source of your list box:

Select Name From MySysObjects Where Type = 1

hth
Jeff Bridgham
bridgham@purdue.edu

 

[fwatanabe]

Seems to me enumerating through the tabledefs collection would be the easiest way to go.

You could look through msysobjects and try to figure out which ones are tables. It seems to me though, that queries & tables have the same object type number, so this may not work.

I would loop through tabledefs, create a string of the table names and set your listbox's rowsource to that string.

I personally have never seen a function that will do this automagically.

 

[monsjic]

Thanks jerby!

I made a few changes, but you definitely gave me the right idea. The SQL I used follows, in case anyone else in the future wants to use it: (the Flags=0 excludes system tables)

SELECT [MSysObjects].[Name] FROM MSysObjects WHERE (([MSysObjects].[Type])=1) And (([MSysObjects].[Flags])=0)) ORDER BY [MSysObjects].[Name];

 

[jebry]

Hi again!

Your welcome! I thought about the system tables later, but I see you already figured it out!

Jeff Bridgham
bridgham@purdue.edu