Run-time error '3251':

[pape93]

Hi All,

I'm getting an error with my code below.
I'm trying to find out if any records exist, if they do, edit the record. if not insert new record. but i'm getting the error message:

Run-time error '3251':
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

Sub StoreTarDscOrPrdIDNext()
   Dim conn As ADODB.Connection
   Dim prdDsc, prdID As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\eps.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set prdDsc = conn.Execute("SELECT hs_cod FROM hs_log where prd_dsc = '" + LCase(txtDesc.value) + "'")
   Set prdID = conn.Execute("SELECT DISTINCT hs_cod FROM hs_log where prd_id = '" + LCase(cboProductID.value) + "'")
 
'On Error GoTo ErrorHandler
 storTarDscPrd = Application.WorksheetFunction.VLookup("storTarDscPrd", Worksheets("config").Range("B:C"), 2)
'If storTarDscPrd = "1" Then
 'store tariff and description
   If Not (prdDsc.BOF And prdDsc.EOF) Then 'There are no records if Beginning-Of-File and End-Of-File are both true.
   'if found Ask to Edit record
       EditTar = MsgBox(Prompt:="Edit record?", Buttons:=vbYesNo, Title:="ePrisoft")
       If EditTar = vbYes Then
         prdDsc("hs_cod") = Trim(cboTariffNo2.value)
         prdDsc.Update
         'Set prdDsc = conn.Execute("UPDATE hs_log SET hs_cod = '" + Trim(cboTariffNo2.value) + "' WHERE prd_dsc = '" + LCase(Trim(txtDesc.value)) + "'")
       End If
       MsgBox "Edit"
   Else
   'else insert new record
        prdDsc.AddNew
        prdDsc("prd_dsc") = LCase(Trim(txtDesc.value))
        prdDsc("hs_cod") = Trim(cboTariffNo2.value)
        prdDsc.Update
        'sql = "INSERT INTO hs_log (prd_dsc, hs_cod) VALUES '" + LCase(Trim(txtDesc.value)) + "','" + Trim(cboTariffNo2.value) + "'"
       'Set prdDsc = conn.Execute("INSERT INTO hs_log (prd_dsc, hs_cod) VALUES '" + LCase(Trim(txtDesc.value)) + "','" + Trim(cboTariffNo2.value) + "'")
    MsgBox "Insert"
   End If
'ElseIf storTarDscPrd = "2" Then
 'store tariff and productid
 
'Else
 'dont store tariff
'End If
'ErrorHandler:
'Err.Clear
'Resume Next
   prdDsc.Close
   Set prdDsc = Nothing
   prdID.Close
   Set prdID = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Any help would be appreciated

Thanks

 

[RoryA]

Instead of using the Execute method, use something like:

   Set prdDsc = New ADODB.Recordset
   prddesc.open "SELECT hs_cod FROM hs_log where prd_dsc = '" + LCase(txtDesc.value) + "'", conn, adopendynamic, adlockoptimistic, adcmdtext

   Set prdID = New ADODB.Recordset
prdID.Open "SELECT DISTINCT hs_cod FROM hs_log where prd_id = '" + LCase(cboProductID.value) + "'", conn, adopendynamic, adlockoptimistic, adcmdtext

 

[gsbelbin]

I agree with rorya but the other thing that might be causing problems is the way you've declared prdDsc. I believe that the way you've declared it will result in it being a Variant rather than an ADODB.Recordset. This might not be an issue but better to eliminate the possibility by declaring it explicitly.