Why does a VBA function call from a sub-query cause an infinite loop?

[marcsirois]

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

[David F Cox]

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" <ma********@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...

>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

[Tom van Stiphout]

On 1 Dec 2006 12:39:07 -0800, "marcsirois" <ma********@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.

>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

[Larry Linson]

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" <ma********@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...

>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

[marcsirois]

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:

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

[David F Cox]

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" <ma********@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...

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:

>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