Runtime Error 3251 Updating not allowed

[akoymakoy]

Run time error 3251 Current Recordset does not support updating, this may be a limitation of the provider, or of the selected Locktype


This is my simple program that will split the entries that have 2 words in it and put it in separate fields:
im using MSaccess for my tables

for example:
before
field1= word1, word2

after:
field1 = word1
field2= word2

Private Sub Command1_Click()
        Set MyConn = New ADODB.Connection
        MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Thesis\testing\comma\FilipinoDict.mdb;"
        MyConn.Open

        Set MyRecSet = MyConn.Execute("SELECT TagWord, EngWord, POS FROM Dictionary ORDER BY TagWord")
        'if myrecset.Fields(0) =
        'for ctr=0 to 50
        ctr = 0
        Do Until MyRecSet.EOF
        
                
        InputSentence.Text = MyRecSet.Fields(0).Value
        wordset = Split(InputSentence.Text, ", ")
        
        If UBound(wordset) > 0 Then
        englishword = MyRecSet.Fields(1)
        For ctr2 = 0 To UBound(wordset)
       
        MyRecSet.Fields(0).Value = wordset(ctr2)

' THE PART WHERE I AM SUPPOSED TO DO THE UPDATING AND ADDING
        
        Next ctr2
        
        End If
        MyRecSet.MoveNext
        Loop
        MyConn.Close

[Killer42]

Before you can change field values, you need to issue an Edit against the record. Then you need to do an Update on it when finished modifying the fields. I didn't read the code carefully though, so may have missed them. Or are they supposed to be in the code where you have the "UPDATING STUFF HERE" type comment?

Which specific statement produces the error?

[willakawill]

Hi, Thanks for posting the code, it helps a lot.
Firstly you might take a shot at changing some of your coding habits. This will help greatly in debugging your code.

When you are accessing recordset fields it is important to identify the field with something other than 0, 1 and 2. You will always have to check back to see what they mean.

Dim TagWord As Integer
Dim EngWord As Integer
Dim POS As Integer

TagWord = 0
EngWord = 1
POS = 2

Then
InputSentence.Text = MyRecSet.Fields(0).Value

can read as

InputSentence.Text = MyRecSet(TagWord)
englishword = MyRecSet(EngWord)

With MyRecordset
        .ActiveConnection = MyConn
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Source = "SELECT TagWord, EngWord, POS FROM Dictionary ORDER BY TagWord"
        .Open
End With

Using a recordset this way allows you to set the properties so that you can be sure to be allowed to update it.

[Killer42]

...
When you are accessing recordset fields it is important to identify the field with something other than 0, 1 and 2. You will always have to check back to see what they mean.
...
TagWord = 0
InputSentence.T ext = MyRecSet(TagWor d)
...

Interesting. I generally just use the actual field name, rather than the number. For instance

InputSentence.Text = MyRecSet("TagWord")

[akoymakoy]

Thanks for all the replies, i think i got it working

[willakawill]

Interesting. I generally just use the actual field name, rather than the number. For instance

InputSentence.Text = MyRecSet("TagWord")

Using the field name as a string is fine if it is not used in a loop or particularly an inner loop because there is an overhead in parsing the string identifier.

[Killer42]

Using the field name as a string is fine if it is not used in a loop or particularly an inner loop because there is an overhead in parsing the string identifier.

Good point, willakawill - I'll have to keep it in mind in future.

Thanks, I'm always interested in performance tweaks.