I have an Excel based application reliant on several different databases which I connect to using ADO (Microsoft ActiveX Data Objects 6.1 Library). The databases reside on regional servers and there is an overhead in establishing the initial connection so I cache the connections in a Scripting.Dictionary object for reuse.
Private moConnCacheDict As Scripting.Dictionary
When I use the kdb+ qodbc.dll database drivers I get unexpected behavior. I can connect and reuse multiple data sources (Oracle,Sybase,Access) and one KDB database instance as expected. However, if I create a second KDB database connection and execute a query on the new dataset, no data is returned despite the fact the query is legitimate.
Recordset.BOF = TRUE and Recordset.EOF = TRUE
It appears to execute fine and the fields are visible. The connection to the previous regional server seems to persist and I can successfully retrieve data which resides on the original server despite the fact that if I look at,
Recordset.ActiveCommand.ActiveConnection.Properties.Item("Extended Properties")
, is the new connection string.
The KDB+ connection string uses the following syntax:
DRIVER=kdb+;DBQ=XXXXX;UID=XXXXX;PWD=XXXXX;
I have included the core VBA functions used as an example:
Private Function ExecuteQuery(sDBName As String, ByRef oRst As ADODB.Recordset, Optional bDeleteConnection As Boolean) As Boolean
Dim oDBConn As ADODB.Connection
Dim sSql As String
'delete connection
If bDeleteConnection Then Call DropConnection(sDBName)
'get cached or new connection
Call GetConnection(sDBName, oDBConn)
Select Case sDBName
Case "MAIN_US"
sSql = mSQL_MAIN
Case "MD_ASIA"
sSql = mSQL_MDASIA
End Select
Set oRst = New Recordset
oRst.Open sSql, oDBConn, adOpenKeyset, adLockPessimistic
If Not oDBConn.State = adStateOpen Then Err.Raise vbObjectError + 1024, "ExecuteQuery", sDBName & ": Recordset Closed. Unable to execute query ->" & sSql
ExecuteQuery = True
End Function
Private Function GetConnection(sDBName As String, ByRef oDBConn As ADODB.Connection) As Boolean
If moConnCacheDict Is Nothing Then Set moConnCacheDict = New Dictionary
If moConnCacheDict.Exists(sDBName) Then
'get existing connection
Set oDBConn = moConnCacheDict.Item(sDBName)
Else
'create connection
Set oDBConn = New Connection
With oDBConn
.Mode = adModeRead
Select Case sDBName
Case "MAIN_US"
.Mode = adModeReadWrite
.ConnectionString = mCONN_MAIN
Case "MD_ASIA"
.Mode = adModeRead
.ConnectionString = mCONN_MDASIA
End Select
.CursorLocation = adUseServer
.Open
End With
moConnCacheDict.Add sDBName, oDBConn
End If
GetConnection = True
End Function
Private Function DropConnection(Optional sDBName As String) As Boolean
Dim oDBConn As ADODB.Connection
Dim i As Integer
'delete object directly from cache
If Not moConnCacheDict Is Nothing Then
If sDBName = vbNullString Then
'close all connections
For i = 0 To moConnCacheDict.Count - 1
If Not IsEmpty(moConnCacheDict.Items(i)) Then
Set oDBConn = moConnCacheDict.Items(i)
If Not oDBConn Is Nothing Then
If oDBConn.State = adStateOpen Then oDBConn.Close
Set oDBConn = Nothing
Debug.Print Now, "Dropping Database Connection - " & moConnCacheDict.Keys(i)
End If
End If
Next i
Set moConnCacheDict = Nothing
Else
If moConnCacheDict.Exists(sDBName) Then
If Not IsEmpty(moConnCacheDict.Item(sDBName)) Then
Set oDBConn = moConnCacheDict.Item(sDBName)
If Not oDBConn Is Nothing Then
If oDBConn.State = adStateOpen Then oDBConn.Close
Set oDBConn = Nothing
Debug.Print Now, "Dropping Database Connection - " & "Dropping Database Connection - " & sDBName
End If
End If
moConnCacheDict.Remove (sDBName)
End If
End If
End If
DropConnection = True
End Function
(Note the ADO.Recordset is always closed and set to nothing by the caller).
The only way to resolve the issue is to close all database connections (regardless of the provider) and then reconnect to the desired regional server. This is horrendously inefficient as I have to reopen all the existing connections. Also note that it is not sufficient to do this purely in the current workbook. This must be done at the application level. If ANY ADO connections to ANY database are still open, I can create a new KDB+ ADO Connection but it will still point to the previous instance.
I have looked at the error properties of the KDB+ connection object and there are two errors:
- Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
- Provider does not support the property.
This appears to be documented in http://support.microsoft.com/kb/269495 but I am unable to locate any CLSID in the registry so am unable to experiment with the suggested change.
If I turn on ODBC logging I see the following message:
EXCEL 8dc-22d0 EXIT SQLGetInfoW with return code -1 (SQL_ERROR)
HDBC 0x02131EA8
UWORD 151 <SQL_KEYSET_CURSOR_ATTRIBUTES2>
PTR 0x003C4FB0
SWORD 4
SWORD * 0x00000000
DIAG [S1096] [Microsoft][ODBC Driver Manager] Information type out of range (0)
Would this be responsible for the error anyway?
As always, any help and suggestions would be much appreciated.
