display list of tables in access form to select from

[John Y]

I am trying to create a form that will display a list of tables that one can be selected from, to then run a query to select data from that table. [ the tables are a year based table of same data -- membership], and query can select same data from any of the tables.

[cactusdata]

Bad design. Move all membership data to one table including a field for the year.
Next, run a select query that filters on the selected year(s).

[isladogs]

Agree completely with @cactusdata.
However if you want code to view a list of tables in a form listbox, I have an example View Database Objects app on my website.
I'm not allowed to post a link here due to forum rules but you can find it by doing a Google search for View database objects Mendip Data Systems

[twinnyfo]

A simple approach is to use the following query to list your tables (this will also include linked tables):

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

Hope this hepps!

[isladogs]

The code supplied by twinnyfo will ONLY find linked Access/Excel/text tables. Local tables won't be listed.

If you need local tables, those have MSysObjects.Type=1 & ODBC tables such as SQL Server have MSysObjects.Type=4

To get all tables, you need:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type IN (1,4,6)
ORDER BY MSysObjects.Name;

That code will also list system tables which you probably don't want. So a further modification will exclude them but show all other local & linked tables:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE ((Not ((MSysObjects.Name) Like "MSys*" 
Or (MSysObjects.Name) Like "USys*" 
Or (MSysObjects.Name) Like "f_*")) 
AND ((MSysObjects.Type) In (1,4,6)))
ORDER BY MSysObjects.Name;
 

[John Y]

Thanks for your replies and advise ... How ever I inherited this and was looking for an easy way to solve the problem ..

As member year info is in different year tables [ membership 1991 ... 1992 etc] this creates problem of getting a year field in master database filled with each members year of membership..
and my knowledge in Db mods is no where near professional..

That is why i was trying to get around it by being able to select year db and run query on it..

I have the query that selects the tables, but can not get it to display in a form in a list box that can be selected from, to run other query on that selected table ..

Maybe I should ask "Is this possible" ..??

Any Help would be appreciated ...

[twinnyfo]

isladogs,

Thanks for the correction for type 1 -- I happened to be looking at a DB that had all linked tables....

--------------

John,

Just assign the query described above at the row source for your list box or combo box. Then use the value of that control to execute your other actions.

However, your bigger, more glaring, more "elephant in the room" issue is your table design. Just combine all your tables into one.

As IslaDogs recommended, create a new table with all the same fields you have now, but add a field for year. Then (this will take a bit of time) append all your data from the original tables to the new table, updating the Year accordingly.

Hope this hepps!