VB6 ADO Updating Record

Hi, I'm quite new in VB6/ADO and I have encountered a problem in updating records. Below is my code:

Private Sub cmdEdit_Click()
Dim strMsg As String
Dim strDisplay As String
Dim recExist As Boolean
Dim blnPerLot As Boolean

Dim adoConnection As New ADODB.Connection
Dim rstProduct As New ADODB.Recordset

Set adoConnection = New ADODB.Connection
Set rstProduct = New ADODB.Recordset

adoConnection.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Persist Security Info=False;" & _
"Mode=Read|Write;" & _
"Data Source=C:\Program Files\" & _
"Microsoft Visual Studio\VB98\koinics\kafps.mdb"

adoConnection.CursorLocation = adUseClient
adoConnection.Open

rstProduct.CursorLocation = adUseClient
rstProduct.Open "Product", _
adoConnection, adOpenDynamic, _
adLockOptimistic, adCmdTable

'get all Product records...
Set rstProduct = adoConnection.Execute _
("SELECT * FROM Product ORDER BY ProductCode;")

'check if Product record exists before editing...
recExist = False
rstProduct.MoveFirst
Do Until rstProduct.EOF
If AreEqualStrings(rstProduct!ProductCode, txtProductCode) Then
recExist = True
Exit Do
End If
rstProduct.MoveNext
Loop 'Do Until rstProduct.EOF

If Not recExist Then
strDisplay = "Cannot edit, Product record must exists."
strMsg = MsgBox(strDisplay, vbExclamation, "Error Message")
txtProductCode.SetFocus 'Put focus on product code textbox
Exit Sub
End If

If recExist Then 'Product Code exists

rstProduct!ProductCode = txtProductCode
rstProduct!ProductDesc = txtProductDesc
rstProduct!PurchasePrice = txtPurchasePrice
rstProduct!PerLot = IIf(optPerLot, True, False)
rstProduct!ItemsPerLot = txtItemsPerLot
rstProduct.Update

strDisplay = "Product record " & txtProductCode & " updated."
strMsg = MsgBox(strDisplay, vbInformation, "F.Y.I.")
End If

'closing of object variables before
'exiting the procedure to release them...
If rstProduct.State = adStateOpen Then
rstProduct.Close
End If
If adoConnection.State = adStateOpen Then
adoConnection.Close
End If

'setting object variables to Nothing which releases
'the pointers and frees up the memory they consume...
Set rstProduct = Nothing
Set adoConnection = Nothing

End Sub 'cmdEdit_Click()

In debug mode, when the program executes the "rstProduct!ProductCode = txtProductCode" statement before the "rstProduct.Update", a message box indicating a "Run-time error '3251': Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype." appears.

I have tried to experiment with other codes suggested by books and web articles but to no success. Appreciate any help. Many thanks!

Nick

Make sure there is a primary key on the table.

Also make sure the .mdb file isn't set as "read-only" on the disk.

Hi Doug,

Thanks for your reply. The table was created with a primary key and is set in read/write mode. I'm able to perform edit (and all other functions) using the Add-Ins Visual Data Manager but couldn't crack how to do it using my program. Appreciate additional advise.

Thanks again.

shouldn't where you reference the fields in the database be written like this?

Same outcome mehere.

"Database fields can be accessed by their name using the RecordSet!FieldName schema, by their index RecordSet.Fields(intIndex) or sometimes by their name from the fields collection RecordSet.Fields("FieldName")." quote taken from www.timesheetsmts.com\adotutorial.htm

since i don't ever use the rs.Update statements ... i'm trying to see where the issue is, but why are you opening the recordset twice?


I'm just trying to see where the issue is.

the first open statement is to open it for update. the sql is to validate if the record (key) exists before the actual update.

any recommendation?

Try using the Jet 4.0 OLEDB provider in your connection string. Examples of most connection strings you might ever need can be found at www.able-consulting.com

Any luck with this?? I'm getting the same error. By the way, I'm using the Jet 4.0 OLEDB provider and it makes no difference.

The update code I have works great with SQL, unfortunately I have to use access in this case.

I suspect it's got something to do with the lock options that are part of the connection, but I don't know what.