Hi,

I am trying to update a pivottablecache by using an ADO connection to my db, but when I run the macro, the db stays locked. I'm not getting any errors if I run the macro again. Don't know what I'm doing wrong.

Here's the code:

Dim RS As New ADODB.Recordset

Public Sub PvtCacheUpdate(ByRef WsCodeName As Worksheet, ByRef PvtTableName As String, _
                          ByRef ConnString As String, ByRef MyStoredProcedure As String)
Dim PT As PivotTable
On Error GoTo catch
Set RS = New ADODB.Recordset
Set PT = WsCodeName.PivotTables(PvtTableName)
Call RS.Open("[" & MyStoredProcedure & "]", ConnString, adOpenStatic, adLockReadOnly, adCmdTable)
Set PT.PivotCache.Recordset = RS
PT.RefreshTable
PT.PivotCache.MaintainConnection = False
catch:
If RS.State = ObjectStateEnum.adStateOpen Then RS.Close
Set RS = Nothing
Set PT = Nothing
End Sub

I have used the same RS.open for my 'insertrecord' 'updaterecord' and to fill a form with data from other queries. By these macros the db always closes correctly.
If someone has an idea, I would really appreciate it

Mike