kwarden13
asked on
Replace Table Name used in QUERIES
I changed a table name and need to replace the name in 100+ queries. Any easy way to do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
10 seconds apart :)
ASKER
I cant. it wont let me download it onto my work computer. I dont have admin rights.
You could try looping through your QueryDefs collection, then, but that is a potentially dangerous route...
Probably more dangerous to try to hand-edit 100+ queries. Why is the table name so important?
Here's why. Suppose you had a table name Customer, which had a column named CustomerID.
Now, you want to update all your queries to refer to tblCustomers instead of Customer. If you do a straight replace of "tblCustomers" for "Customer", then "Customer.CustomerID" can turn into "tblCustomers.tblCustomers
:)
Now, you want to update all your queries to refer to tblCustomers instead of Customer. If you do a straight replace of "tblCustomers" for "Customer", then "Customer.CustomerID" can turn into "tblCustomers.tblCustomers
:)
Easiest solution is to create a query with the same name as the old table, whose data source is the newly named table.
"You could try looping through your QueryDefs collection, "
And only some of they queries need to be renamed probably.
mx
And only some of they queries need to be renamed probably.
mx
ASKER
Any code for looping through? I am not sure where to put the code?!?
even with a code solution, Patrick has an excellent point @ http:#a36184018 ... so, the code has to take into account the 'exact match' type of find, etc. This or course ... is all handled in F&R noted above. If there is anyway you can figure out how to use that, I would seriously try and find (no pun) a way. Sooooooo much has already been handled for you ... years of development and perfection.
mx
mx
What does not work about my suggestion?
ASKER
I built the database. i can go through every query but i rather not.
Jerry,
Never thought to try that. If it works, it's pure genius. Of course, if it works, it also makes me very worried about Access :)
(IMHO, in a perfect world, Access would enforce distinct names among database objects...)
Patrick
Never thought to try that. If it works, it's pure genius. Of course, if it works, it also makes me very worried about Access :)
(IMHO, in a perfect world, Access would enforce distinct names among database objects...)
Patrick
Externally, an Access table and query look the same. Which is why they cannot share a name. It is no different from having a linked table versus one resident in the database.
"Access would enforce distinct names among database objects...)"
Yeah ... and even Northwind doesn't do that, lol.
mx
Yeah ... and even Northwind doesn't do that, lol.
mx
In Access 2010, with the settings I have in place, it just does it on the fly by itself.
In a test shim, if I rename Table1 to Table2, in the queries the table name changes auto-magically.
You have to have Perform Name Auto-Correct turned on--and turning it on, doing the change and turning it off again shouldn't be a big deal.
Access 2003 does it to if auto-correct is on, too.
For performance reasons, I usually have it turned off in production--but it's convenient in situations like this
In a test shim, if I rename Table1 to Table2, in the queries the table name changes auto-magically.
You have to have Perform Name Auto-Correct turned on--and turning it on, doing the change and turning it off again shouldn't be a big deal.
Access 2003 does it to if auto-correct is on, too.
For performance reasons, I usually have it turned off in production--but it's convenient in situations like this
http://www.rickworld.com/products.html
Not free, but TOTALLY worth the price.