Uzun SQL kodunu VBA kodlarına uyarlamak

[lsuersoy]

Private Sub CommandButton1_Click()
Dim Baglanti As New ADODB.Connection
Dim KayitSeti As New ADODB.Recordset
Dim Firma As String, Server As String, Database As String, Kullanıcı As String, Parola As String

Firma = Format(Sheets("SETUP").Range("B5"), "000")
Server = Sheets("SETUP").Range("B1").Value
Database = Sheets("SETUP").Range("B4").Value
Kullanıcı = Sheets("SETUP").Range("B2").Value
Parola = Sheets("SETUP").Range("B3").Value

Sorgu = "SELECT CEK_KART.PORTFOYNO AS PortfoyNo, CEK_KART.SERINO AS SeriNo, CEK_KART.DOC AS Türü, CEK_KART.CURRSTAT AS Statüsü,"
CEK_KART.DUEDATE AS Vade, CEK_KART.SETDATE AS Tarih1, CEK_KART.AMOUNT AS Tutar, MAX(ROLLER.STATNO) AS Hareket,
CARI.CODE AS [Cari Kodu], CARI.DEFINITION_ AS Ünvanı, ROLLER.RECSTATUS, CEK_KART.OURBANKREF, CEK_KART.BANKNAME,
CEK_KART.SPECODE, CEK_KART.CYPHCODE, CEK_KART.CITY, CEK_KART.OWING, CEK_KART.KEFIL, CEK_KART.MUHABIR, CEK_KART.BRANCH,
CEK_KART.DEVIR, CEK_KART.INUSE, CEK_KART.EXTENREF, CEK_KART.CAPIBLOCK_CREATEDBY, CEK_KART.CAPIBLOCK_CREADEDDATE,
CEK_KART.CAPIBLOCK_CREATEDHOUR, CEK_KART.CAPIBLOCK_CREATEDMIN, CEK_KART.CAPIBLOCK_CREATEDSEC,
CEK_KART.CAPIBLOCK_MODIFIEDBY, CEK_KART.CAPIBLOCK_MODIFIEDDATE, CEK_KART.CAPIBLOCK_MODIFIEDHOUR,
CEK_KART.CAPIBLOCK_MODIFIEDMIN, CEK_KART.CAPIBLOCK_MODIFIEDSEC, CEK_KART.COLLREPRATE, CEK_KART.COLLTRRATE,
CEK_KART.CANCELLED, CEK_KART.LINEEXCTYP, CEK_KART.TEXTINC, CEK_KART.SITEID, CEK_KART.RECSTATUS AS Expr1,
CEK_KART.ORGLOGICREF, CEK_KART.WFSTATUS, CEK_KART.BNBRANCHNO, CEK_KART.BNACCOUNTNO, CEK_KART.DEPTADDR1,
CEK_KART.DEPTADDR2, CEK_KART.DEPTCITY, CEK_KART.DEPTCITYCODE, CEK_KART.DEPTCOUNTRY, CEK_KART.DEPTCOUNTRYCODE,
CEK_KART.DEPTPOSTCODE, CEK_KART.DEPTTELNRS1, CEK_KART.DEPTTELNRS2, CEK_KART.DEPTFAXNR, CEK_KART.DEPTTOWN,
CEK_KART.DEPTTOWNCODE, CEK_KART.DEPTDISTRICT, CEK_KART.DEPTDISTRICTCODE, CEK_KART.OPSTAT, CEK_KART.PRINTCNT,
CEK_KART.NEWSERINO, CEK_KART.PROJECTREF, CEK_KART.FAXCODE, CEK_KART.TELCODES2, CEK_KART.TELCODES1,
CEK_KART.COLLATCARDREF , CEK_KART.COLLATROLLREF, CEK_KART.AFFECTCOLLATRL, CEK_KART.AFFECTRISK
FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD CARI INNER JOIN
LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_CSTRANS ROLLER ON CARI.LOGICALREF = ROLLER.CARDREF INNER JOIN
LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_CSCARD CEK_KART ON ROLLER.CSREF = CEK_KART.LOGICALREF
GROUP BY CEK_KART.PORTFOYNO, CEK_KART.SERINO, CEK_KART.DOC, CEK_KART.CURRSTAT, CEK_KART.DUEDATE, CEK_KART.SETDATE, CARI.CODE,
CARI.DEFINITION_, CEK_KART.AMOUNT, ROLLER.RECSTATUS, CEK_KART.OURBANKREF, CEK_KART.BANKNAME, CEK_KART.SPECODE,
CEK_KART.CYPHCODE, CEK_KART.CITY, CEK_KART.OWING, CEK_KART.KEFIL, CEK_KART.MUHABIR, CEK_KART.BRANCH, CEK_KART.DEVIR,
CEK_KART.INUSE, CEK_KART.EXTENREF, CEK_KART.CAPIBLOCK_CREATEDBY, CEK_KART.CAPIBLOCK_CREADEDDATE,
CEK_KART.CAPIBLOCK_CREATEDHOUR, CEK_KART.CAPIBLOCK_CREATEDMIN, CEK_KART.CAPIBLOCK_CREATEDSEC,
CEK_KART.CAPIBLOCK_MODIFIEDBY, CEK_KART.CAPIBLOCK_MODIFIEDDATE, CEK_KART.CAPIBLOCK_MODIFIEDHOUR,
CEK_KART.CAPIBLOCK_MODIFIEDMIN, CEK_KART.CAPIBLOCK_MODIFIEDSEC, CEK_KART.COLLREPRATE, CEK_KART.COLLTRRATE,
CEK_KART.CANCELLED, CEK_KART.LINEEXCTYP, CEK_KART.TEXTINC, CEK_KART.SITEID, CEK_KART.RECSTATUS, CEK_KART.ORGLOGICREF,
CEK_KART.WFSTATUS, CEK_KART.BNBRANCHNO, CEK_KART.BNACCOUNTNO, CEK_KART.DEPTADDR1, CEK_KART.DEPTADDR2,
CEK_KART.DEPTCITY, CEK_KART.DEPTCITYCODE, CEK_KART.DEPTCOUNTRY, CEK_KART.DEPTCOUNTRYCODE, CEK_KART.DEPTPOSTCODE,
CEK_KART.DEPTTELNRS1, CEK_KART.DEPTTELNRS2, CEK_KART.DEPTFAXNR, CEK_KART.DEPTTOWN, CEK_KART.DEPTTOWNCODE,
CEK_KART.DEPTDISTRICT, CEK_KART.DEPTDISTRICTCODE, CEK_KART.OPSTAT, CEK_KART.PRINTCNT, CEK_KART.NEWSERINO,
CEK_KART.PROJECTREF, CEK_KART.FAXCODE, CEK_KART.TELCODES2, CEK_KART.TELCODES1, CEK_KART.COLLATCARDREF,
CEK_KART.COLLATROLLREF , CEK_KART.AFFECTCOLLATRL, CEK_KART.AFFECTRISK
HAVING (CEK_KART.DUEDATE >= CONVERT(DATETIME, '2009-02-17 00:00:00', 102)) AND (CEK_KART.DOC = 1) AND (ROLLER.RECSTATUS IN (1, 2))


