Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: dynamic population of a table: last record left out

    I'm trying to populate a table by using VBA code. I take the data from one table and copy it to another table. The problem is that the last record is always left out: it is not copied into the new table.

    This is the code I'm using:

    Sub AddValues()

    Dim cnn1 As ADODB.Connection
    Dim cat1 As New ADOX.Catalog
    Dim rst1 As New ADODB.Recordset
    Dim rst2 As New ADODB.Recordset

    'Set context for populating new table (Timetable)
    Set cnn1 = CurrentProject.Connection
    Set cat1.ActiveConnection = cnn1
    Set rst1.ActiveConnection = cnn1

    'Open recordsets based on new and original tables
    rst1.Open "Timetable", , adOpenKeyset, _
    adLockOptimistic, adCmdTable
    rst2.Open "SourceTable", cnn1, adOpenForwardOnly, _
    adLockReadOnly, adCmdTable

    'Loop through recordsets to copy from original to new table
    With rst1
    Do Until rst2.EOF
    .Fields(0) = rst2.Fields(0)

    ' .Update
    End With

    End Sub

    Any suggestions?

  2. #2
    Join Date
    Feb 2004

    Re: dynamic population of a table: last record left out

    ehm... just realized. I reply to myself:

    update without the '

  3. #3
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    Your problem can be solved in SQL.

    Select * into tableB from tableA

    Alternatively, if you only want to copy the table structure, then

    Select * into tableB from tableA where 1 = 0
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts