Recordset runtime error 3251 (1 Viewer)

Rank Am

Registered User.
Local time
Tomorrow, 05:26
Joined
Apr 30, 2005
Messages
68
Can anyone help - I cannot seem to get the following ADO recordset to allow updates. I keep getting a run time error stating that the recordset does not allow updating. The recordset opens fine and I have tried changing the cursor types and lock types to no avail

The code is as follows:
Code:
Private Sub cmdUpdateDB_Click()
Dim rs As ADODB.Recordset
Dim cm As ADODB.Command
Dim intLneNo As Integer
Dim sglDia As Single
Dim strPID As String
strPID = Me.lstPID
sglDia = Me.cboDiaPk
intLneNo = Me.cboPipLop
Set cm = New ADODB.Command
With cm
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "AdoQryUpdt_PID_PIP"
    .CommandType = adCmdTable
    .Parameters.Refresh
    .Parameters("[Param1]") = strPID
    .Parameters("[Param2]") = intLneNo
    .Parameters("[Param3]") = sglDia
End With
Set rs = New ADODB.Recordset
With rs
    .CursorType = adOpenKeyset
    .LockType = adLockPessimistic
    .CursorLocation = adUseServer
End With
Set rs = cm.Execute
If rs.EOF = True Or rs.BOF = True Then
rs.AddNew
rs!fldPIDPk = strPID
rs!fldLneNoPK = intLneNo
rs!fldDiaPk = sglDia
rs.Save
Else
MsgBox "This record exists!", vbOKOnly, "Data Clash"
End If
rs.Close
Set rs = Nothing
Set cm = Nothing
Set cm = Nothing
End Sub
The query is just a straight select statement with parameters from a table that allows updating

Regards

Jon
 

Moniker

VBA Pro
Local time
Today, 16:26
Joined
Dec 21, 2006
Messages
1,567
Look at this checklist -- see response #3 -- and see if your query meets any of those parameters. (Ironically, I just posted that one before reading this one.)
 

Rank Am

Registered User.
Local time
Tomorrow, 05:26
Joined
Apr 30, 2005
Messages
68
Here is the SELECT statement

Thanks but the query is a straight forward paramaterised SELECT statement and is updateable
Code:
SELECT tblPIDPip.fldPIDPk, tblPIDPip.fldPipLopPk, tblPIDPip.fldDiaPk
FROM tblPIDPip
WHERE (((tblPIDPip.fldPIDPk)=[Param1]) AND ((tblPIDPip.fldPipLopPk)=[Param2]) AND ((tblPIDPip.fldDiaPk)=[Param3]));

I am using ACC2007 as my new laptop came with it preinstalled - I am wondering whether there is some new Security irritation that MS has included and not documented which is causing this. Some setting in Access which needs to be checked allowing jet to be updated by ADO recordsets. I can see no problem with the code
I am having the same issue with recordsets opened using the Open statement not only through command objects anyone else had similar issues

Regards

Jon
 

Moniker

VBA Pro
Local time
Today, 16:26
Joined
Dec 21, 2006
Messages
1,567
I know you said you tried this, but using a Cursor of adOpenDynamic and a Lock of adLockOptimisic doesn't work? Perhaps I'm not seeing something.
 

Rank Am

Registered User.
Local time
Tomorrow, 05:26
Joined
Apr 30, 2005
Messages
68
Thanks but no combination of lock types or cursor allow the recordset to be updateable - I can't see any issues in the connection string either as mode is set share deny none.

Regards

Jon
 

Rank Am

Registered User.
Local time
Tomorrow, 05:26
Joined
Apr 30, 2005
Messages
68
When I went through rs properties with the debugger the opened recordset properties were set to value 1 adLockReadOnly and not 3 adLockOptimistic as specified in the with statement so this is the problem - strange and I have no idea why this happening
 

Users who are viewing this thread

Top Bottom