By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,653 Members | 1,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,653 IT Pros & Developers. It's quick & easy.

display list of tables in access form to select from

P: 2
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.
2 Weeks Ago #1
Share this Question
Share on Google+
6 Replies

cactusdata
Expert 100+
P: 128
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).
2 Weeks Ago #2

isladogs
Expert
P: 58
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
2 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,551
A simple approach is to use the following query to list your tables (this will also include linked tables):

Expand|Select|Wrap|Line Numbers
  1. SELECT MSysObjects.Name
  2. FROM MSysObjects
  3. WHERE MSysObjects.Type = 6
  4. ORDER BY MSysObjects.Name;
Hope this hepps!
2 Weeks Ago #4

isladogs
Expert
P: 58
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT MSysObjects.Name
  2. FROM MSysObjects
  3. WHERE MSysObjects.Type IN (1,4,6)
  4. 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT MSysObjects.Name
  2. FROM MSysObjects
  3. WHERE ((Not ((MSysObjects.Name) Like "MSys*" 
  4. Or (MSysObjects.Name) Like "USys*" 
  5. Or (MSysObjects.Name) Like "f_*")) 
  6. AND ((MSysObjects.Type) In (1,4,6)))
  7. ORDER BY MSysObjects.Name;
  8.  
2 Weeks Ago #5

P: 2
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 ...
1 Week Ago #6

twinnyfo
Expert Mod 2.5K+
P: 3,551
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!
1 Week Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.