0

I have made this method:

Public Sub Proba()

    Dim CPANonEmptyColumns As Integer
    CPANonEmptyColumns = 0

    Dim Max As Integer
    Max = 0

    Dim Koloni As String
    Koloni = ""

    Dim strSQL As String
    Dim intI As Integer

    Dim rsCPA As DAO.Recordset
    Dim rsCPANezbirni As DAO.Recordset
    Dim dbs_t1t2 As DAO.Database


On Error GoTo ErrorHandler

    Set dbs_t1t2 = CurrentDb

    'Open a recordset on all records from the Employees table that have
    'a Null value in the ReportsTo field.
    strSQL = "SELECT * FROM CPA_t1t2"
    Set rsCPA = dbs_t1t2.OpenRecordset(strSQL)    '//, dbOpenDynaset)

    'If the recordset is empty, exit.
    If rsCPA.EOF Then Exit Sub

    intI = 1
    With rsCPA
      Do Until .EOF
        DoCmd.RunSQL "INSERT INTO CPA_nezbirni (tipprod, promet) VALUES ('" & ![tipprod] & "', '" & ![promet] & "');"

        ' check individual column if it has a value and increment max if CPANonEmptyColumns
        If ![t4k1] <> Null Or ![t4k1] <> "" Then
           CPANonEmptyColumns = CPANonEmptyColumns + 1
           Koloni = Koloni & "t4k1,"

        End If

        If ![t4k2] <> Null Or ![t4k2] <> "" Then
           CPANonEmptyColumns = CPANonEmptyColumns + 1
           Koloni = Koloni & "t4k2,"
        End If

        If ![t4k3] <> Null Or ![t4k3] <> "" Then
           CPANonEmptyColumns = CPANonEmptyColumns + 1
           Koloni = Koloni & "t4k3,"
        End If

        If CPANonEmptyColumns > Max Then
            Max = CPANonEmptyColumns
        End If

        Debug.Print "Red: " & Str(intI) & " Max: " & Str(Max) & ", Koloni: " & Koloni

        .Edit
        .MoveNext
        CPANonEmptyColumns = 0
        Koloni = ""
        intI = intI + 1
      Loop
    End With

    rsCPA.Close
    dbs_t1t2.Close

    Set rsCPA = Nothing
    Set dbs_t1t2 = Nothing

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

Basically, I open two tables, CPA_t1t2 and CPA_nezbirni. I want to copy the appropriate values for the appropriate columns, tipprod and promet from CPA_t1t2 to CPA_nezbirni. The problem is, the source table CPA_t1t2 has 18000 rows and it needs time to run all those "INSERT" queries with the statement:

DoCmd.RunSQL "INSERT INTO CPA_nezbirni (tipprod, promet) VALUES ('" & ![tipprod] & "', '" & ![promet] & "');"

I am always suspicions when it comes SQL about performance. Since it needed 3-4 minutes to finish the procedure and insert values into CPA_nezbirni, is the SQL more slower way to copy value from one table to another?

Is there better, faster way by using the procedure above and some VBA, trough the same "Do Until" loop?

Vlad
  • 2,739
  • 7
  • 49
  • 100
  • 3
    Why don't you use `INSERT INTO CPA_nezbirni (...) SELECT ... FROM CPA_t1t2` query for coping rows? – Sergey S. Jul 14 '17 at 11:54
  • 1
    As Sergey wrote, try to use a single SQL INSERT command to insert all rows. If that isn't possible (for whatever reasons), use `DAO.Recordset.AddNew` instead, see https://stackoverflow.com/questions/33007539/increase-ms-access-insert-performance – Andre Jul 14 '17 at 12:47
  • No people, I wanted faster solution. This solution works, its slow. – Vlad Jul 14 '17 at 12:49
  • 1
    A single INSERT statement will be faster - you join the two tables in the statement and insert the records that match your criteria in one hit. What's your criteria for moving records? – Darren Bartrup-Cook Jul 14 '17 at 12:59
  • Ok. Not sure if I need SELECT on each loop iteration. I just need it once, fetch the data, than run INSERT for appropriate columns. The reason why I need to run INSERT on each iteration is that each row contains numbers which represent column names in CPA_nezbirni. I need to add 1 to those specific columns. Resolving each column name with each iteration. – Vlad Jul 14 '17 at 13:03
  • 1
    Your code can't work as `dbs_t1t2` is never set to `CurrentDb`, you set dbs_Trg_t1t2 (Typo or no Option Explicit?). Why is your recordset `rsCPA` a dynaset and uses `.Edit`, I can't see any. Sergey and Andre want you to use just one `DoCmd.RunSQL "INSERT INTO CPA_nezbirni SELECT tipprod, promet FROM CPA_t1t2;"` That will insert all data in one SQL_Statement. `Max`and`Koloni`are just for debuging? – BitAccesser Jul 14 '17 at 21:01
  • Yes, sorry, I am noob VBA programmer. I found this code over the Internet. Yes dbs_Trg_t1t2 is a typo error. I will fix it. Max and Koloni were supposed to be helper variables, but now I see they are not needed. I don't know what is a dynaset. I will remove that keyword. Now I see I can develop an SQL string trough my loop and, then run it once per iteration with DoCmd.RunSQL – Vlad Jul 15 '17 at 07:04
  • 1
    What SQL string do you want to create? What is wrong with the single SQL string? Removing dbOpenDynaset is not sufficent (then it uses default settings (dbOpenTable or dbOpenDynaset)), use dbOpenSnapshot. See [.OpenRecordset](https://msdn.microsoft.com/en-us/library/office/ff820966.aspx). – BitAccesser 8 hours ago – BitAccesser Jul 16 '17 at 11:06

0 Answers0