Avatar of kwarden13
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?
Microsoft AccessVisual Basic ClassicSQL

Avatar of undefined
Last Comment
Nick67

8/22/2022 - Mon
Patrick Matthews

Get this indispensable tool for Access developers:

http://www.rickworld.com/products.html

Not free, but TOTALLY worth the price.
ASKER CERTIFIED SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

10 seconds apart :)
ASKER
kwarden13

I cant. it wont let me download it onto my work computer. I dont have admin rights.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Patrick Matthews

You could try looping through your QueryDefs collection, then, but that is a potentially dangerous route...
jerryb30

Probably more dangerous to try to hand-edit 100+ queries.  Why is the table name so important?  

Patrick Matthews

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.tblCustomersID".

:)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jerryb30

Easiest solution is to create a query with the same name as the old table, whose data source is the newly named table.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"You could try looping through your QueryDefs collection, "
And only some of they queries need to be renamed probably.

mx
ASKER
kwarden13

Any code for looping through? I am not sure where to put the code?!?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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
jerryb30

What does not work about my suggestion?
ASKER
kwarden13

I built the database. i can go through every query but i rather not.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Patrick Matthews

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
jerryb30

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.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"Access would enforce distinct names among database objects...)"
Yeah ... and even Northwind doesn't do that, lol.

mx
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Nick67

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