Navigation for dotNetTips.com - Copying Rows In Access

Supposedly, RecordCount is set correctly when you first open a snapshot, but the ListBox test showed otherwise.
Try it yourself. Here is the solution code with my debug lines commented out.

Sub CopyRows () 

Dim i As Integer 
Dim db As Database 
Dim snap As Snapshot 
Dim tbl As Table 
Dim wSQL As String 

    Set db = OpenDatabase("MYDATA.MDB", False, False) 
    Set tbl = db.OpenTable("Table1") 

    ' select the rows to be copied into a snapshot object 
    'List1.Clear 'Mark's debug code 

    wSQL = "Select * from Table1 where Field1 = 'A'" 

    'I also changed your SQL statement a 
    'little, removed characters I don't use, but I don't 
    'think it made any difference. 

    Set snap = db.CreateSnapshot(wSQL) 
    snap.MoveLast '<===== MoveLast initializes RecordCount of snap 
    snap.MoveFirst '<===== Need to MoveFirst 

    'List1.AddItem snap.RecordCount 'Mark's debug code 
    'List1.AddItem "**" 'Mark's debug code 

    ' loop through all rows in the snapshot 
    Do Until snap.EOF 
        tbl.AddNew 
        ' copy each field in the snapshot row to the table row 
        For i = 0 To snap.Fields.Count - 1 
            tbl.Fields(i).Value = snap.Fields(i).Value 
        Next i 
        ' change the value of the other field 
        tbl.Fields("Field2").Value = "B" 
        ' insert the new row into the table 
        tbl.Update 
        ' Ack! Here's the problem. This "tbl.Update" also is 
        ' updating the contents of the snapshot. Meaning the 
        ' "snap.MoveNext" will never get to EOF! 
        'List1.AddItem snap.RecordCount 'Mark's debug code 
        'List1.Refresh 'Mark's debug code 
        'DoEvents 'Mark's debug code 

        snap.MoveNext 
    Loop 

    ' close everything 

    snap.Close 
    tbl.Close 
    db.Close 

End Sub

Tip By: Kyle Lutes