Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Run-time error '3251':

ernieBMay 11 2011 — edited May 12 2011
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.

My Code Below:
{
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 9 2011
Added on May 11 2011
4 comments
287 views