Replacing tables name within queries

[jaryszek]

Hi Guys,

i am using linked tables and when i am changing it within BE (postgresql) i have problem with queries.
I have to go for each of query and replace table name for new one.

It is a way to do it in easy way? No manually?

Best,
Jacek

[orange]

Can you tell us why you have to replace table name for new one?

[jaryszek]

Hi orange,

ok:

I have table t_clients and this is linked table.

I am changing its name to t_customers.

So i have to refresh linked table and refresh queries with this table.

Best,
Jacek

[Gicu]

Jacek,

Not sure why you need to rename the table, but you could try to see if turning the Name AutoCorrect on and renaming the table first will work for you. So what I propose is to have the first table (t_clients) in Postgre be, then rename the t_clients in the front-end to t_customers (at which point NameAutocorrect should update all the object relationships) and only after that rename it in the back-end and refresh the links. Name Autocorrect was historically a very messy feature that most people had disabled (me included), but lately I found it was working in cases like you describe (I would turn it back off after you use it).

Cheers,
Vlad

[isladogs]

Gicu's solution should work fine for queries but won't alter VBA code

In all, you need to do three things:
1. In the VBE, search & replace all instances of tblClients with tblCustomers

2. Now go to Database Tools ... Object Dependences
Select tblClients and click Objects that depend on me to get a list of all quries that include that table
Then edit the SQL manually
OR do that part as Gicu has suggested

3. Check any macros if you use them

OR if that's likely to take a long time, I suggest you download the free Access add-in, V-Tools, which includes a deep search & replace feature which works well
The only thing it cannot alter are macros

[jaryszek]

Thank you Guys !!

Fortunately in my model changing SQL manually was short task.
But for the future i will use your tips and advices.

Best Wishes,
Jacek