Hi. I have a simple question. I have a table and a query that calculates values using numbers from the table. I want to change the name of the table because it is not very descriptive. However when I do this, my query dosen't work anymore because it can't find the table anymore. So does anyone know how to change the name of the table without having to change every formula in the query?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Change name of table without damaging query
- Thread starter carolineJ
- Start date
What version are you using? In Access 2000 i saw (but have not used) where for tables and queries created in 2000, there is a feature that will do this for you.
in Access 97 there is no easy way.
If you are in 97, this is what i do: keep the old table.
duplicate it, and call it whatever you want.
for safety's sake, duplicate your query in case you mess up.
in the query, go SHOW TABLE and show the new table.
make your joins as they currently are with the old table.
down in the query grid, wherever the orig table name is selected, change it to the new table name. if you have formulas based on the old table, and the 'old' table name is in those formulas, just change it. change it once, copy it with your cursor, and paste it in all the other places.
when you are all done, save the query. then delete the old table. if you missed something, close and don't save, re-open and fix whatever it is you missed.
delete the old query (if you are uncomfortable deleting it right now, something i do is re-name things i think i won't need as zQueryName so it will sort to the bottom of the list and get out of my way, yet still be around in case i need it later).
re-name the new query as the old one.
all done
)
g
in Access 97 there is no easy way.
If you are in 97, this is what i do: keep the old table.
duplicate it, and call it whatever you want.
for safety's sake, duplicate your query in case you mess up.
in the query, go SHOW TABLE and show the new table.
make your joins as they currently are with the old table.
down in the query grid, wherever the orig table name is selected, change it to the new table name. if you have formulas based on the old table, and the 'old' table name is in those formulas, just change it. change it once, copy it with your cursor, and paste it in all the other places.
when you are all done, save the query. then delete the old table. if you missed something, close and don't save, re-open and fix whatever it is you missed.
delete the old query (if you are uncomfortable deleting it right now, something i do is re-name things i think i won't need as zQueryName so it will sort to the bottom of the list and get out of my way, yet still be around in case i need it later).
re-name the new query as the old one.
all done
)g
- Thread starter
- #3
UncleJack
Programmer
- Sep 20, 2001
- 344
Duplicate the table and change the name of the duplicate to the name you want. Open the query, add the new, duplicated table to the query. Display table names in your query. For each instance where the old table name appears change it to the new name. When finished delete the old table from the query and save the query.
Uncle Jack
Uncle Jack
Guest_imported
New member
- Jan 1, 1970
- 0
If you are using Access 2000, take a look at Name_AutoCorrect and make sure its turn on (look in general under options. This should help.
- Status
Similar threads
- Locked
- Question
- Locked
- Question