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