Combo box to look at tables names

[Maracay]

Hi Gurus,

Every month I create a copy of a table as monthly backup, some times I need
to see some information of a previous month, what I need and I don’t know how
to do it is to create a combo box to choose the table of the month I want to
see, basically a combo box able to shows all the tables with names start with
tblMainClient. I usually call the back up tables tblMainClient+Month+Year,
Ex. tblMainClient042009.

Thanks

 

[Beetle]

Is there a reason why you are creating a separate backup table for every
month?

Why not just leave all the data in one table, backup your entire application
on a regular basis (which you should already be doing anyway), and use
queries, etc. to view the data for particular time periods?

You could easily create a small form with combo/text boxes where you
could enter the month and year you want to see, and a query that would
use those control values as criteria (assuming your main table has a
Date/Time field that stores the date of each record).

_________

Sean Bailey

 

[Maracay]

I know what you mean but this is a special request, the owner of the data
wants to have separate backups files, and in the current months file just the
data of the month, the easy way to created a consult screen is to letting him
choose the table, but as I said before I don't know how to do that.

Thanks

 

[Jeff Boyce]

I'm with Sean ... creating a separate spreadshee... (oops, I mean "table")
for each month is a little like trying to drive nails with a chainsaw. You
can (theoretically) do it, but only at risk to yourself, the nail and the
chainsaw.

If the owner of the data is "choosing" anyway, why not have that combobox
list the months (?!and years?!) of the data available, then select (only)
the selected month's (and year's) data from a single table?

This obviates the need to generate a large number of spreadsh... (dang,
goofed again!) tables that will then have to be managed.

Since you apparently have the control over the user interface, the owner
would never need to know that the table structure is well-normalized (a
single table), rather than committing spreadsheet on Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I know what you mean but this is a special request, the owner of the data
wants to have separate backups files, and in the current months file just the
data of the month, the easy way to created a consult screen is to letting him
choose the table, but as I said before I don't know how to do that.

Let me just amplify the good advice you're getting.

The most common source of data loss in Access databases is corruption of the
database itself. You'll *very* rarely get corruption of one table with other
tables being left unaffected (the exception is that tables with Memo fields
can get corrupted).

Therefore, making multiple tables in one database is like keeping several
little jewelboxes in a single big jewelbox. A thief (corruption) will just
take the big box (your database) and all your jewels (data) will be lost in
one swell foop. In fact running multiple MakeTable queries increases your risk
of corruption, I'd guess, since it requires frequent hits to the MSYSObjects
table.