Sub export_allocations()

  Set wb = ThisWorkbook

  Set ws_Setup = wb.Worksheets("Setup")

 

  Set ws_Values = wb.Sheets("Allocations")

  Set rng_Export = ws_Values.Range("_rng_Export_alloc")

  'Set rng_Val_Null = ws_Values.Range("_Null_Indicator_alloc")

 

 

 

  called_from = ws_Setup.Range("_Opened_from").Value

  asAtDate = ws_Setup.Range("_AsAtDate").Value

  reserving_class = ws_Setup.Range("_Class").Value

 

  Call M_Utils.AddReferences(wb, "ADODB")

  Call M_Utils.AddReferences(wb, "Scripting")

 

  Call M_Utils.lock_summary_sheets(False)

 

 

 

  timestampField = "RL_Timestamp"

  filelocField = "RL_File_Location"

  conn_str = M_DB.ConnString(ws_Setup.Range("_ExportDB_Location").Value, ws_Setup.Range("_ExportDB_Name").Value, "Access")

 

  table_name = "[tbl_stopLoss_allocations]"

  Call M_Export_Data.ExportDataToDB(ws_Values, rng_Val, Nothing, conn_str, table_name, timestampField, filelocField)

  

  ''test area

  Set Conn = New ADODB.Connection

    

    Conn.Open conn_str

  

 

  i = 1

Do Until rng_Export.Offset(i, 0) = ""

 

    'Make New Recordset

    Set rs = New ADODB.Recordset

            

    'Build SQL Query

    rs_sql = "SELECT * FROM [tbl_stopLoss_allocations] "

    If rng_Export.Offset(i, 0) <> Empty Then rs_sql = rs_sql & " WHERE [As_at_Date] = '" & rng_Export.Offset(i, 0).Value & "' "

    If rng_Export.Offset(i, 1) <> Empty Then rs_sql = rs_sql & " AND [Reserving_Group] = '" & rng_Export.Offset(i, 1).Value & "' "

    If rng_Export.Offset(i, 2) <> Empty Then rs_sql = rs_sql & " AND [YOA] = " & rng_Export.Offset(i, 2).Value & " "

    If rng_Export.Offset(i, 3) <> Empty Then rs_sql = rs_sql & " AND [SCC] = '" & rng_Export.Offset(i, 3).Value & "' "

    If rng_Export.Offset(i, 4) <> Empty Then rs_sql = rs_sql & " AND [Service_Company] = '" & rng_Export.Offset(i, 4).Value & "' "

    If rng_Export.Offset(i, 5) <> Empty Then rs_sql = rs_sql & " AND [RI_Type] = '" & rng_Export.Offset(i, 5).Value & "' "

    If rng_Export.Offset(i, 6) <> Empty Then rs_sql = rs_sql & " AND [Net Premium Alloc perc] = " & rng_Export.Offset(i, 6).Value & " "

    If rng_Export.Offset(i, 7) <> Empty Then rs_sql = rs_sql & " AND [Net Claims Alloc perc] = " & rng_Export.Offset(i, 7).Value & " "

    rs_sql = rs_sql & ";"

 

    rs.Open rs_sql, Conn, adOpenForwardOnly, adLockOptimistic, adCmdText

    j = 0

    'update if exists

    If (Not rs.EOF Or Not rs.BOF) Then

        rs.Update

    Else

        rs.AddNew

    End If

 

    Do Until rng_Export.Offset(0, j) = ""

         str_Field = rng_Export.Offset(0, j)

         rs.Fields(str_Field).Value = rng_Export.Offset(i, j).Value

         j = j + 1

    Loop

       

    rs.Update

    

    rs.Close

    Set rs = Nothing

    i = i + 1

Loop

 

Conn.Close

Set Conn = Nothing

  '' end of test area

 

  If called_from = "this" Then MsgBox "Data export complete."

End Sub