Speed up VB code to copy

Could we have some more information?

Does this sum what you are trying to do:
1. If there is data in "!bill" then leave the field alone"
2. If the field is empty, then fill it with a NULL??

Why not create a recordset with only fields that have len(!bill) = 0 and then Update the entire recordset with !bill = vbnullstring?

Also, we might help more if you can give us background on what you need

The code looks for Records that are not Null and copies that field to the next Null field, and resets when it comes to a Not null field again and loops.
Example:  BEFORE
K80524A  05-06-2005
                 05-06-2006
                 06-05-2008
K90564A 05-06-2008
                06-05-2007
                07-05-2006

Example: AFTER
K80524A  05-06-2005
K80524A  05-06-2006
K80524A 06-05-2008
K90564A 05-06-2008
K90564A 06-05-2007
K90564A 07-05-2006

Is the recordset the WHOLE table?

Try creating the recordset with just the Primary key, !bill.  This way you don't load too much into memory.

I dont have a primary key set up on this table and I dont suggest using one either..I find that it just complicates matters

then your data may have issues, how can you be certain that the records will always come up in the same order?

Anyway, just create the recordset with the on column, then run the script, and see if you get a performance boost.

oops typo on should be on

Anyway, just create the recordset with the --->>>one <<<--- column, then run the script, and see if you get a performance boost.

Run the script where in SQL? Is that right, and if so the VB script or the SQL script listed from PAT??

What is the underlying database? And does it support batch update?

ok, I am not sure of your environment, but use this for your recordset.  you may get some errors, filelocking that we will have to tackle.

Set rstThis = CurrentDb.OpenRecordset ("Select Bill from <TableName>")

What is this code going to do??

instead of having a recordset with lots of fields and data, there will only be 1 column of data.

1. Explicitly set the type of recordset.  Note that table access is faster than dynaset.

Example:
Set rstThis = CurrentDb.OpenRecordset("Weekly Comments", dbOpenTable)
Set rstThis = CurrentDb.OpenRecordset("Weekly Comments", dbOpenDynaset)

2. Do your updates within one or more transactions.
Example:

    DBEngine.Workspaces(0).BeginTrans
    With rstThis

...

        End If
        DBEngine.Workspaces(0).CommitTrans
        .Close
    End With

3. RecordCount is not reliable and is unnecessary overhead in your code.

4. You do not need to .MoveLast and .MoveFirst

5. Your Null test is very inefficient.

See snippet for faster example.

6. Is there a primary key (hopefully and autonumber column) in [Weekly Comments]?  If so, you can solve this problem with a single SQL statement, although it won't be a simple statement as suggested in an earlier comment.
Such a SQL-only solution should be much quicker than a VBA solution, which is interpreted.

Public Function LA()
    Dim rstThis As Recordset
    Dim varField2 As Variant
    
    Set rstThis = db.OpenRecordset("Weekly Comments", dbOpenTable)  'or dbOpenDynaset if a query
 
    DBEngine.Workspaces(0).BeginTrans
    With rstThis
        Do Until .EOF
            If IsNull(!Bill) Then
                .Edit
                    !Bill = varField2
                .Update
            Else
                varField2 = !Bill
            End If
            .MoveNext
        Loop
        DBEngine.Workspaces(0).CommitTrans
        .Close
    End With
    Set rstThis = Nothing
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:

oops.  Should be:

    Set rstThis = Currentdb.OpenRecordset("Weekly Comments", dbOpenTable)  'or dbOpenDynaset if a query
 

6. *update* I've attempted to get a good performing query without success.  I expanded the configuration to use several queries and a temporary table.  It appears that the Jet engine will not join two tables (or sets of data) efficiently on a BETWEEN condition. :-(

Here's a pertinent question...
7. Is the [Weekly Comments] table bound to any object(s) when you are trying to do this update?

@nelsonje

We haven't heard from you in a week.  Are you still participating in this question?  Have we already solved this question for you and just don't know it yet?

We've shown you tweaks to your posted code that run in 2.4 seconds on my PC.  This should be enough improvement from the 30 minutes of your code to warrant some response from you, such as "OMG" or "OMG!" or "Wow! You guys rock." or "Awesome" or "Thanks" or "I'll try that and get back to you" or "It still performs poorly on my PC"

If you are working on this, please let us know.  If you are still having performance problems, some of our questions will give us enough information to help you beyond the tweak.

@ aikimark

I definately think you rock!!! OMG, WOW, Awesome, And Thanks...  I learned a few things.  Too bad i can't give you points.