What does this code mean? 1

[shenlingstyle]

Hi, I'm analyzing a program made with MS Access. It has a whole bunch of tables and queries already made, and a form with lots of code behind it. I'm looking through and I'm not sure what the following means:

With CurrentDb
.QueryDefs("qry_cur_pier").SQL = _
" SELECT DISTINCT RE.AIRPORT_RESOURCE_NAME,
RE.AIRPORT_RESOURCE_KEY " & _
" FROM qry_cur_resource AS RE " & _
" WHERE RE.AIRPORT_RESOURCE_CODE = 'PIER';"

qry_cur_pier appears to be an already existing query. AIRPORT_RESOURCE_NAME and AIRPORT_RESOURCE_KEY are fields in that query. AIRPORT_RESOURCE_CODE is not in the query though. What does the RE. and the AS RE do here? Thanks in advance.

 

[CajunCenturion]

This is setting the SQL statement of an existing query. CurrentDB.QueryDefs is a collection object which contains one entry for each defined query, keyed by the name of the query, which in this case, is 'qry_cur_pier'.

This query is selecting fields from the recordset of another query -- qry_cur_resource. In other words, the query 'qry_cur_resource' is executed and a recordset created. This query then acts on that recordset. AIRPORT_RESOURCE_NAME, AIRPORT_RESOURCE_KEY, and AIRPORT_RESOURCE_CODE are all fields in the recordset created by 'qry_cur_resource' query. RE is defined as a alias for that recordset and functions the same as a table name.

RE.AIRPORT_RESOURCE_NAME means the Airport Resource Name field from the RE table. "FROM qry_cur_resource AS RE" means execute the 'qry_cur_resource' query, and name the recordset 'RE'.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[shenlingstyle]

Thanks for your reply! I'm still a bit confused about which happens after which.

"CurrentDB.QueryDefs is a collection object which contains one entry for each defined query, keyed by the name of the query, which in this case, is 'qry_cur_pier'."

What does QueryDefs mean? And why is qry_cur_pier in the parameter and not qry_cur_resource?

I see that AIRPORT_RESOURCE_NAME, AIRPORT_RESOURCE_KEY, and AIRPORT_RESOURCE_CODE are already fields in the qry_cur_resource query, so why use qry_cur_pier at all? qry_cur_resource already exists too right?

The order of the statements is confusing to me. 'SELECT DISTINCT RE.AIRPORT_RESOURCE_NAME' for example is executed before 'FROM qry_cur_resource AS RE'?

Thanks again for your reply. If you have time please help me with these questions.

 

[CajunCenturion]

QueryDefs doesn't mean anything per se, it is an object which at run time contains the definitions of the defined queries. All collection objects are be addressed by ordinal position, or by a key. In the case, the name of the query functions as the key. qry_cur_pier is the key because it is the qry_cur_pier that you're dealing with.

Yes, 'qry_cur_resource' does exist and it too has an entry in QueryDefs. I don't know what the SQL statement for qry_cur_resource actually is, but let's assume it is the following:

SELECT * FROM AIRPORT_TABLE WHERE PROVINCE = 'ON'

If we assume that AIRPORT_TABLE contains records for all the airports in Canada, then this query will extract all the pertinent information for only those airports in Ontario.

Now, when you execute qry_cur_pier, that query will first call qry_cur_resource and create a recordset which contains only those airports in Ontario. The 'AS RE' assigns the alias name 'RE' to that recordset, or intermediate table if you prefer. The main query (qry_cur_pier) will then act against that intermediate query to extract the resource information for those that are piers.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[shenlingstyle]

Hi, now it makes a lot more sense, thanks a bunch. You really know this stuff. I have some final questions.

So from my understanding, there are two stages happening here. One is that qry_cur_resource is 'filtered' for preferred information. Another is that qry_cur_pier takes this preferred information, which is now in a table called 'RE', and selects further preferred information. I'm still not sure why qry_cur_pier is needed, but I'll keep thinking about it.

My other concern is about where the RE table is created. I can't seem to find it anywhere in the database. Is it just temporary?

Thanks again, you've been great help.

 

[CajunCenturion]

Yes, I think you're on the right track. It's possible that you could eliminate one of the stages, but without knowing the big picture, I can't really say.

First of all, I'd find out what 'qry_cur_resource' actually does, or what is the SQL for that query. It is also possible that there are a dozen or so queries which all use 'qry_cur_resource' as a base. 'qry_cur_pier' is just one of them, which is based on RESOURCE_CODE, and who knows, others may be based on other attributes.

The RE table is probably just temporary, is built only when qry_cur_resource is executed. There is othing in what we've seen so far to indicate otherwise.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein