Trying to get old code to work - Getting error 3251

Question

This is several year old code I am reviving. It used to work, but now I get he error "Current Recordset does not support updating."  The error occurs at .Addnew 

'Global_Information Module contents:
Option Compare Database
Option Explicit
Public Const ConnectString = "DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=127.0.0.1;DATABASE=mydb;USER=root;PWD=secret;OPTION=3"
'Form Class update contents:
Public Sub UpdateTables()
  Dim Cnn As ADODB.Connection
  Dim Rst As ADODB.Recordset
  Dim Rst1 As ADODB.Recordset
  Dim Cnt As Integer
  Dim PkgLink As Long
  Dim Cancel As Integer
  
  Cnt = 0
  Set Cnn = New ADODB.Connection
  Cnn.Open ConnectString
  
  Set Rst = New ADODB.Recordset
  
  With Rst
    If RecordFound = True Then
      .CursorType = adOpenDynamic
      .LockType = adLockOptimistic
      .Open "SELECT * FROM Main_Table WHERE Link_Value = " & LinkVal, Cnn, , , adCmdText
      !Total_Weight = !Total_Weight + (NumPkgs * PkgWt)
      .Update
    Else
      .CursorType = adOpenDynamic
      .LockType = adLockPessimistic
      .Open "Main_Table", Cnn, , , adCmdTableDirect
      .AddNew
      !Material_ID_Number = MIDNum
      !Product_Name = UCase(ProdName)
      !SQR_Number = SQRNum
      !Package_Code = PkgCde
      !Package_Weight = PkgWt
      !Material_Code = MatCde
      !Lot_Number = UCase(LotNum)
      !Total_Weight = (NumPkgs * PkgWt)
      !Link_Value = LinkVal
      .Update
    End If
    .Close
  End With

Can anybody help me repair this? I have found this topic all over the place, but it seems everything I try just causes different errors. I have been away from coding so long, that I think I have rust in my brain.

Answer 1

'Option Compare Database
'Option Explicit

Why do you have these commented out? That is a really bad idea.

My guess is the table does not have a Primary Key. That would make it not updatable.

---

-Tom. Microsoft Access MVP

Answer 2

  
  With Rst
    If RecordFound = True Then
      .CursorType = adOpenDynamic
      ...

Hi Wilson,

In this way RecordFound is used as a variable, and not as a property.

You could use:

    If .RecordFound = True Then

Imb.

Answer 3

They aren't in my code. I commented them to show that they are in a module and not in the form Class. I see how that was misleading, so I edited my post to show it. Thanks for the quick head's up!

I still have my original problem.

Answer 4

That change throws an error. Are you saying that the fact that I use RecordFound as a declared boolean variable in my earlier parts of my form, that I am getting the 3251 error from the OP or are you telling my that defining a new property and using it is better practice? 

Thanks for your quick response.

I still have my original problem.

Answer 5

That change throws an error. Are you saying that the fact that I use RecordFound as a declared boolean variable in my earlier parts of my form, that I am getting the 3251 error from the OP or are you telling my that defining a new property and using it is better practice? 

Hi Wilson,

I was a little confused, I think. I could not see a declaration of RecordFound, and assumed it was a new property introduced after A2003.

Imb.