Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 4th, 2006, 06:03 PM
Registered User
Join Date: Dec 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default can't unlock database


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.PivotCache.MaintainConnection = False
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


Similar Threads
Thread Thread Starter Forum Replies Last Post
lock/unlock row in GridView jung1975 ASP.NET 2.0 Basics 0 January 4th, 2006 06:42 PM
lock/unlock a site yanis97 J2EE 0 September 27th, 2005 07:05 AM
How to unlock a table? Edward King Oracle 1 March 16th, 2005 01:52 AM
Unlock Access Database socoolbrewster Access 1 September 28th, 2004 02:44 AM
How to unlock record in SQL Server database DDB001 SQL Server 2000 2 August 2nd, 2004 09:46 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.