Let's say I have a query with two tables joined. The tables do not have any columns with the same name. My question is does Access benefit from putting the table name in front of the column name? For example by using
SELECT tbl1.Key FROM tbl1 INNER JOIN tbl2 ON tbl.Key = tbl2.ID
SELECT Key FROM tbl1 INNER JOIN tbl2 ON Key = ID
I ask because Access always uses [tableName].[Column] even if the column names are unique. I am creating the queries in VBA and usually use just the column name to shorten the code.
My thought was that with the table name prefix Access knows right away what column I am referencing, but without the prefix Access has to determine which table and then find the column. If this is the case and I can gain some performance be using the table name then I will.
I don't believe that any gains will be made by including the table name in the reference. The reason Microsoft does it is so it can guarantee that the field you added will be taken from the proper table. If two tables have the same field name (which can happen frequently) then trying to figure out which table you want is impossible without user intervention.
Creating indexes and setting up Relationships will help Access optimize your queries.