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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s