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

How to bring up a list of tables in form view containing the word "backup"

P: 23
Well, I think I've described what I want to do in the title here.

In the database, I have two main tables that contain the main data for the database. One for group expenditures, another for application details. I have a query for each of them to copy all the contents of the table into a new table, then delete all the data from the main table. I need to do this becasue once a year the data needs to be removed, ready for putting in new data. I couldn't just delete it without backing it up, just incase we need to look at the old data later. The database is all user-proofed, so a user doesn't have access to menus or the database window - so users don't have access to these backup tables.

Basically, I want to be able to have some sort of query or something to bring up a list of all the tables containing the word "backup", becasue at the moment the only way to access the backups is to open the database in design view - which is ok for me, but I can't have other users doing this. I can't just link to the tables through a form, because we don't know the names of the forms (not the full name anyway, there will be new ones added later. Under the format of "AppBackup__dd_mm_yyyy" and "ExpBackup__dd_mm_yyyy". I just want to be able to open a form, show a list of tables containing the word "backup", then from the list be able to click a button to show that table's contents (preferably in a form).

Sorry about this. Don't know if anybody can help. I could just have all the backup data in one table, then rather than adding a new table each time we backup, it just appends to the table. but after a while, this table could get huge, plus it then won't clearly show which data corresponds to what year. I'll do the append thing as a last resort if I have to, that's easy to do. but preferably I would like to do what I've asked here, to bring up a list (in form view) of all tables containing the word "backup", then to click one to show that table's contents.

Thanks in advance if anybody can help, and even if you can't, thanks for reading.
Jun 9 '07 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 3,532
This will do the job!

Expand|Select|Wrap|Line Numbers
  1.   On your form place a Combobox
  2.  When the Combobox Wizard comes up click on Cancel
  3.  Goto the Combobox Properties >> Data 
  4.  Row SourceType = Table/Query
  5. Row Source = SELECT [Name] FROM MSysObjects WHERE [Name] Like '*backup*' AND [Name] Not Like 'MSys*' AND [Type]=1; 
Now in the VBE (code window)
Expand|Select|Wrap|Line Numbers
  1. Private Sub YourComboBox_AfterUpdate()
  2.     DoCmd.OpenTable YourComboBox
  3.  End Sub
This will only bring up tables that have "backup" somewhere in their names.

Good Luck!
Jun 9 '07 #2

P: 23
Fantastic, thank you very much, it works great. Can't thank you enough. A cookie for thee shall be thy reward. Thank you.
Jun 11 '07 #3

Expert 2.5K+
P: 3,532
Glad we could help you!
Jun 12 '07 #4

Post your reply

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