Queries running more time because of more connection string

[Prabakaran3003]

Hi,

I am using multiple connection strings (Conn1,Conn2,Conn3,Conn4 and Conn5) to get the results from data base,.

Except conn1, remaining strings are pointing same database but different queries. because of keeping multiple string its taking more time to fetch the results.

Could someone please help to reduce the code to bring all the string into one.??

Private Sub CommandButton2_Click()
Unload Me
Final.Show
End Sub

Private Sub CommandButton3_Click()
Unload Me
End Sub

Private Sub PIN_Click()
Dim x As Workbook
Set x = Workbooks.Open("C:\Test data.xlsx")
a = WorksheetFunction.CountA(Worksheets(1).Columns(2))

For i = 2 To a
    ssn = x.Sheets("Sheet1").Range("B" & i).Value
    ssn = "'" & ssn & "'"
    'ssn = Format(x.Sheets("Sheet1").Range("B" & i).Value, "000000000")
    ssn = "'" & Format(x.Sheets("Sheet1").Range("B" & i).Value, "000000000") & "'"
    Set conn1 = CreateObject("ADODB.Connection")
    Set conn2 = CreateObject("ADODB.Connection")
    Set conn3 = CreateObject("ADODB.Connection")
    Set conn4 = CreateObject("ADODB.Connection")
    Set conn5 = CreateObject("ADODB.Connection")

    Set rs = CreateObject("ADODB.Recordset")
    If Final.ComboBox1.ListIndex = intg Then
        If Final.ComboBox2.ListIndex = INGWIN Then
            database = "SSOIN01"
        Else
            Final.TextBox7.Value = "hldom530"
            logon = ". integ"
            database = "SSOIN01"
        End If
    Else
        'If ComboBox1.ListIndex = Accp Then
        If Final.ComboBox2.ListIndex = INGWIN Then
            Final.TextBox7.Value = "hldom560"
            logon = ". wnlogonu"
            database = "SSOAR02"
        Else
            Final.TextBox7.Value = "hlaom530"
            logon = " . accp"
            database = "SSOAR02"
        End If
    End If

    If Final.ComboBox2.ListIndex = INGWIN Then
        conn1.Open "CSW - Integ", "i704960", "%4x-CY#L"
        Sql = "SELECT Css_EntityRole.SECURITY_CODE FROM Css_Acct, Css_BookOfRecord, Css_Entity, Css_EntityRole WHERE Css_Entity.Entity_Syskey = Css_EntityRole.Entity_Syskey AND Css_EntityRole.Account_Syskey = Css_Acct.Account_Syskey AND Css_BookOfRecord.Book_Of_Record_Syskey = Css_Acct.Book_Of_Record_Syskey AND Css_Entity.SSN_TIN_Number = " & ssn
        rs.Open Sql, conn
        Set rs = conn1.Execute(Sql)
        If Not rs.EOF Then
           Excel.Workbooks("Test data.xlsx").Worksheets("Sheet1").Range("I" & i).Value = rs.Fields(0).Value
            'rs.Fields(0).Value = "'" & Format(x.Sheets("Sheet1").Range("I" & i).Value, "0000") & "'"
            'rs.Fields(0).Value = Format(x.Sheets("Sheet1").Range("I" & i).Value, "0000")
        Else
            Excel.Workbooks("Test data.xlsx").Worksheets("Sheet1").Range("I" & i).Value = "Data not found"
            rs.Close
        End If
    End If

        conn2.Open database, "j701853", "20OYnr8Q"
        Sql = "select sso.FUNC_DECRYPT_STR (password) from SSO.SSO_USER where sso_user_id =" & ssn
        rs.Open Sql, conn2
        Set rs = conn2.Execute(Sql)
        If Not rs.EOF Then
            Excel.Workbooks("Test data.xlsx").Worksheets("Sheet1").Range("I" & i).Value = rs.Fields(0).Value
        Else
            Excel.Workbooks("Test data.xlsx").Worksheets("Sheet1").Range("I" & i).Value = "Data not found"
        End If
        rs.Close
        
        conn3.Open database, "j701853", "20OYnr8Q"
        Sql = "select sso.FUNC_DECRYPT_STR(ALPHA_PASSWORD)  from SSO.SSO_USER where sso_user_id =" & ssn
        rs.Open Sql, conn3
        Set rs = conn3.Execute(Sql)
        If Not rs.EOF Then
            Excel.Workbooks("Test data.xlsx").Worksheets("Sheet1").Range("J" & i).Value = rs.Fields(0).Value
        Else
            Excel.Workbooks("Test data.xlsx").Worksheets("Sheet1").Range("J" & i).Value = "Data not found"
        End If
        rs.Close

        conn4.Open database, "j701853", "20OYnr8Q"
        Sql = "select USERNAME from SSO.SSO_USER where sso_user_id =" & ssn
        rs.Open Sql, conn4
        Set rs = conn4.Execute(Sql)
        If Not rs.EOF Then
            Excel.Workbooks("Test data.xlsx").Worksheets("Sheet1").Range("K" & i).Value = rs.Fields(0).Value
        Else
            Excel.Workbooks("Test data.xlsx").Worksheets("Sheet1").Range("K" & i).Value = "Data not found"
        End If
        rs.Close

        conn5.Open database, "j701853", "20OYnr8Q"
        Sql = "select employee_id from SSO.SSO_USER where sso_user_id =" & ssn
        rs.Open Sql, conn5
        Set rs = conn5.Execute(Sql)
        If Not rs.EOF Then
            Excel.Workbooks("Test data.xlsx").Worksheets("Sheet1").Range("L" & i).Value = rs.Fields(0).Value
        Else
            Excel.Workbooks("Test data.xlsx").Worksheets("Sheet1").Range("L" & i).Value = "Data not found"
        End If
        rs.Close
        
Next
        MsgBox "Completed"
        x.Save
        x.Close
End Sub

[Prabakaran3003]

Could someone please help me on this????