Changing table names in queries.

[AccessBeetle]

Hello all,
I am migrating access bakend to SQL and I have changed names in SQL due to naming convention rules. I have linked those tables in access. There are almost 50 queries. Is there any way we can change the name of the tables in all these queries? Is it even possible?I am so curious because to go in each and every query and delete old tables and add new tables ..This is so tedious.
Any help??
Thanks

[Stewart Ross]

Hi. The SQL for Access queries is held in each query's QueryDef object's SQl property. Theoretically at least, it would be possible to loop through the querydef collection and use VBA's Replace function to replace all occurrences of particular table names with their revised versions.

It may not be so easy in practice to do so, as you will need to verify that each and every query you alter runs correctly and you may end up investing as much time in trying to program it and verify the results as you would in changing the queries manually.

An alternative approach: within the front end database rename the linked tables from their revised versions back to their originals - this just aliases the tables and has no effect on the name of the table at the back end of the link.

-Stewart

[Jim Doherty]

Hello all,
I am migrating access bakend to SQL and I have changed names in SQL due to naming convention rules. I have linked those tables in access. There are almost 50 queries. Is there any way we can change the name of the tables in all these queries? Is it even possible?I am so curious because to go in each and every query and delete old tables and add new tables ..This is so tedious.
Any help??
Thanks

In addition to Stewarts advice, unless you are satisifed that the queries you previously had are relatively simple affairs I would be looking at the performance of those existing queries separately and the current layout for each one separately.

I know this may sound burdensome but a backend commercial strength SQL engine is a wholly different animal, optimised differently and more importantly provides for functionality that you might need even need to include in your frontend because it can all be done at the back so to speak ie: stored procedures,user defined functions on the server and so on. What is your backend incidentally?

[AccessBeetle]

I guess it would be easier to change the names in queries manually because it is complicated and huge front end. If I mess up something it might drive me crazy to figure that out.
Stewart,

An alternative approach: within the front end database rename the linked tables from their revised versions back to their originals - this just aliases the tables and has no effect on the name of the table at the back end of the link.

I like this idea and going to try but then I think the person who will take it over will be confused between frontend and backend table names.
Thanks to you both for looking into it.