Query not refreshed with new names when tables renamed

[vineet78]

Hello

I renamed two tables in simple DB but Access query based on two tables refreshed names fully for one table but partially for other table.
I am confused as to why full change was not done in Access query for second table.

Can someone help me on this understanding please.

Regards
Vineet

 

[stumac]

If your setting for Name autocorrect in options is on then access renames the tables within Queries etc, however, it wont rename references to tables such as Expressions where the table is referenced (Dlookup etc). would need to see the SQL for the query to be able to give exact reason it wasn't updated in your case.

 

[vineet78]

Hi Stumec

I am using below SQL. in below SQL, i have a table named FUNCTION, and i want to change its name but query is not updating. I have nameautocorrect option turned on.

SELECT Function.SubFunction, Function.Cost_Centre, [Exp1]+[Exp2] AS YTD, IIf(Sum([Functionwise-Billed_Till_Date]![Amount]) Is Null,0,Sum([Functionwise-Billed_Till_Date]![Amount]))+[Exp3] AS Exp1, IIf([Functionwise-Current Month]![Amount] Is Null,0,[Functionwise-Current Month]![Amount]) AS Exp2, YTD.Unitcost AS Exp3
FROM ((Function LEFT JOIN [Functionwise-Current Month] ON Function.SubFunction = [Functionwise-Current Month].SubFunction) LEFT JOIN [Functionwise-Billed_Till_Date] ON Function.SubFunction = [Functionwise-Billed_Till_Date].SubFunction) LEFT JOIN YTD ON Function.SubFunction = YTD.SubFunction
GROUP BY Function.SubFunction, Function.Cost_Centre, IIf([Functionwise-Current Month]![Amount] Is Null,0,[Functionwise-Current Month]![Amount]), YTD.Unitcost, Function.Function
HAVING (((Function.Function) Like IIf(IsNull([Forms]![frmReportSelector]![cmb1]),"*",DLookUp("[FunctionHeaders]![Function]","[FunctionHeaders]","[FunctionHeaders]![ID]=" & [Forms]![frmReportSelector]![cmb1]))));

 

[stumac]

I am pretty sure that the reason it isn't working in your situation is the use of the reserved word 'Function' as a table name.

Using reserved words as table and field names in access should be avoided as you will find issues like this cropping unexpectedly. Renaming it is a good idea but you will need to look at the object dependencies of the table and manually update all the dependant queries.

More info and a list of reserved words can be found here:

https://support.office.com/en-gb/ar...-symbols-ae9d9ada-3255-4b12-91a9-f855bdd9c5a2

 

[vineet78]

thank you very much !!!
i did not know of usage of reserved words in DB thus used whatever came to my head.

thanks once more Stumac