code just hangs

  • Thread starter Thread starter patti
  • Start date Start date
P

patti

I have a report that has lots of stuff going on behind it. It ran fine weekly
til today.
I have compacted & repaired. I have also recalled an old copy of the db,
relinked tables, and tried to run report again - still hanging. "Access not
responding".


From strSQL(abridged version)

SELECT....fnGetPO([ON Order PO Info].[m1 po],[ON Order PO Info].[m2 PO],...
____________________________
this is the module for the fnGetPO (abridged version)

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
Else
If Not IsNull(po2) Then
strPO = po2
Else
If Not IsNull(po3) Then
strPO = po3
Else
If Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If
End If

End If
End If

fnGetPO = strPO
End Function
___________________________________________

Thanks to Roger Carlson ( MS Access MVP) i stepped through my code with f8.

i just loop and loop and loop through the function.

I am really confused since it was working the past few months. Is it an
issue with the code? Perhaps this week's data itself? Is anything obviously
wrong here? Gut feelings are always welcome too!
Thanks for the help.
 
Thanks for the help.

I am going through about 4000 records. Access just hangs - get a not
responding message. Tried on another computer - still hanging. This report
ran fine - took a minute or so but always popped up with data. I don't
believe i have that much more data than usual.

When i step through it w/ F8, does that go through every record? So, i
should just F8 until when? how will i know i hit a wall?

Marshall Barton said:
patti said:
I have a report that has lots of stuff going on behind it. It ran fine weekly
til today.
I have compacted & repaired. I have also recalled an old copy of the db,
relinked tables, and tried to run report again - still hanging. "Access not
responding".


From strSQL(abridged version)

SELECT....fnGetPO([ON Order PO Info].[m1 po],[ON Order PO Info].[m2 PO],...
____________________________
this is the module for the fnGetPO (abridged version)

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
Else
If Not IsNull(po2) Then
strPO = po2
Else
If Not IsNull(po3) Then
strPO = po3
Else
If Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If
End If

End If
End If

fnGetPO = strPO
End Function
___________________________________________

Thanks to Roger Carlson ( MS Access MVP) i stepped through my code with f8.

i just loop and loop and loop through the function.

I am really confused since it was working the past few months. Is it an
issue with the code? Perhaps this week's data itself? Is anything obviously
wrong here? Gut feelings are always welcome too!
Click to expand...


That function doesn't loop. Perhaps your query is returning
a lot of records?
Click to expand...
 
Don't know why things are hanging, but you could do that all with this
expression in your query

NZ([mi po],Nz([Mi po2],Nz([Mi po3], Nz([Mi po4],"None")))) as PoNum

The public function could also be rewritten to be less confusing. It
probably won't be any faster.

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
ElseIf Not IsNull(po2) Then
strPO = po2
ElseIf Not IsNull(po3) Then
strPO = po3
ElseIf Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If

End Function

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Thanks for the time & advice. This is an inherited db that's just a monster.

I ran this on yet another computer that was not connected to the network. I
copied the db and necessary files to my home laptop and my report ran fine.
So, can i conclude that there is a problem with the network?

John Spencer said:
Don't know why things are hanging, but you could do that all with this
expression in your query

NZ([mi po],Nz([Mi po2],Nz([Mi po3], Nz([Mi po4],"None")))) as PoNum

The public function could also be rewritten to be less confusing. It
probably won't be any faster.

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
ElseIf Not IsNull(po2) Then
strPO = po2
ElseIf Not IsNull(po3) Then
strPO = po3
ElseIf Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If

End Function

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a report that has lots of stuff going on behind it. It ran fine weekly
til today.
I have compacted & repaired. I have also recalled an old copy of the db,
relinked tables, and tried to run report again - still hanging. "Access not
responding".


From strSQL(abridged version)

SELECT....fnGetPO([ON Order PO Info].[m1 po],[ON Order PO Info].[m2 PO],...
____________________________
this is the module for the fnGetPO (abridged version)

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
Else
If Not IsNull(po2) Then
strPO = po2
Else
If Not IsNull(po3) Then
strPO = po3
Else
If Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If
End If

End If
End If

fnGetPO = strPO
End Function
___________________________________________

Thanks to Roger Carlson ( MS Access MVP) i stepped through my code with f8.

i just loop and loop and loop through the function.

I am really confused since it was working the past few months. Is it an
issue with the code? Perhaps this week's data itself? Is anything obviously
wrong here? Gut feelings are always welcome too!
Thanks for the help.
Click to expand...
Click to expand...
 
Perhaps. You changed two things - the computer you were using and
eliminating the network.
If you copy the db and files to your work(?) computer, do you see the same
improvement in speed? If so, then you could attribute the performance
problem to the network or you could be running into problems with anti-virus
software or some other factors.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

patti said:
Thanks for the time & advice. This is an inherited db that's just a
monster.

I ran this on yet another computer that was not connected to the network.
I
copied the db and necessary files to my home laptop and my report ran
fine.
So, can i conclude that there is a problem with the network?

John Spencer said:
Don't know why things are hanging, but you could do that all with this
expression in your query

NZ([mi po],Nz([Mi po2],Nz([Mi po3], Nz([Mi po4],"None")))) as PoNum

The public function could also be rewritten to be less confusing. It
probably won't be any faster.

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
ElseIf Not IsNull(po2) Then
strPO = po2
ElseIf Not IsNull(po3) Then
strPO = po3
ElseIf Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If

End Function

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a report that has lots of stuff going on behind it. It ran fine
weekly
til today.
I have compacted & repaired. I have also recalled an old copy of the
db,
relinked tables, and tried to run report again - still hanging. "Access
not
responding".


From strSQL(abridged version)

SELECT....fnGetPO([ON Order PO Info].[m1 po],[ON Order PO Info].[m2
PO],...
____________________________
this is the module for the fnGetPO (abridged version)

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
Else
If Not IsNull(po2) Then
strPO = po2
Else
If Not IsNull(po3) Then
strPO = po3
Else
If Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If
End If

End If
End If

fnGetPO = strPO
End Function
___________________________________________

Thanks to Roger Carlson ( MS Access MVP) i stepped through my code with
f8.

i just loop and loop and loop through the function.

I am really confused since it was working the past few months. Is it an
issue with the code? Perhaps this week's data itself? Is anything
obviously
wrong here? Gut feelings are always welcome too!
Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
I tried it on 2 different computers at the company - app just hung. Had been
working fine until yesterday.

I had planned on copying it locally today onto work pc.
Also, planned on checking w/ IT to see if they could monitor what is going
on while i tried it with db on network. It was the only db we had problems
with - but then it may be b/c of the complications of the report and its
code, queries, links, etc. But definitely asking IT to look into this. Odd
that just this one db would bomb.

Thanks for the help.

John Spencer said:
Perhaps. You changed two things - the computer you were using and
eliminating the network.
If you copy the db and files to your work(?) computer, do you see the same
improvement in speed? If so, then you could attribute the performance
problem to the network or you could be running into problems with anti-virus
software or some other factors.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

patti said:
Thanks for the time & advice. This is an inherited db that's just a
monster.

I ran this on yet another computer that was not connected to the network.
I
copied the db and necessary files to my home laptop and my report ran
fine.
So, can i conclude that there is a problem with the network?

John Spencer said:
Don't know why things are hanging, but you could do that all with this
expression in your query

NZ([mi po],Nz([Mi po2],Nz([Mi po3], Nz([Mi po4],"None")))) as PoNum

The public function could also be rewritten to be less confusing. It
probably won't be any faster.

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
ElseIf Not IsNull(po2) Then
strPO = po2
ElseIf Not IsNull(po3) Then
strPO = po3
ElseIf Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If

End Function

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


patti wrote:
I have a report that has lots of stuff going on behind it. It ran fine
weekly
til today.
I have compacted & repaired. I have also recalled an old copy of the
db,
relinked tables, and tried to run report again - still hanging. "Access
not
responding".


From strSQL(abridged version)

SELECT....fnGetPO([ON Order PO Info].[m1 po],[ON Order PO Info].[m2
PO],...
____________________________
this is the module for the fnGetPO (abridged version)

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
Else
If Not IsNull(po2) Then
strPO = po2
Else
If Not IsNull(po3) Then
strPO = po3
Else
If Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If
End If

End If
End If

fnGetPO = strPO
End Function
___________________________________________

Thanks to Roger Carlson ( MS Access MVP) i stepped through my code with
f8.

i just loop and loop and loop through the function.

I am really confused since it was working the past few months. Is it an
issue with the code? Perhaps this week's data itself? Is anything
obviously
wrong here? Gut feelings are always welcome too!
Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
Patti,

I'm having the same problem with code that works on local comptuters but
just hangs, or slows to a crawl, when the back end is on the network. Our
network people haven't been much help. Did you ever find a solution to your
problem? Our application was working just fine for over two years, and now
the slowdown.
--
Michelle


patti said:
I tried it on 2 different computers at the company - app just hung. Had been
working fine until yesterday.

I had planned on copying it locally today onto work pc.
Also, planned on checking w/ IT to see if they could monitor what is going
on while i tried it with db on network. It was the only db we had problems
with - but then it may be b/c of the complications of the report and its
code, queries, links, etc. But definitely asking IT to look into this. Odd
that just this one db would bomb.

Thanks for the help.

John Spencer said:
Perhaps. You changed two things - the computer you were using and
eliminating the network.
If you copy the db and files to your work(?) computer, do you see the same
improvement in speed? If so, then you could attribute the performance
problem to the network or you could be running into problems with anti-virus
software or some other factors.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

patti said:
Thanks for the time & advice. This is an inherited db that's just a
monster.

I ran this on yet another computer that was not connected to the network.
I
copied the db and necessary files to my home laptop and my report ran
fine.
So, can i conclude that there is a problem with the network?

:

Don't know why things are hanging, but you could do that all with this
expression in your query

NZ([mi po],Nz([Mi po2],Nz([Mi po3], Nz([Mi po4],"None")))) as PoNum

The public function could also be rewritten to be less confusing. It
probably won't be any faster.

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
ElseIf Not IsNull(po2) Then
strPO = po2
ElseIf Not IsNull(po3) Then
strPO = po3
ElseIf Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If

End Function

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


patti wrote:
I have a report that has lots of stuff going on behind it. It ran fine
weekly
til today.
I have compacted & repaired. I have also recalled an old copy of the
db,
relinked tables, and tried to run report again - still hanging. "Access
not
responding".


From strSQL(abridged version)

SELECT....fnGetPO([ON Order PO Info].[m1 po],[ON Order PO Info].[m2
PO],...
____________________________
this is the module for the fnGetPO (abridged version)

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
Else
If Not IsNull(po2) Then
strPO = po2
Else
If Not IsNull(po3) Then
strPO = po3
Else
If Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If
End If

End If
End If

fnGetPO = strPO
End Function
___________________________________________

Thanks to Roger Carlson ( MS Access MVP) i stepped through my code with
f8.

i just loop and loop and loop through the function.

I am really confused since it was working the past few months. Is it an
issue with the code? Perhaps this week's data itself? Is anything
obviously
wrong here? Gut feelings are always welcome too!
Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
Hi Michelle,

When i returned to the office the next day, everything ran fine. I
definitely would say i had a network connection issue, though IT coudn'l say
for sure. Ask your IT to monitor the connection. (Not really sure how they do
that, but i would hope your network people know).

If that doesn't work, and you try the other suggestions here, i'd say repost
so you don't get lost in this thread.

good luck.

patti


Tigerfish said:
Patti,

I'm having the same problem with code that works on local comptuters but
just hangs, or slows to a crawl, when the back end is on the network. Our
network people haven't been much help. Did you ever find a solution to your
problem? Our application was working just fine for over two years, and now
the slowdown.
--
Michelle


patti said:
I tried it on 2 different computers at the company - app just hung. Had been
working fine until yesterday.

I had planned on copying it locally today onto work pc.
Also, planned on checking w/ IT to see if they could monitor what is going
on while i tried it with db on network. It was the only db we had problems
with - but then it may be b/c of the complications of the report and its
code, queries, links, etc. But definitely asking IT to look into this. Odd
that just this one db would bomb.

Thanks for the help.

John Spencer said:
Perhaps. You changed two things - the computer you were using and
eliminating the network.
If you copy the db and files to your work(?) computer, do you see the same
improvement in speed? If so, then you could attribute the performance
problem to the network or you could be running into problems with anti-virus
software or some other factors.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for the time & advice. This is an inherited db that's just a
monster.

I ran this on yet another computer that was not connected to the network.
I
copied the db and necessary files to my home laptop and my report ran
fine.
So, can i conclude that there is a problem with the network?

:

Don't know why things are hanging, but you could do that all with this
expression in your query

NZ([mi po],Nz([Mi po2],Nz([Mi po3], Nz([Mi po4],"None")))) as PoNum

The public function could also be rewritten to be less confusing. It
probably won't be any faster.

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
ElseIf Not IsNull(po2) Then
strPO = po2
ElseIf Not IsNull(po3) Then
strPO = po3
ElseIf Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If

End Function

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


patti wrote:
I have a report that has lots of stuff going on behind it. It ran fine
weekly
til today.
I have compacted & repaired. I have also recalled an old copy of the
db,
relinked tables, and tried to run report again - still hanging. "Access
not
responding".


From strSQL(abridged version)

SELECT....fnGetPO([ON Order PO Info].[m1 po],[ON Order PO Info].[m2
PO],...
____________________________
this is the module for the fnGetPO (abridged version)

Public Function fnGetPO(po1, po2, po3, po4) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
Else
If Not IsNull(po2) Then
strPO = po2
Else
If Not IsNull(po3) Then
strPO = po3
Else
If Not IsNull(po4) Then
strPO = po4
Else
strPO = "None"
End If
End If

End If
End If

fnGetPO = strPO
End Function
___________________________________________

Thanks to Roger Carlson ( MS Access MVP) i stepped through my code with
f8.

i just loop and loop and loop through the function.

I am really confused since it was working the past few months. Is it an
issue with the code? Perhaps this week's data itself? Is anything
obviously
wrong here? Gut feelings are always welcome too!
Thanks for the help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top