Why does a VBA function call from a sub-query cause an infinite loop? | | |
I have an SQL Query that looks like this. I simplified it for the
purpose of this example, but it's still a runnable query
SELECT TOP 1
FundedPositions.PositionNumber AS [Position],
(select top 1
assign_i
from dbo_assign a
where a.scenario_i=1
and a.assign_i = TestMyFunction(FundedPositions.PositionNumber))AS
EmployeeName
FROM FundedPositions
As you can see, it should return one record, and uses a subquery to get
the EmployeeName column. In the subquery, I call a VBA Function,
called TestMyFunction. The function contains one paramater, which I
pass from the outer query and returns the value that I will join with
a.assign_i. Here is what the function looks like, simplified again for
this example:
Public Function TestMyFunction(PositionNumber As String)
TestMyFunction = "1234"
End Function
For some reason, the function is called infinitely, making everything
hang. Why is this happening? When I put a braekpoint at the start of
the function, I see it go through once with the right value being
passed, but then it goes through again ad-nauseum, with the same value.
The problem doesn't seem to be with the query, since I tried replacing
the function call with a hard-coded value, and everything is fine.
The problem doesn't seem to be with the function either, since if I
pass a hardcoded value in the function call, everything is fine. For
example, the example below is fine (notice how I pass '5555'):
SELECT TOP 1
FundedPositions.PositionNumber AS [Position],
(select top 1
assign_i
from dbo_assign a
where a.scenario_i=1
and a.assign_i = TestMyFunction('55555'))AS EmployeeName
FROM FundedPositions
Can anyone help me? I can provide table descriptions if you want, but
I don't think the tables are the matter here.
Thanks in advance,
Marc
| | | | re: Why does a VBA function call from a sub-query cause an infinite loop?
Is it really an infinite loop?
How many records are there in [dbo_assign a] ?
My guess is that the function is being called that many times and will
complete. When all the records have been read the the query will return the
"TOP 1", but not before.
Apologies if I am way off beam.
"marcsirois" <marcsirois@gmail.comwrote in message
news:1165005547.142806.136880@j72g2000cwa.googlegr oups.com... Quote:
>I have an SQL Query that looks like this. I simplified it for the
purpose of this example, but it's still a runnable query
>
SELECT TOP 1
FundedPositions.PositionNumber AS [Position],
(select top 1
assign_i
from dbo_assign a
where a.scenario_i=1
and a.assign_i = TestMyFunction(FundedPositions.PositionNumber))AS
EmployeeName
FROM FundedPositions
>
As you can see, it should return one record, and uses a subquery to get
the EmployeeName column. In the subquery, I call a VBA Function,
called TestMyFunction. The function contains one paramater, which I
pass from the outer query and returns the value that I will join with
a.assign_i. Here is what the function looks like, simplified again for
this example:
>
Public Function TestMyFunction(PositionNumber As String)
>
TestMyFunction = "1234"
>
End Function
>
For some reason, the function is called infinitely, making everything
hang. Why is this happening? When I put a braekpoint at the start of
the function, I see it go through once with the right value being
passed, but then it goes through again ad-nauseum, with the same value.
>
The problem doesn't seem to be with the query, since I tried replacing
the function call with a hard-coded value, and everything is fine.
>
The problem doesn't seem to be with the function either, since if I
pass a hardcoded value in the function call, everything is fine. For
example, the example below is fine (notice how I pass '5555'):
>
SELECT TOP 1
FundedPositions.PositionNumber AS [Position],
(select top 1
assign_i
from dbo_assign a
where a.scenario_i=1
and a.assign_i = TestMyFunction('55555'))AS EmployeeName
FROM FundedPositions
>
Can anyone help me? I can provide table descriptions if you want, but
I don't think the tables are the matter here.
>
Thanks in advance,
>
Marc
>
>
| | | | re: Why does a VBA function call from a sub-query cause an infinite loop?
On 1 Dec 2006 12:39:07 -0800, "marcsirois" <marcsirois@gmail.com>
wrote:
David is right: most likely your function is called MANY times, and it
takes a while to process.
Then also realize that Access is smart enough to realize if a function
is returning a constant value like in your simplified example. It then
only calls your function once, and caches the "1234" result for all
rows.
Also realize that if the function argument is constant, Access again
assumes with a constant argument the function couldn't possibly be
returning diffent values every time (even though this is a WRONG
assumption, e.g. if the function returns Now()), and it caches the
first return value. To overcome this, additionally pass in the PK.
Do optimize your function for speed. For example if it is opening a
recordset, perhaps you can open this rs BEFORE you call this
procedure, and close it after it ends. Also don't use an error handler
which can be a real performance drag. Rather write perfect code :-)
-Tom. Quote:
>I have an SQL Query that looks like this. I simplified it for the
>purpose of this example, but it's still a runnable query
>
>SELECT TOP 1
>FundedPositions.PositionNumber AS [Position],
(select top 1
assign_i
from dbo_assign a
where a.scenario_i=1
and a.assign_i = TestMyFunction(FundedPositions.PositionNumber))AS
>EmployeeName
>FROM FundedPositions
>
>As you can see, it should return one record, and uses a subquery to get
>the EmployeeName column. In the subquery, I call a VBA Function,
>called TestMyFunction. The function contains one paramater, which I
>pass from the outer query and returns the value that I will join with
>a.assign_i. Here is what the function looks like, simplified again for
>this example:
>
>Public Function TestMyFunction(PositionNumber As String)
>
TestMyFunction = "1234"
>
>End Function
>
>For some reason, the function is called infinitely, making everything
>hang. Why is this happening? When I put a braekpoint at the start of
>the function, I see it go through once with the right value being
>passed, but then it goes through again ad-nauseum, with the same value.
>
>The problem doesn't seem to be with the query, since I tried replacing
>the function call with a hard-coded value, and everything is fine.
>
>The problem doesn't seem to be with the function either, since if I
>pass a hardcoded value in the function call, everything is fine. For
>example, the example below is fine (notice how I pass '5555'):
>
>SELECT TOP 1
>FundedPositions.PositionNumber AS [Position],
(select top 1
assign_i
from dbo_assign a
where a.scenario_i=1
and a.assign_i = TestMyFunction('55555'))AS EmployeeName
>FROM FundedPositions
>
>Can anyone help me? I can provide table descriptions if you want, but
>I don't think the tables are the matter here.
>
>Thanks in advance,
>
>Marc
| | | | re: Why does a VBA function call from a sub-query cause an infinite loop?
For one thing, the "dbo_" indicates your records are on SQL Server. If the
function is a VBA function in Access, you may be forcing every record to be
retrieved and processed locally on the client to apply the function rather
than the entire SELECT operation being done on the server, with only 1
record being returned -- SQL Server cannot execute your Access VBA functions
for you.
Further, I do not know what effect your use of the subquery has on
performance, but my suspicion is that it would have a negative effect.
Larry Linson
Microsoft Access MVP
"marcsirois" <marcsirois@gmail.comwrote in message
news:1165005547.142806.136880@j72g2000cwa.googlegr oups.com... Quote:
>I have an SQL Query that looks like this. I simplified it for the
purpose of this example, but it's still a runnable query
>
SELECT TOP 1
FundedPositions.PositionNumber AS [Position],
(select top 1
assign_i
from dbo_assign a
where a.scenario_i=1
and a.assign_i = TestMyFunction(FundedPositions.PositionNumber))AS
EmployeeName
FROM FundedPositions
>
As you can see, it should return one record, and uses a subquery to get
the EmployeeName column. In the subquery, I call a VBA Function,
called TestMyFunction. The function contains one paramater, which I
pass from the outer query and returns the value that I will join with
a.assign_i. Here is what the function looks like, simplified again for
this example:
>
Public Function TestMyFunction(PositionNumber As String)
>
TestMyFunction = "1234"
>
End Function
>
For some reason, the function is called infinitely, making everything
hang. Why is this happening? When I put a braekpoint at the start of
the function, I see it go through once with the right value being
passed, but then it goes through again ad-nauseum, with the same value.
>
The problem doesn't seem to be with the query, since I tried replacing
the function call with a hard-coded value, and everything is fine.
>
The problem doesn't seem to be with the function either, since if I
pass a hardcoded value in the function call, everything is fine. For
example, the example below is fine (notice how I pass '5555'):
>
SELECT TOP 1
FundedPositions.PositionNumber AS [Position],
(select top 1
assign_i
from dbo_assign a
where a.scenario_i=1
and a.assign_i = TestMyFunction('55555'))AS EmployeeName
FROM FundedPositions
>
Can anyone help me? I can provide table descriptions if you want, but
I don't think the tables are the matter here.
>
Thanks in advance,
>
Marc
>
| | | | re: Why does a VBA function call from a sub-query cause an infinite loop?
Thanks for your insightful replies David, Tom and Larry.
What you are saying makes sense. This is why I will try to find
another approach to solving my problem, without using a VBA function.
As an MS Access newbie, I find the flexibility very limited. In SQL
Server, I could have solved my problem with a simple cursor inside a
stored procedure.
Thanks again for your help.
Marc
marcsirois wrote: Quote:
I have an SQL Query that looks like this. I simplified it for the
purpose of this example, but it's still a runnable query
>
SELECT TOP 1
FundedPositions.PositionNumber AS [Position],
(select top 1
assign_i
from dbo_assign a
where a.scenario_i=1
and a.assign_i = TestMyFunction(FundedPositions.PositionNumber))AS
EmployeeName
FROM FundedPositions
>
As you can see, it should return one record, and uses a subquery to get
the EmployeeName column. In the subquery, I call a VBA Function,
called TestMyFunction. The function contains one paramater, which I
pass from the outer query and returns the value that I will join with
a.assign_i. Here is what the function looks like, simplified again for
this example:
>
Public Function TestMyFunction(PositionNumber As String)
>
TestMyFunction = "1234"
>
End Function
>
For some reason, the function is called infinitely, making everything
hang. Why is this happening? When I put a braekpoint at the start of
the function, I see it go through once with the right value being
passed, but then it goes through again ad-nauseum, with the same value.
>
The problem doesn't seem to be with the query, since I tried replacing
the function call with a hard-coded value, and everything is fine.
>
The problem doesn't seem to be with the function either, since if I
pass a hardcoded value in the function call, everything is fine. For
example, the example below is fine (notice how I pass '5555'):
>
SELECT TOP 1
FundedPositions.PositionNumber AS [Position],
(select top 1
assign_i
from dbo_assign a
where a.scenario_i=1
and a.assign_i = TestMyFunction('55555'))AS EmployeeName
FROM FundedPositions
>
Can anyone help me? I can provide table descriptions if you want, but
I don't think the tables are the matter here.
>
Thanks in advance,
>
Marc
| | | | re: Why does a VBA function call from a sub-query cause an infinite loop?
As an MS Access newbie, I find the flexibility very limited....
Just perhaps that could be translated into "I do not know how to do this in
Access".
If you look through the Access newsgroups you will find practically every
manipulation you could imagine done to names without recourse to custom VBA
functions.
What problem were you trying to solve?
"marcsirois" <marcsirois@gmail.comwrote in message
news:1165242829.247572.277740@j72g2000cwa.googlegr oups.com... Quote:
Thanks for your insightful replies David, Tom and Larry.
What you are saying makes sense. This is why I will try to find
another approach to solving my problem, without using a VBA function.
As an MS Access newbie, I find the flexibility very limited. In SQL
Server, I could have solved my problem with a simple cursor inside a
stored procedure.
>
Thanks again for your help.
Marc
>
>
marcsirois wrote: Quote:
>I have an SQL Query that looks like this. I simplified it for the
>purpose of this example, but it's still a runnable query
>>
>SELECT TOP 1
>FundedPositions.PositionNumber AS [Position],
> (select top 1
> assign_i
> from dbo_assign a
> where a.scenario_i=1
> and a.assign_i = TestMyFunction(FundedPositions.PositionNumber))AS
>EmployeeName
>FROM FundedPositions
>>
>As you can see, it should return one record, and uses a subquery to get
>the EmployeeName column. In the subquery, I call a VBA Function,
>called TestMyFunction. The function contains one paramater, which I
>pass from the outer query and returns the value that I will join with
>a.assign_i. Here is what the function looks like, simplified again for
>this example:
>>
>Public Function TestMyFunction(PositionNumber As String)
>>
> TestMyFunction = "1234"
>>
>End Function
>>
>For some reason, the function is called infinitely, making everything
>hang. Why is this happening? When I put a braekpoint at the start of
>the function, I see it go through once with the right value being
>passed, but then it goes through again ad-nauseum, with the same value.
>>
>The problem doesn't seem to be with the query, since I tried replacing
>the function call with a hard-coded value, and everything is fine.
>>
>The problem doesn't seem to be with the function either, since if I
>pass a hardcoded value in the function call, everything is fine. For
>example, the example below is fine (notice how I pass '5555'):
>>
>SELECT TOP 1
>FundedPositions.PositionNumber AS [Position],
> (select top 1
> assign_i
> from dbo_assign a
> where a.scenario_i=1
> and a.assign_i = TestMyFunction('55555'))AS EmployeeName
>FROM FundedPositions
>>
>Can anyone help me? I can provide table descriptions if you want, but
>I don't think the tables are the matter here.
>>
>Thanks in advance,
>>
>Marc
>
>
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,223 network members.
|
