I think the problem is that Access does not recognise the TTOD() function. The event_date_time Access field is a datetime field, but I want to extract all records for a specific date. So when I use

CREATE SQL VIEW Temp_Agent_details CONNECTION arcview AS ;
SELECT *;
FROM Agent_details_001 ;
WHERE TTOD(Agent_details_001.event_date_time) = DTOC("06/06/2003")

and Use Temp_agent_details I get the error ODBC Connectivity Error undefined function TTOD in expression. If I use REQUERY("Temp_agent_details") or REQUERY("arc!Temp_agent_details") I get Alias not found even though I can see it in the database