Results 1 to 5 of 5

Thread: vba to sql

  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: vba to sql

    I'm a trying to replicate the following vba code into sql. Can anyone please help?

    -----------------------------------
    thedb.Execute ("INSERT INTO Mortgage_Rel_Temp ( wh_acc_no, wh_cust_no ) SELECT Mortgage_Rel.wh_acc_no, Mortgage_Rel.wh_cust_no FROM Mortgage_Rel")

    sqlstring1 = "SELECT Mortgage_Rel_Temp.wh_acc_no, Mortgage_Rel_Temp.wh_cust_no, Mortgage_Rel_Temp.cluster_no FROM Mortgage_Rel_Temp ORDER BY Mortgage_Rel_Temp.wh_acc_no"
    Set rst1 = thedb.OpenRecordset(sqlstring1, dbOpenDynaset)
    rst1.MoveLast
    rwnbr = rst1.RecordCount
    rst1.MoveFirst
    ChangeCount = 1
    For i = 1 To rwnbr
    rst1.Edit
    rst1!cluster_no = i
    rst1.Update
    rst1.MoveNext
    Next i
    Do While ChangeCount <> 0
    ChangeCount = 0


    sqlstring2 = "SELECT Mortgage_Rel_Temp.wh_acc_no, Mortgage_Rel_Temp.wh_cust_no, Mortgage_Rel_Temp.cluster_no FROM Mortgage_Rel_Temp ORDER BY Mortgage_Rel_Temp.wh_acc_no ASC, Mortgage_Rel_Temp.cluster_no ASC"
    Set rst2 = thedb.OpenRecordset(sqlstring2, dbOpenDynaset)

    rst2.MoveFirst
    prev_fac = rst2!wh_acc_no
    prev_cust = rst2!wh_cust_no
    prev_clus = rst2!cluster_no
    rst2.MoveNext

    For i = 2 To rwnbr
    If rst2!cluster_no <> prev_clus Then
    If rst2!wh_acc_no = prev_fac Then
    rst2.Edit
    rst2!cluster_no = prev_clus
    ChangeCount = ChangeCount + 1
    rst2.Update
    Else
    End If
    Else
    End If
    prev_fac = rst2!wh_acc_no
    prev_cust = rst2!wh_cust_no
    prev_clus = rst2!cluster_no

    rst2.MoveNext
    Next i

    rst2.Close

    sqlstring3 = "SELECT Mortgage_Rel_Temp.wh_acc_no, Mortgage_Rel_Temp.wh_cust_no, Mortgage_Rel_Temp.cluster_no FROM Mortgage_Rel_Temp ORDER BY Mortgage_Rel_Temp.wh_cust_no ASC, Mortgage_Rel_Temp.cluster_no ASC"
    Set rst3 = thedb.OpenRecordset(sqlstring3, dbOpenDynaset)

    rst3.MoveFirst
    prev_fac = rst3!wh_acc_no
    prev_cust = rst3!wh_cust_no
    prev_clus = rst3!cluster_no
    rst3.MoveNext

    For i = 2 To rwnbr
    If rst3!cluster_no <> prev_clus Then
    If rst3!wh_cust_no = 50798 Then
    Debug.Print rst3!wh_cust_no
    End If

    If rst3!wh_cust_no = prev_cust Then
    rst3.Edit
    rst3!cluster_no = prev_clus
    ChangeCount = ChangeCount + 1
    rst3.Update
    Else
    End If
    Else
    End If



    prev_fac = rst3!wh_acc_no
    prev_cust = rst3!wh_cust_no
    prev_clus = rst3!cluster_no

    rst3.MoveNext
    Next i


    Loop
    ---------------------------------

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    What have you done so far and what problem are you having with it. We are here to help you do your job. Not do your job for you.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2013
    Posts
    2
    I have done the row numbering in section 1 of the vba code (SQL 1). Getting stock with tne looping to assign clusters_no. I've tried writing an update but I seem to be missing some account that were picked by the vba (SQL 2

    --------SQL1-------
    INSERT INTO MORTG_REL_TEMP
    (WH_ACC_NO
    ,WH_CUST_NO
    ,CLUSTER_NO
    )
    SELECT
    b.wh_acc_no
    ,b.wh_cust_no
    ,MIN(a.cluster_n) AS CLUSTER_NO
    FROM
    (
    SELECT wh_acc_no, wh_cust_no
    ,ROW_NUMBER() OVER(ORDER BY wh_ACC_no, cluster_no ASC) AS cluster_N
    FROM MORTGAGE_REL
    GROUP BY wh_acc_no, wh_cust_no
    )a
    ,MORTGAGE_REL b

    WHERE b.wh_acc_no = a.wh_acc_no

    GROUP BY
    b.wh_acc_no
    ,b.wh_cust_no


    ----------SQL 2------------
    UPDATE MORTG_REL_TEMP
    FROM
    (
    SELECT
    Wh_cust_No ,
    MIN(cluster_no)
    FROM
    MORTG_REL_TEMP
    GROUP BY 1
    ) AS TEST (D1,D2)
    SET cluster_no = d2
    WHERE
    Wh_cust_No = D1
    AND CLUSTER_NO <> D2

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What version (2000, 2005, 2008, 2012) of SQL Server are you using?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Which instance of sqlstring2? There can be many as the WHILE loop iterates.
    Code:
    thedb.Execute ( _
       "INSERT INTO Mortgage_Rel_Temp ( wh_acc_no, wh_cust_no )" _
       & " SELECT Mortgage_Rel.wh_acc_no, Mortgage_Rel.wh_cust_no FROM Mortgage_Rel")
      
    sqlstring1 = "SELECT Mortgage_Rel_Temp.wh_acc_no" _
    &  ", Mortgage_Rel_Temp.wh_cust_no, Mortgage_Rel_Temp.cluster_no" _
    &  " FROM Mortgage_Rel_Temp ORDER BY Mortgage_Rel_Temp.wh_acc_no"
    Set rst1 = thedb.OpenRecordset(sqlstring1, dbOpenDynaset)
    rst1.MoveLast
    rwnbr = rst1.RecordCount
    
    rst1.MoveFirst
    For i = 1 To rwnbr
       rst1.Edit
       rst1!cluster_no = i
       rst1.Update
       rst1.MoveNext
    Next i
    
    ChangeCount = 1
    Do While ChangeCount <> 0
       ChangeCount = 0
      
       sqlstring2 = "SELECT Mortgage_Rel_Temp.wh_acc_no" _
       & ", Mortgage_Rel_Temp.wh_cust_no, Mortgage_Rel_Temp.cluster_no" _
       & " FROM Mortgage_Rel_Temp" _
       & " ORDER BY Mortgage_Rel_Temp.wh_acc_no ASC" _
       & ", Mortgage_Rel_Temp.cluster_no ASC"
       Set rst2 = thedb.OpenRecordset(sqlstring2, dbOpenDynaset)
      
       rst2.MoveFirst
       prev_fac = rst2!wh_acc_no
       prev_cust = rst2!wh_cust_no
       prev_clus = rst2!cluster_no
       rst2.MoveNext
      
       For i = 2 To rwnbr
          If rst2!cluster_no <> prev_clus Then
             If rst2!wh_acc_no = prev_fac Then
                rst2.Edit
                rst2!cluster_no = prev_clus
                ChangeCount = ChangeCount + 1
                rst2.Update
             End If
          End If
     
          prev_fac = rst2!wh_acc_no
          prev_cust = rst2!wh_cust_no
          prev_clus = rst2!cluster_no
          rst2.MoveNext
       Next i
       rst2.Close
      
       sqlstring3 = "SELECT Mortgage_Rel_Temp.wh_acc_no" _
       & ", Mortgage_Rel_Temp.wh_cust_no, Mortgage_Rel_Temp.cluster_no" _
       & " FROM Mortgage_Rel_Temp" _
       & " ORDER BY Mortgage_Rel_Temp.wh_cust_no ASC" _
       & ", Mortgage_Rel_Temp.cluster_no ASC"
       Set rst3 = thedb.OpenRecordset(sqlstring3, dbOpenDynaset)
       rst3.MoveFirst
       prev_fac = rst3!wh_acc_no
       prev_cust = rst3!wh_cust_no
       prev_clus = rst3!cluster_no
    
       rst3.MoveNext
       For i = 2 To rwnbr
         If rst3!cluster_no <> prev_clus Then
            If rst3!wh_cust_no = 50798 Then
               Debug.Print rst3!wh_cust_no
            End If
        
            If rst3!wh_cust_no = prev_cust Then
               rst3.Edit
               rst3!cluster_no = prev_clus
               ChangeCount = ChangeCount + 1
               rst3.Update
            End If
         End If
        
         prev_fac = rst3!wh_acc_no
         prev_cust = rst3!wh_cust_no
         prev_clus = rst3!cluster_no
         rst3.MoveNext
       Next i
    Loop
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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