We help IT Professionals succeed at work.

VBA application hangs up at a certain point

Rads R
Rads R asked
on
The VBA application hangs up at this point of line ..(bold,underline and italic)  and I got to explicitly kill the session
Do i need to change the recordset type and the type of lock ..can this solve the issue

Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
   rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

Let me know what the issue could be... would greatly appreciate your efforts
thanks
rds
Comment
Watch Question

Commented:
Do you have VBA debugger, if not try to find one in google
and you should have some code for error chekcing for your program that will
understand your error handle to see wheat error is , for example,


Sub AA()
On Error GoTo Err_Clear

Your code is herer....



Err_Clear:
If Err <> 0 Then
'MsgBox Err.Number & ": " & Err.Description
Err.Clear
.quit
End If

Author

Commented:
it is not even going to the err handler part .. it is just hanging up and does not respond
at all

Commented:
Hang up at the code, and could not stop running, RIght,

IFso, give me several minutes

Commented:
I don't have your program,probably you need to modifiy
these follows something like that but not exacly ..Hope it help
The might  be declaration issue

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String

Set cnn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
strSQL = "SELECT * from tblOne;"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

rst.Filter = "InvoicePrinted=False"
While Not rst.EOF
rst.Edit
rst!InvoicePrinted = True
rst.Update
rst.MoveNext
Wend


Commented:
Sounds like it could possibly be server-side.  Does this always happen, or is it sporadic?  Do you have access to the SQL Server's activity monitor?
Commented:
We don't understand what is that mean of "Sporaid".

If you answer and ask question, please write it simple and clearly  for international and interactive  help, otherwise people will stop reply you and could not help you right away.
From your question, it is  a part we can understand your problem

Author

Commented:
hi duncanb7:

i tried but the code you have written and what I have is almost the same and it is still not responding and it is hanging as usual ... !

Commented:
Radhs74,

Did you read my post?

Author

Commented:
I cannot check in the acitivity monitor as i do not have the privileges to do

Commented:
Does this happen with every query you run, or is it seemingly at random?

Author

Commented:
it is happening since recently for most of the queries when we have upgraded to sql server 2008

Author

Commented:
any suggestions  please?????

Commented:
I would get with your server administrator and look through at the logs and activity monitor to see what is happening on the server-side.  It could be a full hard disk or something completely un-related to your code.

I would also leave the query running overnight and see if maybe it finished running in the morning, or if it eventually gives you some sort of error message.

There is an issue called TCP/IP socket stress where if an application is opening a lot of connections in a short period of time, the server will start rejecting connections.  However, it doesn't sound like that's the issue here.

Author

Commented:
Torrwin

I have requested access to the activity monitor and they have given me the access .. can you tell me what should i check in the activity monitor and what should be discussed with the dba
Can you please explain in detail. Would greatly appreciate your help

thanks

Commented:
Sure, no problem.

Open the activity monitor and take a look at all of the current processes running.  You should be able to see the user, database, application, etc.

With the activity monitor on one screen/computer, then open your program on another screen/computer.  Assuming your program connects when opened, you should start seeing connections to your database in the monitor.  Filter by your database and watch the connections coming in as you do various things in your program.  If you don't see any connections then you know it's a connection problem.  If you see lots of connections and you're the only user, then it could be the socket stress issue I mentioned before.

Another possibility could be that you may have a connection that is locking records in a transaction.  All other queries referencing those records would still connect, but they would wait for the transaction to finish before executing.  If this is the case, kill the process that's in the transaction and your other queries should immediately execute.

Author

Commented:
ok will try and let you know

thanks
rads

Author

Commented:
Hi Torrwin

I tried connecting to the application and executed it and at the same time I have checked the Activity Monitor and it is showing the connection to that db and it shows the sql statement to be executed too
and it is taking too long to execute and just hangs up on me and does not respond anything in the window even if I want to kill the session. That means it is connecting to the db ... but the issue is something else and today I asked the DBA to delete the existing copy of the db and bring in a new copy of it thinking that there would have been issues with the moving from one server to another .. but that also did not server any purpose. Let me know if you have any other suggestions

Thanks
Rads

Commented:
If you run the SQL statement directly on the server does the same thing happen?

Commented:
Perhaps the database just needs to be tuned again after the upgrade.

Author

Commented:
no it just executes without any issue .. within a sec

Commented:
With the same credentials as the application is using?

Author

Commented:
yes

Commented:
So we've ruled out connectivity and security...are there any transactions going on against the database?  (in yours or another application)

Author

Commented:
The application worked fine when it was moved to another server

Author

Commented:
thanks