Return PreStored Query Result into Recordset thourgh Excel

[jpstory]

Hi all

I am trying to run a prestored query in a Access database and popuate the result into a Recordset --- through Excel.
So far, I have managed to accomplish that using the code below.
However, now I need to update some records in the recordset. The code below does not allow me to do so. Please help!!

The following message prompted when the code tried to update the record:
Run-time error '3251'
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

This is written for other users who only knows how to create a query in Access. And the users prefer not to import the query results to spreadsheet.

*Someone suggested use Recordset.Open however, that seems to work only with SQL statement but not prestored Queries.

Sub RunExistingQuery()
'Execute prestored queries in Access
Dim Con As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim adoCmd As ADODB.Command
Dim qryName As String
Dim dbDir As String
Dim PC As PivotCache
Dim PT As PivotTable
 
    dbDir = "C:\Access Test\MyDatabase.accdb"
    qryName = "MyQuery"
    
    Set Con = New ADODB.Connection
    Set adoCmd = New ADODB.Command
    Set Rst = New ADODB.Recordset
    
    With Con
       .Provider = "Microsoft.ACE.OLEDB.12.0"
       .Open dbDir
    End With
    If Con.State = 0 Then
       Err.Raise 99, , "ADODB Connection object not successfully opened."
    End If
    
    With adoCmd
       .ActiveConnection = Con 'Command executing target
       .CommandType = adCmdStoredProc ' Stored Procedure is the Command Type
       .CommandText = qryName  ' Name of query
       Con.BeginTrans
       Con.CommitTrans
    End With
    Rst.CursorType = adOpenStatic
    Rst.LockType = adLockOptimistic
    Set Rst = adoCmd.Execute(qryName)
    
    ' Connection.Execute method creates a Forward-Only Read-Only recordset.
    ' Need to use Recorset.Open Method if I need to update the recods
    
    Rst.MoveFirst
    Do Until Rst.EOF
       Select Case Rst.Fields("Filed_A").Value
          Case "A"
             Rst.Fields("Field_B").Value = "New Value 1"  '------------------>> this is where the code got stock 1
             Rst.Update
          Case "B"
             Rst.Fields("Field_B").Value = "New Value 2"
             Rst.Update
       End Select
       Rst.MoveNext
    Loop
    
    Set PC = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal)
    With PC
       Set .Recordset = Rst
       Set PT = .CreatePivotTable(TableDestination:=Range("A4"), TableName:="MyPivot")
    End With
    
    Set adoCmd = Nothing
    Con.Close
    Set Con = Nothing
    Set PC = Nothing
    Set PT = Nothing
End Sub

 

[anvg]

Try with such a code

    Rst.CursorLocation = adUseClient
    Rst.Open qryName, Con, adOpenStatic, adLockOptimistic, adCmdStoredProc
    Rst.MoveFirst
    'And so on

Regards,

 

[jpstory]

Hi Anvg

Thanks, your suggestion worked and not worked. Apologies, I forgot to mention that the prestored queries are written for a linked table (linked to a external csv file). Your approach is definitely right.
I interpreted the concept of Recordset in the wrong way, I thought once data are loaded to a recodset object, they are copied/extracted thus are independent off the original data source.
Upon further search on the Internet, I am think about using a temp table (query table maybe?) to store the result of prestored queries instead of recordset.