3

Looking for help in below table using VBA in Ms-Access 2007.

I have 39 columns to copy and repeat col40 times enter image description here

Just starting to learn VBA. Any help would be appreciated.

I am attempting to take the following approach (by modifying another similar post that I found from a while back) which is giving me a few errors.

Sub DuplicateRecords()

Dim rstSource As Recordset
Dim rstDest As Recordset
Dim Dups As Integer

' use dbOpenSnapshot to open the source table READ-ONLY
Set rstSource = CurrentDb.OpenRecordset( _
        "SELECT  col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27, col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39, col40, col41 FROM Qry_Fleet_Plan_Builder;" _
        , dbOpenSnapshot)
' use dbOpenDynaset to open the destination table READ-WRITE
Set rstDest = CurrentDb.OpenRecordset( _
        "SELECT  col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27, col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39 FROM BDFU;" _
        , dbOpenDynaset)

With rstSource
    ' .BOF is Beginning of the table
    ' .EOF is End of the table
    ' Checking if both are false means there are records in the
    ' source table
    If Not (.BOF And .EOF) Then
        ' get the first record from the source table
        .MoveFirst
        Do
           ' if col40 is NULL (empty)
            If Nz(!col40, "") = "" Then
                  MsgBox ("Error! No Records in Source File")
            Else ' if col40 IS NOT NULL
                Dups = rstSource!col40
                ' loop through 
                For a = 1 To Dups
                    rstDest.AddNew
                    rstDest!col2 = rstSource!col2
                    rstDest!col3 = rstSource!col3
                    rstDest!col4 = rstSource!col4
                    rstDest!col5 = rstSource!col5
                    rstDest!col6 = rstSource!col6
                    rstDest!col7 = rstSource!col7
                    rstDest!col8 = rstSource!col8
                    rstDest!col9 = rstSource!col9
                    rstDest!col10 = rstSource!col10
                    rstDest!col11 = rstSource!col11
                    rstDest!col12 = rstSource!col12
                    rstDest!col13 = rstSource!col13
                    rstDest!col14 = rstSource!col14
                    rstDest!col15 = rstSource!col15
                    rstDest!col16 = rstSource!col16
                    rstDest!col17 = rstSource!col17
                    rstDest!col18 = rstSource!col18
                    rstDest!col19 = rstSource!col19
                    rstDest!col20 = rstSource!col20
                    rstDest!col21 = rstSource!col21
                    rstDest!col22 = rstSource!col22
                    rstDest!col23 = rstSource!col23
                    rstDest!col24 = rstSource!col24
                    rstDest!col25 = rstSource!col25
                    rstDest!col26 = rstSource!col26
                    rstDest!col27 = rstSource!col27
                    rstDest!col28 = rstSource!col28
                    rstDest!col29 = rstSource!col29
                    rstDest!col30 = rstSource!col30
                    rstDest!col31 = rstSource!col31
                    rstDest!col32 = rstSource!col32
                    rstDest!col33 = rstSource!col33
                    rstDest!col34 = rstSource!col34
                    rstDest!col35 = rstSource!col35
                    rstDest!col36 = rstSource!col36
                    rstDest!col37 = rstSource!col37
                    rstDest!col38 = rstSource!col38
                    rstDest!col39 = rstSource!col39
                    rstDest.Update
                Next a
            End If
            ' load the next record from the source table
            .MoveNext
        ' repeat until the end of the source table is reached
        Loop Until .EOF
    End If
    ' close the source table
    .Close
End With
' close the destination table
rstDest.Close

End Sub

  • Do you need also copy col1? In your example it seems col1 would be a key auto-generated. – David Sep 12 '16 at 19:55
  • Yes Column 1 is the key. I guess no need to copy that one. Thx- – Todd Sep 13 '16 at 12:12
2

Create and populate Table 1 with a single column [col1]. You will populate that table with values 1, 2, 2, 3, 3, 3, 4, 4, 4, 4,... Once you have that table the basic query is very simple, asumming your source is Table 2:

select  t2.[col2],t2.[col3],t2.[col4], ...
from [dbo].[Table2] as t2
left join [dbo].[Table1] as t1
on t2.[col40] = t1.[col1]
order by t2.[col1]

A more elegant way is given by Martin Smith in the comments: Create and populate Table 3 with a single column [col1]. You will populate that table with unique values 1, 2, 3, 4,...The query then would be:

select  t2.[col2],t2.[col3],t2.[col4]
from [dbo].[Table2] as t2
left join [dbo].[Table3] as t3
on t2.[col40] >= t3.[col1]
order by t2.[col1]

Having these basic database approaches, you can then use VBA to create, populate the tables, and run the queries. Good luck!

  • 3
    Or just create a unique numbers table without repetition and change t2.[col40] = t1.[col1] to t1.[col1] <= t2.[col40] – Martin Smith Sep 12 '16 at 20:17
  • @MartinSmith - I understand and agree with your comment. However my test results are unexpected (I am using SQL Server Express 2016): For col40 values 2, 5, 6 I obtain 9, 6, 5 respectively. Weird! – David Sep 12 '16 at 20:45
  • @ypercubeᵀᴹ - Thank you! you are right!! – David Sep 13 '16 at 1:44
  • 2
    MartinSmith hasn't answered so you can edit your answer and add his suggestion as well. – ypercubeᵀᴹ Sep 13 '16 at 15:45
  • Thank you @ypercubeᵀᴹ. Have edited my answer to include Martin Smith's suggestion. – David Sep 15 '16 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.