Why is my code working slower on excel vba

Viewed 18

I write code for my work. But it is not fast. So what are you think about my code?

code in this line's working is takes 5 seconds kayit.Open Sorgu1, Baglan, 1, 1

But, it is takes 15 minitus that run of next line : If kayit.RecordCount = 0 Then

So, I moved this line to the beginning of if maybe is solved. But not working fast veri = kayit.GetRows

Why getRows is slowy run

Main Code

Sub getDataFromDatabase()

    On Error GoTo HATA
    Dim ConnectionString, StrQuery As String
    Dim veri As Variant
    
    ConnectionString = "Provider=SQLOLEDB.1;Password=" & password & ";Persist Security Info=True;User ID=" & username & ";Data Source=" & server & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=" & database
    Set Baglan = CreateObject("adodb.connection")
    Baglan.Open ConnectionString
    baslikSTR = ""
    For i = 2 To Sheets("par").Cells(Sheets("par").Rows.Count, "c").End(3).Row
        baslikSTR = baslikSTR & Sheets("par").Cells(i, 3) & ","
    Next i
    baslikSTR = Mid(baslikSTR, 1, Len(baslikSTR) - 1)
    Sorgu1 = "SELECT " & baslikSTR & " FROM " & tablesName & " WHERE KT_CLIENT = '" & OOP.sirket & "'"

    Set kayit = VBA.CreateObject("adodb.Recordset")
    kayit.Open Sorgu1, Baglan, 1, 1
    
    If kayit.RecordCount = 0 Then
        'MsgBox "Şirkete ait veri bulunamadı.", vbCritical, "BİLGİ"
    ElseIf kayit.RecordCount > 0 Then
        veri = kayit.GetRows
        Set OOP.dataObject = New clsData
        For i = 0 To UBound(veri, 2)
               
                With OOP.dataObject
                    Set satirDetayData = New clsSatirDetayData

                    satirDetayData.setData CStr(Trim(veri(0, i))), CInt(Trim(veri(1, i))), CStr(Trim(veri(2, i))), _
                                            CStr(Trim(veri(3, i))), CStr(Trim(veri(4, i))), CStr(Trim(veri(5, i))), _
                                            CDbl(Trim(veri(6, i))), CDbl(Trim(veri(7, i))), CDbl(Trim(veri(8, i))), _
                                            CStr(Trim(veri(9, i))), CStr(Trim(veri(10, i))), CStr(Trim(veri(11, i))), _
                                            CStr(Trim(veri(12, i))), CStr(Trim(veri(13, i))), CStr(Trim(veri(14, i))), _
                                            CStr(Trim(veri(15, i))), CInt(Trim(veri(16, i))), CInt(Trim(veri(17, i)))
                    
                    whichDataTypeControl = 0
                    If Left(Trim(veri(12, i)), 1) = "3" Then
                        whichDataTypeControl = 0
                    ElseIf Left(Trim(veri(12, i)), 1) = "4" Or Left(Trim(veri(12, i)), 1) = "5" Then
                        whichDataTypeControl = 1
                    ElseIf Left(Trim(veri(12, i)), 1) = "1" Or Left(Trim(veri(12, i)), 1) = "2" Then
                        whichDataTypeControl = 2
                    End If
                    
                    If Not isExist(OOP.dataObject.dataType, CStr(OOP.whichDataName(whichDataTypeControl))) Then
                        Set dataType = New clsDataType
                        dataType.dataTypeName = CStr(OOP.whichDataName(whichDataTypeControl))
                        OOP.dataObject.dataType.Add dataType, CStr(OOP.whichDataName(whichDataTypeControl))
                    End If

                    If Not isExist(OOP.dataObject.dataType(CStr(OOP.whichDataName(whichDataTypeControl))).hesapKoduData, CStr(satirDetayData.accountCode)) Then
                        Set hesapKoduData = New clsHesapKoduData
                        hesapKoduData.peopleSoftCode = satirDetayData.peopleSoftCode
                        hesapKoduData.accountCode = satirDetayData.accountCode
                        hesapKoduData.accountName = satirDetayData.accountName
                        hesapKoduData.reportTitle = satirDetayData.reportTitle
                        OOP.dataObject.dataType(CStr(OOP.whichDataName(whichDataTypeControl))).hesapKoduData.Add hesapKoduData, CStr(satirDetayData.accountCode)
                    End If
           
                    If Not isExist(OOP.dataObject.dataType(CStr(OOP.whichDataName(whichDataTypeControl))).hesapKoduData(CStr(satirDetayData.accountCode)).ayData, CStr(satirDetayData.month)) Then
                        Set ayData = New clsAyData
                        ayData.hangiAy = satirDetayData.month
                        OOP.dataObject.dataType(CStr(OOP.whichDataName(whichDataTypeControl))).hesapKoduData(CStr(satirDetayData.accountCode)).ayData.Add ayData, CStr(satirDetayData.month)
                    End If

                    OOP.dataObject.dataType(CStr(OOP.whichDataName(whichDataTypeControl))).hesapKoduData(CStr(satirDetayData.accountCode)).ayData(CStr(satirDetayData.month)).ayToplam = _
                        OOP.dataObject.dataType(CStr(OOP.whichDataName(whichDataTypeControl))).hesapKoduData(CStr(satirDetayData.accountCode)).ayData(CStr(satirDetayData.month)).ayToplam + _
                        satirDetayData.balance
                        
                        
                    OOP.dataObject.dataType(CStr(OOP.whichDataName(whichDataTypeControl))).hesapKoduData(CStr(satirDetayData.accountCode)).ayData(CStr(satirDetayData.month)).ayDebitToplam = _
                        OOP.dataObject.dataType(CStr(OOP.whichDataName(whichDataTypeControl))).hesapKoduData(CStr(satirDetayData.accountCode)).ayData(CStr(satirDetayData.month)).ayDebitToplam + _
                        satirDetayData.debit
                        
                    OOP.dataObject.dataType(CStr(OOP.whichDataName(whichDataTypeControl))).hesapKoduData(CStr(satirDetayData.accountCode)).ayData(CStr(satirDetayData.month)).ayCreditToplam = _
                        OOP.dataObject.dataType(CStr(OOP.whichDataName(whichDataTypeControl))).hesapKoduData(CStr(satirDetayData.accountCode)).ayData(CStr(satirDetayData.month)).ayCreditToplam + _
                        satirDetayData.credit
                        
                    OOP.dataObject.dataType(CStr(OOP.whichDataName(whichDataTypeControl))).hesapKoduData(CStr(satirDetayData.accountCode)).ayData(CStr(satirDetayData.month)).satirDetayData.Add satirDetayData
                
                    
                End With
        Next i
    End If
    kayit.Close
    Baglan.Close
    Set kayit = Nothing
    Set Baglan = Nothing
    Exit Sub
    
HATA::
    Set kayit = Nothing
    Set Baglan = Nothing
    
End Sub
0 Answers
Related