Updating table's column via VBA

[Amerigo]

Haven't programmed in access in a while, so i am a bit rusty


I am trying to assign a vlue to the column in a table


(based on content of other criteria), but i am stuck in the first step

how do i say that i want 16th column to become "2" for all records ?

.Fields(16).Value = 2

 
 
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

 
Set db = CurrentDb()
strSQL = "SELECT *  FROM qryResult qryResult"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst

Debug.Print Z
With rst
     Do While .EOF = False
         .Edit
         .Fields(16).Value = 2
         .MoveNext
    Loop
End With
rst.Close

[pbaldy]

What's your question? You've doubled up "qryResult" in the SQL string. It would be much more efficient to execute an UPDATE query than to loop a recordset. Personally, I don't like to use the field number; the name is a lot easier to understand:

!FieldName = 2

[Amerigo]

What's your question? You've doubled up "qryResult" in the SQL string. It would be much more efficient to execute an UPDATE query than to loop a recordset. Personally, I don't like to use the field number; the name is a lot easier to understand:

!FieldName = 2

>>You've doubled up "qryResult" in the SQL string.
not sure what u mean

I can't use 'Update' query since my end goal is to populate Fields(16) based on other criteria. I've done this few years ago, but can't move past first step.
I think the problem is in this line

.Fields(16).Value = 2

i am not getting errors, but nothing is updating

[Amerigo]

technically i need something like this

BookMyNewFieldX_111d_561t_221t_222r_771r_772r_773yy_661f_41f_781

this new field i am trying to update depends on 'Book'
I'd like MyNewField to count start counting each time book changes

[pbaldy]

This is what I mean:

strSQL = "SELECT * FROM qryResult qryResult"

An UPDATE query can have criteria. Have you tried Fields(15) or the field name? I suspect the Fields property is zero based.

I would also include the update line:

.Edit
!FieldName = 2
.Update

[Amerigo]

This is what I mean:

strSQL = "SELECT * FROM qryResult qryResult"

An UPDATE query can have criteria. Have you tried Fields(15) or the field name? I suspect the Fields property is zero based.

I would also include the update line:

.Edit
!FieldName = 2
.Update

I see - u were correct, now i am getting last row populated
So now i am trying to make the last row depend on column 'book'

 
rst.MoveFirst
Debug.Print Z
With rst
Value = "value"
     Do While .EOF = False
         .Edit
            
            If .Fields(0).Value = Value Then
                .Fields(17).Value = 1
                 Value = .Fields(0).Value
            Else
                .Fields(17).Value = Order
                Order = Order + 1
                Value = .Fields(0).Value
            End If
         
         
         .Update
         .MoveNext
    Loop
End With
rst.Close

So if Fields(0) is the the same for 2 or more rows, i'd like increment it, and to go back to 1 for eac new Fields(0)

(what i wrote doesn't work)

[pbaldy]

I suspect this:

Value = "value"

should be

Value = .Fields(0).Value

And I'd note that having a variable with the same name as a property will be confusing. I think your logic is reversed. If the current Fields(0) is the same as the last, you want to increment your variable and use it. In the else, reset it to 1.

[Amerigo]

hey - so I got what i wanted with below --
but it run;s for almost 10 min ! need it to be much faster. Any way ?

 
With rst
Value = "value"
Order = 1
     Do While .EOF = False
         .Edit
            
            If .Fields(0).Value <> Value Then
                .Fields(4).Value = 1
                Order = 1                       'order back to 1
                Debug.Print .Fields(4).Value
                 Value = .Fields(0).Value
                 Spread = .Fields(2).Value
            Else
                
                    If .Fields(2).Value <> Spread Then Order = Order + 1
                   .Fields(4).Value = Order
                    Debug.Print .Fields(4).Value
                    
                Value = .Fields(0).Value
                Spread = .Fields(2).Value
            End If
         
         
         .Update
         .MoveNext
    Loop
End With

[pbaldy]

How many records? For 100, that's terrible. For 100,000, it's probably to be expected.

[Amerigo]

How many records? For 100, that's terrible. For 100,000, it's probably to be expected.

around 70 thousand now; but the populion is growing daily by 2000 - 3000 records

[pbaldy]

Are you redoing the whole table every time, or just the new records? Just doing the new records would obviously be preferable if feasible. I don't know that there's a lot you can do to speed that up. I assume the appropriate fields are indexed on the table, but I suspect the bottleneck is the recordset having to examine each record. You can make it run at night when nobody is there, or something along those lines.