Baglanti.Open "Provider=SQLOLEDB; Data Source=" & Server & "; Initial Catalog=" & Database & "; User ID=" & Kullanıcı & "; Password=" & Parola & ";"
KayitSeti.Open Sorgu, Baglanti

Cells(8, 1).CopyFromRecordset KayitSeti

KayitSeti.Close
Baglanti.Close
Set KayitSeti = Nothing
Set Baglanti = Nothing
End Sub

Yukarıdaki kodu başka bir bölüm de yayımlamıştım konu ile ilgilenen arkadaşımız ofline olduğundan ve konuyu mümkün mertebe daha hızlı çözüme kavuşturabilmek için yeni konu açmak zorunda hissettim kendimi...

ilgili kodu VBA editör sayfasına kopyaladığınız da kırmızı olarak görünen satırları göreceksiniz...

sizlerden istediğim kırmızı kodların hatasız olarak VBA e adapte edilmesi...veya en azından kırmızı olan 2 satır ile yol gösterilmesi ve kuralın açıklanması , diğer satırlardı da sizlerden gelecek geri döünüşler ile kendim yapabilirim.

ilgilenebilecek arkadaşalara şimdiden teşekkürler...

[lsuersoy]

Tekrar Merhabalar ;

Araştırmalarım sonucunda kuralı buldum...

Example.
Dim db as database
Dim rst as recordset
set db=currentdb
set rst = db.openrecordset("select * " & _
"from tblSomeTable " & _
"where somefield=" & somecriteria)
rst.edit
... etc ...



Teşekkürler

[lsuersoy]

Private Sub CommandButton1_Click()
Dim Baglanti As New ADODB.Connection
Dim KayitSeti As New ADODB.Recordset
Dim Firma As String, Server As String, Database As String, Kullanıcı As String, Parola As String

Firma = Format(Sheets("SETUP").Range("B5"), "000")
Server = Sheets("SETUP").Range("B1").Value
Database = Sheets("SETUP").Range("B4").Value
Kullanıcı = Sheets("SETUP").Range("B2").Value
Parola = Sheets("SETUP").Range("B3").Value

