Freeze in ADODB.Recordset.Open

[Jonathan Rynd]

In VBA (Excel), connecting to MS SQL Server

We have some code that creates an ADODB recordset. We set

Public glbConn As ADODB.Connection


Set glbConn = New ADODB.Connection
glbConn.Provider = "MSDASQL"
strConn = "DSN=" & kDBKMDSN & ";UID=" & kDBKMUser & ";PWD=" & _
kDBKMPass & ";"
glbConn.ConnectionString = strConn
glbConn.Open

Set mRS = New ADODB.Recordset
With mRS
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.ActiveConnection = glbConn
End With

and then in a loop we call

With mRS
.Source = strSQL
.Open
' more stuff
.Close
End With

The problem is that, on some computers but not others, the call to .Open
hangs -- it takes forever. The only way to recover is to break the
program (ctrl-break or esc), click debug, and then press F5. That makes
things start working again. Without doing that it hangs forever.

This hang does not take place when the code is stepped through using F8.

- How can I diagnose this? What other information would be helpful?

[Val Mazur]

Hi Jonathan,


I do not see any reason for this. Since it does not happen on all PCs, could
be that MDAC installation is corrupted. Try to run Component Checker utility
, which ships with MDAC to see if it is OK

. Other suggestions:

1. Use Set statement to point to the opened connection. If you do not do
this, then recordset will open new connection each time, when you open it

Set .ActiveConnection = glbConn


2. Use OLEDB provider for SQL Server, rather than OLEDB for ODBC. It would
improve performance
--
Val Mazur
Microsoft MVP

Post by Jonathan Rynd
In VBA (Excel), connecting to MS SQL Server
We have some code that creates an ADODB recordset. We set
Public glbConn As ADODB.Connection
Set glbConn = New ADODB.Connection
glbConn.Provider = "MSDASQL"
strConn = "DSN=" & kDBKMDSN & ";UID=" & kDBKMUser & ";PWD=" & _
kDBKMPass & ";"
glbConn.ConnectionString = strConn
glbConn.Open
Set mRS = New ADODB.Recordset
With mRS
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.ActiveConnection = glbConn
End With
and then in a loop we call
With mRS
.Source = strSQL
.Open
' more stuff
.Close
End With
The problem is that, on some computers but not others, the call to .Open
hangs -- it takes forever. The only way to recover is to break the
program (ctrl-break or esc), click debug, and then press F5. That makes
things start working again. Without doing that it hangs forever.
This hang does not take place when the code is stepped through using F8.
- How can I diagnose this? What other information would be helpful?

[Stephen Howe]

Post by Jonathan Rynd
The problem is that, on some computers but not others, the call to .Open
hangs -- it takes forever.

I don't have any answers Jonathan but I do have some suggestions (what I
would do was I there) :-)

1. On the computers that work and the others that fail, find out what
version of MDAC is on each and also Windows OS/Service Pack. It could be
that the ones that fail have a comon pattern. I would also check the state
of Connection caching in the registry. I would do this first, make a list of
the state of all machines and see if there is a common pattern to
success/fail.

2. Do you also start with

Dim mRS AS ADODB.Recordset

; recordset code in eample

and also

SET mRS = Nothing

when you have finished with the Recordset ?

3. For the machines where there is a successful Connect Open, I would do a
dump of the properties of the Connection Open (both before and after).
Frequently there are other dynamic properties that can be set that are
provider-specific.

Hope something here helps

Stephen Howe