Access Queries Running too Slow!

[deepgrewal22]

Overview:
I have coded modules and designed a form in Access which accepts user input and then passes on the input (via functions) to Access queries which ultimately return results from tables located on an Oracle9i database. The functions and form have worked very well on some queries and return results quickly. Certain other queries are not so quick to return results via a function and form.

Problem:
When the criteria is manually typed in the grid design view and the query is run, results appear within 5 seconds on all queries.

However, for certain queries, when a function (which obtains its value from user input from the form) is used in the criteria field, the hour glass icon becomes the mouse pointer and results are not returned. I have to Ctrl+Alt+Del to stop Access from running. These are the same queries which process results within seconds, when criteria is manually typed.

Sample Code:

Public EndDate As String
EndDate = txt_EndDate.Value
[COLOR=GREEN]'Let's just say EndDate = "6/22/2005"[/COLOR]
Call ED

Public Function ED()
     ED = CDate(EndDate)
End Function

In Access, the Criteria field for the respective attribute simply displays this:
ED()
and causes either very slow response time or Access freezes

If the Criteria field for the same respective attribute displays this:
[End Date:]
OR
#6/22/2005#
Results are returned immediately.

Any ideas why?


Deep Grewal
Don't say the M word (Microsoft); you sound ignorant.


Deep Grewal
Don't say the M word (Microsoft); you sound ignorant.

 

[dhookom]

The Access data engine will attempt to optimize your query as it is passed to Oracle. Since you used a function that Oracle doesn't understand, the processing must occur on the Access side rather than on the Oracle server where you want it.

I would use some code to modify the SQL of your query and possibly use a pass-through query to Oracle. Changing the SQL property is fairly straight forward with DAO.

CurrentDb.QueryDefs("YourQueryName").SQL = "SELECT...."

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[deepgrewal22]

dhookom,

Thanx for the reply. I considered that as well. I was able to finally find a quick fix:

Public EndDate As String
EndDate = txt_EndDate.Value
[COLOR=GREEN]'Let's just say EndDate = "6/22/2005"[/COLOR]
Call ED

Public Function ED()[COLOR=BLUE] As Date[/COLOR]
     ED = CDate(EndDate)
End Function

Adding the words "As Date" to the end of the public function makes the queries run at a reasonable and acceptable speed. What a difference being anal in your programming can make...

Deep Grewal
Don't say the M word (Microsoft); you sound ignorant.