Sorgu = "SELECT CEK_KART.PORTFOYNO AS PortfoyNo," & _
"CEK_KART.SERINO AS SeriNo," & _
"CEK_KART.DOC AS Türü," & _
"CEK_KART.CURRSTAT AS Statüsü," & _
"CEK_KART.DUEDATE AS Vade," & _
"CEK_KART.SETDATE AS Tarih1," & _
"CEK_KART.AMOUNT AS Tutar," & _
"MAX(ROLLER.STATNO) AS Hareket," &_
"CARI.CODE AS [Cari Kodu]," & _
"CARI.DEFINITION_ AS Ünvanı," & _
"ROLLER.RECSTATUS," & _
"CEK_KART.OURBANKREF,&_
"CEK_KART.BANKNAME," & _
"CEK_KART.SPECODE," & _
"CEK_KART.CYPHCODE," & _
"CEK_KART.CITY," & _
"CEK_KART.OWING, " & _
"CEK_KART.KEFIL," & _
"CEK_KART.BRANCH," & _
"CEK_KART.DEVIR," & _
"CEK_KART.INUSE," & _
"CEK_KART.EXTENREF," & _
"CEK_KART.CAPIBLOCK_CREATEDBY," &_
"CEK_KART.CAPIBLOCK_CREADEDDATE ," & _
"CEK_KART.CAPIBLOCK_CREATEDHOUR," & _
"CEK_KART.CAPIBLOCK_CREATEDMIN," & _
"CEK_KART.CAPIBLOCK_CREATEDSEC," & _
"CEK_KART.CAPIBLOCK_MODIFIEDBY," & _
"CEK_KART.CAPIBLOCK_MODIFIEDDATE," &
"CEK_KART.CAPIBLOCK_MODIFIEDHOUR," & _
"CEK_KART.CAPIBLOCK_MODIFIEDMIN," & _
"CEK_KART.CAPIBLOCK_MODIFIEDSEC," & _
"CEK_KART.COLLREPRATE," & _
"CEK_KART.COLLTRRATE," & _
"CEK_KART.CANCELLED," & _
"CEK_KART.LINEEXCTYP," & _
"CEK_KART.TEXTINC," & _ "CEK_KART.SITEID," & _ "CEK_KART.RECSTATUS AS Expr1," & _
"CEK_KART.ORGLOGICREF," & _" CEK_KART.WFSTATUS, CEK_KART.BNBRANCHNO, CEK_KART.BNACCOUNTNO, CEK_KART.DEPTADDR1," & _
"CEK_KART.DEPTADDR2, CEK_KART.DEPTCITY, CEK_KART.DEPTCITYCODE, CEK_KART.DEPTCOUNTRY, CEK_KART.DEPTCOUNTRYCODE," & _
"CEK_KART.DEPTPOSTCODE, CEK_KART.DEPTTELNRS1, CEK_KART.DEPTTELNRS2, CEK_KART.DEPTFAXNR, CEK_KART.DEPTTOWN," & _
"CEK_KART.DEPTTOWNCODE, CEK_KART.DEPTDISTRICT, CEK_KART.DEPTDISTRICTCODE, CEK_KART.OPSTAT, CEK_KART.PRINTCNT," & _
"CEK_KART.NEWSERINO, CEK_KART.PROJECTREF, CEK_KART.FAXCODE, CEK_KART.TELCODES2, CEK_KART.TELCODES1," & _
"CEK_KART.COLLATCARDREF , CEK_KART.COLLATROLLREF, CEK_KART.AFFECTCOLLATRL, CEK_KART.AFFECTRISK" & _
"FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD CARI INNER JOIN" & _
"LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_CSTRANS ROLLER ON CARI.LOGICALREF = ROLLER.CARDREF INNER JOIN " & _
"LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_CSCARD CEK_KART ON ROLLER.CSREF = CEK_KART.LOGICALREF" & _
"GROUP BY CEK_KART.PORTFOYNO, CEK_KART.SERINO, CEK_KART.DOC, CEK_KART.CURRSTAT, CEK_KART.DUEDATE, CEK_KART.SETDATE, CARI.CODE," & _
"CARI.DEFINITION_, CEK_KART.AMOUNT, ROLLER.RECSTATUS, CEK_KART.OURBANKREF, CEK_KART.BANKNAME, CEK_KART.SPECODE," & _
"CEK_KART.CYPHCODE, CEK_KART.CITY, CEK_KART.OWING, CEK_KART.KEFIL, CEK_KART.MUHABIR, CEK_KART.BRANCH, CEK_KART.DEVIR," & _
"CEK_KART.INUSE, CEK_KART.EXTENREF, CEK_KART.CAPIBLOCK_CREATEDBY, CEK_KART.CAPIBLOCK_CREADEDDATE," & _
"CEK_KART.CAPIBLOCK_CREATEDHOUR, CEK_KART.CAPIBLOCK_CREATEDMIN, CEK_KART.CAPIBLOCK_CREATEDSEC," & _
"CEK_KART.CAPIBLOCK_MODIFIEDBY, CEK_KART.CAPIBLOCK_MODIFIEDDATE, CEK_KART.CAPIBLOCK_MODIFIEDHOUR," & _
"CEK_KART.CAPIBLOCK_MODIFIEDMIN, CEK_KART.CAPIBLOCK_MODIFIEDSEC, CEK_KART.COLLREPRATE, CEK_KART.COLLTRRATE," & _
"CEK_KART.CANCELLED, CEK_KART.LINEEXCTYP, CEK_KART.TEXTINC, CEK_KART.SITEID, CEK_KART.RECSTATUS, CEK_KART.ORGLOGICREF," &
"CEK_KART.WFSTATUS, CEK_KART.BNBRANCHNO, CEK_KART.BNACCOUNTNO, CEK_KART.DEPTADDR1, CEK_KART.DEPTADDR2," & _
"CEK_KART.DEPTCITY, CEK_KART.DEPTCITYCODE, CEK_KART.DEPTCOUNTRY, CEK_KART.DEPTCOUNTRYCODE, CEK_KART.DEPTPOSTCODE," & _
"CEK_KART.DEPTTELNRS1, CEK_KART.DEPTTELNRS2, CEK_KART.DEPTFAXNR, CEK_KART.DEPTTOWN, CEK_KART.DEPTTOWNCODE," & _
"CEK_KART.DEPTDISTRICT, CEK_KART.DEPTDISTRICTCODE, CEK_KART.OPSTAT, CEK_KART.PRINTCNT, CEK_KART.NEWSERINO," & _
"CEK_KART.PROJECTREF, CEK_KART.FAXCODE, CEK_KART.TELCODES2, CEK_KART.TELCODES1, CEK_KART.COLLATCARDREF," & _
"CEK_KART.COLLATROLLREF , CEK_KART.AFFECTCOLLATRL, CEK_KART.AFFECTRISK" & _
"HAVING (CEK_KART.DUEDATE >=" & CONVERT(DATETIME, '2009-02-17 00:00:00', 102)) AND (CEK_KART.DOC ="& 1) AND (ROLLER.RECSTATUS IN (1, 2))"


Baglanti.Open "Provider=SQLOLEDB; Data Source=" & Server & "; Initial Catalog=" & Database & "; User ID=" & Kullanıcı & "; Password=" & Parola & ";"
KayitSeti.Open Sorgu, Baglanti

Cells(8, 1).CopyFromRecordset KayitSeti

KayitSeti.Close
Baglanti.Close
Set KayitSeti = Nothing
Set Baglanti = Nothing
End Sub


Kuralı kendi koduma uygulamaya çalıştım ama sonuç vahim...
üstadlardan yardım bekliyorum...

[beab05]

Merhablar;

Belki syntax hatası yapıyorsunuzdur, örneğin gözüme hemen,
"CEK_KART.OURBANKREF,&_
alanında tırnak işreti olmadığı ilişti. Bence tamamen kontrol ediniz..

[lsuersoy]

bu syntax ile başım belada , mutlaka kolay bir yolunu bulmalıyım , sabahdan beri yüzlerce kez şu hatayı aldım ve bunaldım...pes ettim...dinlenmeye çekildi...çok yıprandım çok...

[Tarkan VURAL]

:D Geçmiş olsun. İyi dinlenin bari :D

[lsuersoy]

Bu kadar dinlendiğim yeter , konunun ehli gelmiş , hoş gelmiş.

selam vermiş sayarım ben sizi

bocunuzu ödeyin...

bir el atın şu kodcağıza

http://www.excel.web.tr/images/icons/icon10.gif

[modalı]

Sayın Isuersoy,

sorgu = "SELECT CEK_KART.PORTFOYNO AS PortfoyNo, CEK_KART.SERINO AS SeriNo, CEK_KART.DOC AS Türü, CEK_KART.CURRSTAT AS Statüsü,"
sorgu = sorgu & " CEK_KART.DUEDATE AS Vade, CEK_KART.SETDATE AS Tarih1, CEK_KART.AMOUNT AS Tutar,"
sorgu = sorgu & " MAX(ROLLER.STATNO) AS Hareket, CARI.CODE AS [Cari Kodu], CARI.DEFINITION_ AS Ünvanı,"
sorgu = sorgu & " ROLLER.RECSTATUS, CEK_KART.OURBANKREF, CEK_KART.BANKNAME, CEK_KART.SPECODE,"
sorgu = sorgu & " CEK_KART.CYPHCODE, CEK_KART.CITY, CEK_KART.OWING, CEK_KART.KEFIL,"
sorgu = sorgu & " CEK_KART.BRANCH, CEK_KART.DEVIR, CEK_KART.INUSE, CEK_KART.EXTENREF,"
sorgu = sorgu & " CEK_KART.CAPIBLOCK_CREATEDBY, CEK_KART.CAPIBLOCK_CREADEDDATE , CEK_KART.CAPIBLOCK_CREATEDHOUR,"
sorgu = sorgu & " CEK_KART.CAPIBLOCK_CREATEDMIN, CEK_KART.CAPIBLOCK_CREATEDSEC, CEK_KART.CAPIBLOCK_MODIFIEDBY,"
sorgu = sorgu & " CEK_KART.CAPIBLOCK_MODIFIEDDATE, CEK_KART.CAPIBLOCK_MODIFIEDHOUR, CEK_KART.CAPIBLOCK_MODIFIEDMIN,"
sorgu = sorgu & " CEK_KART.CAPIBLOCK_MODIFIEDSEC, CEK_KART.COLLREPRATE, CEK_KART.COLLTRRATE,"
sorgu = sorgu & " CEK_KART.CANCELLED, CEK_KART.LINEEXCTYP, CEK_KART.TEXTINC, CEK_KART.SITEID, CEK_KART.RECSTATUS AS Expr1,"
sorgu = sorgu & " CEK_KART.ORGLOGICREF, CEK_KART.WFSTATUS, CEK_KART.BNBRANCHNO, CEK_KART.BNACCOUNTNO, CEK_KART.DEPTADDR1,"
sorgu = sorgu & " CEK_KART.DEPTADDR2, CEK_KART.DEPTCITY, CEK_KART.DEPTCITYCODE, CEK_KART.DEPTCOUNTRY, CEK_KART.DEPTCOUNTRYCODE,"
sorgu = sorgu & " CEK_KART.DEPTPOSTCODE, CEK_KART.DEPTTELNRS1, CEK_KART.DEPTTELNRS2, CEK_KART.DEPTFAXNR, CEK_KART.DEPTTOWN,"
sorgu = sorgu & " CEK_KART.DEPTTOWNCODE, CEK_KART.DEPTDISTRICT, CEK_KART.DEPTDISTRICTCODE, CEK_KART.OPSTAT, CEK_KART.PRINTCNT,"
sorgu = sorgu & " CEK_KART.NEWSERINO, CEK_KART.PROJECTREF, CEK_KART.FAXCODE, CEK_KART.TELCODES2, CEK_KART.TELCODES1,"

şeklinde devam ederek yazın. 10 satırdan fazla alt satıra geçilemiyor.

İyi çalışmalar

[Tarkan VURAL]

Bu kadar dinlendiğim yeter , konunun ehli gelmiş , hoş gelmiş.

selam vermiş sayarım ben sizi

bocunuzu ödeyin...

bir el atın şu kodcağıza

http://www.excel.web.tr/images/icons/icon10.gif

Estağfurullah
ADO nesnelerini set etmeyi unutmayalım.
Dener misiniz ?
DefStr S
Private Sub CommandButton1_Click()
Dim Baglanti As Object, KayitSeti As Object
Set Baglanti = CreateObject("adodb.connection")
Set KayitSeti = CreateObject("adodb.recordset")

strFirma = Format(Sheets("SETUP").Range("B5"), "000")
strServer = Sheets("SETUP").Range("B1").Value
strDatabase = Sheets("SETUP").Range("B4").Value
strKullanıcı = Sheets("SETUP").Range("B2").Value
strParola = Sheets("SETUP").Range("B3").Value

s = "SELECT CEK_KART.PORTFOYNO AS PortfoyNo, CEK_KART.SERINO AS SeriNo, CEK_KART.DOC AS Türü, CEK_KART.CURRSTAT AS Statüsü,"
s = s & "CEK_KART.DUEDATE AS Vade, CEK_KART.SETDATE AS Tarih1, CEK_KART.AMOUNT AS Tutar, MAX(ROLLER.STATNO) AS Hareket,"
s = s & "CARI.CODE AS [Cari Kodu], CARI.DEFINITION_ AS Ünvanı, ROLLER.RECSTATUS, CEK_KART.OURBANKREF, CEK_KART.BANKNAME,"
s = s & "CEK_KART.SPECODE, CEK_KART.CYPHCODE, CEK_KART.CITY, CEK_KART.OWING, CEK_KART.KEFIL, CEK_KART.MUHABIR, CEK_KART.BRANCH,"
s = s & "CEK_KART.DEVIR, CEK_KART.INUSE, CEK_KART.EXTENREF, CEK_KART.CAPIBLOCK_CREATEDBY, CEK_KART.CAPIBLOCK_CREADEDDATE,"
s = s & "CEK_KART.CAPIBLOCK_CREATEDHOUR, CEK_KART.CAPIBLOCK_CREATEDMIN, CEK_KART.CAPIBLOCK_CREATEDSEC,"
s = s & "CEK_KART.CAPIBLOCK_MODIFIEDBY, CEK_KART.CAPIBLOCK_MODIFIEDDATE, CEK_KART.CAPIBLOCK_MODIFIEDHOUR,"
s = s & "CEK_KART.CAPIBLOCK_MODIFIEDMIN, CEK_KART.CAPIBLOCK_MODIFIEDSEC, CEK_KART.COLLREPRATE, CEK_KART.COLLTRRATE,"
s = s & "CEK_KART.CANCELLED, CEK_KART.LINEEXCTYP, CEK_KART.TEXTINC, CEK_KART.SITEID, CEK_KART.RECSTATUS AS Expr1,"
s = s & "CEK_KART.ORGLOGICREF, CEK_KART.WFSTATUS, CEK_KART.BNBRANCHNO, CEK_KART.BNACCOUNTNO, CEK_KART.DEPTADDR1,"
s = s & "CEK_KART.DEPTADDR2, CEK_KART.DEPTCITY, CEK_KART.DEPTCITYCODE, CEK_KART.DEPTCOUNTRY, CEK_KART.DEPTCOUNTRYCODE,"
s = s & "CEK_KART.DEPTPOSTCODE, CEK_KART.DEPTTELNRS1, CEK_KART.DEPTTELNRS2, CEK_KART.DEPTFAXNR, CEK_KART.DEPTTOWN,"
s = s & "CEK_KART.DEPTTOWNCODE, CEK_KART.DEPTDISTRICT, CEK_KART.DEPTDISTRICTCODE, CEK_KART.OPSTAT, CEK_KART.PRINTCNT,"
s = s & "CEK_KART.NEWSERINO, CEK_KART.PROJECTREF, CEK_KART.FAXCODE, CEK_KART.TELCODES2, CEK_KART.TELCODES1,"
s = s & "CEK_KART.COLLATCARDREF , CEK_KART.COLLATROLLREF, CEK_KART.AFFECTCOLLATRL, CEK_KART.AFFECTRISK "
s = s & "FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD CARI INNER JOIN"
s = s & "LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_CSTRANS ROLLER ON CARI.LOGICALREF = ROLLER.CARDREF INNER JOIN"
s = s & "LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_CSCARD CEK_KART ON ROLLER.CSREF = CEK_KART.LOGICALREF "
s = s & "GROUP BY CEK_KART.PORTFOYNO, CEK_KART.SERINO, CEK_KART.DOC, CEK_KART.CURRSTAT, CEK_KART.DUEDATE, CEK_KART.SETDATE, CARI.CODE,"
s = s & "CARI.DEFINITION_, CEK_KART.AMOUNT, ROLLER.RECSTATUS, CEK_KART.OURBANKREF, CEK_KART.BANKNAME, CEK_KART.SPECODE,"
s = s & "CEK_KART.CYPHCODE, CEK_KART.CITY, CEK_KART.OWING, CEK_KART.KEFIL, CEK_KART.MUHABIR, CEK_KART.BRANCH, CEK_KART.DEVIR,"
s = s & "CEK_KART.INUSE, CEK_KART.EXTENREF, CEK_KART.CAPIBLOCK_CREATEDBY, CEK_KART.CAPIBLOCK_CREADEDDATE,"
s = s & "CEK_KART.CAPIBLOCK_CREATEDHOUR, CEK_KART.CAPIBLOCK_CREATEDMIN, CEK_KART.CAPIBLOCK_CREATEDSEC,"
s = s & "CEK_KART.CAPIBLOCK_MODIFIEDBY, CEK_KART.CAPIBLOCK_MODIFIEDDATE, CEK_KART.CAPIBLOCK_MODIFIEDHOUR,"
s = s & "CEK_KART.CAPIBLOCK_MODIFIEDMIN, CEK_KART.CAPIBLOCK_MODIFIEDSEC, CEK_KART.COLLREPRATE, CEK_KART.COLLTRRATE,"
s = s & "CEK_KART.CANCELLED, CEK_KART.LINEEXCTYP, CEK_KART.TEXTINC, CEK_KART.SITEID, CEK_KART.RECSTATUS, CEK_KART.ORGLOGICREF,"
s = s & "CEK_KART.WFSTATUS, CEK_KART.BNBRANCHNO, CEK_KART.BNACCOUNTNO, CEK_KART.DEPTADDR1, CEK_KART.DEPTADDR2,"
s = s & "CEK_KART.DEPTCITY, CEK_KART.DEPTCITYCODE, CEK_KART.DEPTCOUNTRY, CEK_KART.DEPTCOUNTRYCODE, CEK_KART.DEPTPOSTCODE,"
s = s & "CEK_KART.DEPTTELNRS1, CEK_KART.DEPTTELNRS2, CEK_KART.DEPTFAXNR, CEK_KART.DEPTTOWN, CEK_KART.DEPTTOWNCODE,"
s = s & "CEK_KART.DEPTDISTRICT, CEK_KART.DEPTDISTRICTCODE, CEK_KART.OPSTAT, CEK_KART.PRINTCNT, CEK_KART.NEWSERINO,"
s = s & "CEK_KART.PROJECTREF, CEK_KART.FAXCODE, CEK_KART.TELCODES2, CEK_KART.TELCODES1, CEK_KART.COLLATCARDREF,"
s = s & "CEK_KART.COLLATROLLREF , CEK_KART.AFFECTCOLLATRL, CEK_KART.AFFECTRISK HAVING (CEK_KART.DUEDATE >= "
s = s & "CONVERT(DATETIME, '2009-02-17 00:00:00', 102)) AND (CEK_KART.DOC = 1) AND (ROLLER.RECSTATUS IN (1, 2))"

Baglanti.Open "Provider=SQLOLEDB; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; User ID=" & strKullanıcı & "; Password=" & strParola & ";"

KayitSeti.Open sorgu, Baglanti

Cells(8, 1).CopyFromRecordset KayitSeti

KayitSeti.Close
Baglanti.Close
Set KayitSeti = Nothing
Set Baglanti = Nothing
strFirma = vbNullString
strServer = vbNullString
strDatabase = vbNullString
strParola = vbNullString
strKullanıcı = vbNullString
s = vbNullString
End Sub

[lsuersoy]

Sayın Isuersoy,

sorgu = "SELECT CEK_KART.PORTFOYNO AS PortfoyNo, CEK_KART.SERINO AS SeriNo, CEK_KART.DOC AS Türü, CEK_KART.CURRSTAT AS Statüsü,"
sorgu = sorgu & " CEK_KART.DUEDATE AS Vade, CEK_KART.SETDATE AS Tarih1, CEK_KART.AMOUNT AS Tutar,"
sorgu = sorgu & " MAX(ROLLER.STATNO) AS Hareket, CARI.CODE AS [Cari Kodu], CARI.DEFINITION_ AS Ünvanı,"
sorgu = sorgu & " ROLLER.RECSTATUS, CEK_KART.OURBANKREF, CEK_KART.BANKNAME, CEK_KART.SPECODE,"
sorgu = sorgu & " CEK_KART.CYPHCODE, CEK_KART.CITY, CEK_KART.OWING, CEK_KART.KEFIL,"
sorgu = sorgu & " CEK_KART.BRANCH, CEK_KART.DEVIR, CEK_KART.INUSE, CEK_KART.EXTENREF,"
sorgu = sorgu & " CEK_KART.CAPIBLOCK_CREATEDBY, CEK_KART.CAPIBLOCK_CREADEDDATE , CEK_KART.CAPIBLOCK_CREATEDHOUR,"
sorgu = sorgu & " CEK_KART.CAPIBLOCK_CREATEDMIN, CEK_KART.CAPIBLOCK_CREATEDSEC, CEK_KART.CAPIBLOCK_MODIFIEDBY,"
sorgu = sorgu & " CEK_KART.CAPIBLOCK_MODIFIEDDATE, CEK_KART.CAPIBLOCK_MODIFIEDHOUR, CEK_KART.CAPIBLOCK_MODIFIEDMIN,"
sorgu = sorgu & " CEK_KART.CAPIBLOCK_MODIFIEDSEC, CEK_KART.COLLREPRATE, CEK_KART.COLLTRRATE,"
sorgu = sorgu & " CEK_KART.CANCELLED, CEK_KART.LINEEXCTYP, CEK_KART.TEXTINC, CEK_KART.SITEID, CEK_KART.RECSTATUS AS Expr1,"
sorgu = sorgu & " CEK_KART.ORGLOGICREF, CEK_KART.WFSTATUS, CEK_KART.BNBRANCHNO, CEK_KART.BNACCOUNTNO, CEK_KART.DEPTADDR1,"
sorgu = sorgu & " CEK_KART.DEPTADDR2, CEK_KART.DEPTCITY, CEK_KART.DEPTCITYCODE, CEK_KART.DEPTCOUNTRY, CEK_KART.DEPTCOUNTRYCODE,"
sorgu = sorgu & " CEK_KART.DEPTPOSTCODE, CEK_KART.DEPTTELNRS1, CEK_KART.DEPTTELNRS2, CEK_KART.DEPTFAXNR, CEK_KART.DEPTTOWN,"
sorgu = sorgu & " CEK_KART.DEPTTOWNCODE, CEK_KART.DEPTDISTRICT, CEK_KART.DEPTDISTRICTCODE, CEK_KART.OPSTAT, CEK_KART.PRINTCNT,"
sorgu = sorgu & " CEK_KART.NEWSERINO, CEK_KART.PROJECTREF, CEK_KART.FAXCODE, CEK_KART.TELCODES2, CEK_KART.TELCODES1,"şeklinde devam ederek yazın. 10 satırdan fazla alt satıra geçilemiyor.

İyi çalışmalar

Modalı'ya teşekkürler , 10 satır sorunsalını da sizden öğrenmiş oldum.

Tarkan Vural' a da teşekür ediyorum.

Farklı bir platformda sorduğum aynı soruya kendisi ivedilikle şu yanıtı vermiş ve doğru sonuç üretiyor...

DefStr S
Private Sub CommandButton1_Click()
Dim Baglanti As Object, KayitSeti As Object
Set Baglanti = CreateObject("adodb.connection")
Set KayitSeti = CreateObject("adodb.recordset")

strFirma = Format(Sheets("SETUP").Range("B5"), "000")
strServer = Sheets("SETUP").Range("B1").Value
strDatabase = Sheets("SETUP").Range("B4").Value
strKullanıcı = Sheets("SETUP").Range("B2").Value
strParola = Sheets("SETUP").Range("B3").Value

s = "SELECT CEK_KART.PORTFOYNO AS PortfoyNo, CEK_KART.SERINO AS SeriNo, CEK_KART.DOC AS Türü, CEK_KART.CURRSTAT AS Statüsü, "
s = s & "CEK_KART.DUEDATE AS Vade, CEK_KART.SETDATE AS Tarih1, CEK_KART.AMOUNT AS Tutar, MAX(ROLLER.STATNO) AS Hareket, "
s = s & "CARI.CODE AS [Cari Kodu], CARI.DEFINITION_ AS Ünvanı, ROLLER.RECSTATUS, CEK_KART.OURBANKREF, CEK_KART.BANKNAME, "
s = s & "CEK_KART.SPECODE, CEK_KART.CYPHCODE, CEK_KART.CITY, CEK_KART.OWING, CEK_KART.KEFIL, CEK_KART.MUHABIR, CEK_KART.BRANCH, "
s = s & "CEK_KART.DEVIR, CEK_KART.INUSE, CEK_KART.EXTENREF, CEK_KART.CAPIBLOCK_CREATEDBY, CEK_KART.CAPIBLOCK_CREADEDDATE, "
s = s & "CEK_KART.CAPIBLOCK_CREATEDHOUR, CEK_KART.CAPIBLOCK_CREATEDMIN, CEK_KART.CAPIBLOCK_CREATEDSEC, "
s = s & "CEK_KART.CAPIBLOCK_MODIFIEDBY, CEK_KART.CAPIBLOCK_MODIFIEDDATE, CEK_KART.CAPIBLOCK_MODIFIEDHOUR, "
s = s & "CEK_KART.CAPIBLOCK_MODIFIEDMIN, CEK_KART.CAPIBLOCK_MODIFIEDSEC, CEK_KART.COLLREPRATE, CEK_KART.COLLTRRATE, "
s = s & "CEK_KART.CANCELLED, CEK_KART.LINEEXCTYP, CEK_KART.TEXTINC, CEK_KART.SITEID, CEK_KART.RECSTATUS AS Expr1, "
s = s & "CEK_KART.ORGLOGICREF, CEK_KART.WFSTATUS, CEK_KART.BNBRANCHNO, CEK_KART.BNACCOUNTNO, CEK_KART.DEPTADDR1, "
s = s & "CEK_KART.DEPTADDR2, CEK_KART.DEPTCITY, CEK_KART.DEPTCITYCODE, CEK_KART.DEPTCOUNTRY, CEK_KART.DEPTCOUNTRYCODE, "
s = s & "CEK_KART.DEPTPOSTCODE, CEK_KART.DEPTTELNRS1, CEK_KART.DEPTTELNRS2, CEK_KART.DEPTFAXNR, CEK_KART.DEPTTOWN, "
s = s & "CEK_KART.DEPTTOWNCODE, CEK_KART.DEPTDISTRICT, CEK_KART.DEPTDISTRICTCODE, CEK_KART.OPSTAT, CEK_KART.PRINTCNT, "
s = s & "CEK_KART.NEWSERINO, CEK_KART.PROJECTREF, CEK_KART.FAXCODE, CEK_KART.TELCODES2, CEK_KART.TELCODES1, "
s = s & "CEK_KART.COLLATCARDREF , CEK_KART.COLLATROLLREF, CEK_KART.AFFECTCOLLATRL, CEK_KART.AFFECTRISK "
s = s & "FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD CARI INNER JOIN "
s = s & "LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_CSTRANS ROLLER ON CARI.LOGICALREF = ROLLER.CARDREF INNER JOIN "
s = s & "LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_CSCARD CEK_KART ON ROLLER.CSREF = CEK_KART.LOGICALREF "
s = s & "GROUP BY CEK_KART.PORTFOYNO, CEK_KART.SERINO, CEK_KART.DOC, CEK_KART.CURRSTAT, CEK_KART.DUEDATE, CEK_KART.SETDATE, CARI.CODE, "
s = s & "CARI.DEFINITION_, CEK_KART.AMOUNT, ROLLER.RECSTATUS, CEK_KART.OURBANKREF, CEK_KART.BANKNAME, CEK_KART.SPECODE, "
s = s & "CEK_KART.CYPHCODE, CEK_KART.CITY, CEK_KART.OWING, CEK_KART.KEFIL, CEK_KART.MUHABIR, CEK_KART.BRANCH, CEK_KART.DEVIR, "
s = s & "CEK_KART.INUSE, CEK_KART.EXTENREF, CEK_KART.CAPIBLOCK_CREATEDBY, CEK_KART.CAPIBLOCK_CREADEDDATE, "
s = s & "CEK_KART.CAPIBLOCK_CREATEDHOUR, CEK_KART.CAPIBLOCK_CREATEDMIN, CEK_KART.CAPIBLOCK_CREATEDSEC, "
s = s & "CEK_KART.CAPIBLOCK_MODIFIEDBY, CEK_KART.CAPIBLOCK_MODIFIEDDATE, CEK_KART.CAPIBLOCK_MODIFIEDHOUR, "
s = s & "CEK_KART.CAPIBLOCK_MODIFIEDMIN, CEK_KART.CAPIBLOCK_MODIFIEDSEC, CEK_KART.COLLREPRATE, CEK_KART.COLLTRRATE, "
s = s & "CEK_KART.CANCELLED, CEK_KART.LINEEXCTYP, CEK_KART.TEXTINC, CEK_KART.SITEID, CEK_KART.RECSTATUS, CEK_KART.ORGLOGICREF, "
s = s & "CEK_KART.WFSTATUS, CEK_KART.BNBRANCHNO, CEK_KART.BNACCOUNTNO, CEK_KART.DEPTADDR1, CEK_KART.DEPTADDR2, "
s = s & "CEK_KART.DEPTCITY, CEK_KART.DEPTCITYCODE, CEK_KART.DEPTCOUNTRY, CEK_KART.DEPTCOUNTRYCODE, CEK_KART.DEPTPOSTCODE, "
s = s & "CEK_KART.DEPTTELNRS1, CEK_KART.DEPTTELNRS2, CEK_KART.DEPTFAXNR, CEK_KART.DEPTTOWN, CEK_KART.DEPTTOWNCODE, "
s = s & "CEK_KART.DEPTDISTRICT, CEK_KART.DEPTDISTRICTCODE, CEK_KART.OPSTAT, CEK_KART.PRINTCNT, CEK_KART.NEWSERINO, "
s = s & "CEK_KART.PROJECTREF, CEK_KART.FAXCODE, CEK_KART.TELCODES2, CEK_KART.TELCODES1, CEK_KART.COLLATCARDREF, "
s = s & "CEK_KART.COLLATROLLREF , CEK_KART.AFFECTCOLLATRL, CEK_KART.AFFECTRISK HAVING (CEK_KART.DUEDATE >= "
s = s & "CONVERT(DATETIME, '2009-02-17 00:00:00', 102)) AND (CEK_KART.DOC = 1) AND (ROLLER.RECSTATUS IN (1, 2))"

Baglanti.Open "Provider=SQLOLEDB; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; User ID=" & strKullanıcı & "; Password=" & strParola & ";"

KayitSeti.Open s, Baglanti, 1, 1

Cells(8, 1).CopyFromRecordset KayitSeti

KayitSeti.Close
Baglanti.Close
Set KayitSeti = Nothing
Set Baglanti = Nothing
strFirma = vbNullString
strServer = vbNullString
strDatabase = vbNullString
strParola = vbNullString
strKullanıcı = vbNullString
s = vbNullString

End Sub


TEŞEKKÜRLER