Herkese merhaba,

ADO (ActiveX Data Objects – Veri Erişim Nesneleri)

Bu yazıda sizlere,  ADO nesnesinden ve onun gücünden bahsetmeye çalışacağım.
Şöyle düşünün; Türkiye adında bir klasörünüz var, o klasör içinde 7 bölgeye ait klasörler mevcut, her bölgenin içinde de şehirlere ait klasörler var, bunların içinde de o şehirlerdeki farklı ilçelerdeki bayilere ait klasörler mevcut, o klasörlerin içinde de 1’den 12’ye kadar ay isimlerinin olduğu klasörler var ve son olarak da o klasörlerin içinde şablonları aynı olan, Excel olarak tutulan satış  listeleri var.  ADO ile yüzlerce hatta binlerce Excel dosyasını açmanıza gerek kalmadan, o dosyalara bağlanıp SQL sorgularının da gücünü kullanarak, aradığınız veriye kolayca ulaşabilir veya hızlıca raporlar elde edebilirsiniz. Tüm dosyalardaki verileri tek bir dosyada alt alta listeleyebilir, iki tarih arası raporlar oluşturabilir, belirttiğiniz şartlara göre verileri kolayca listeleyebilirsiniz.

ADO‘yu genel olarak şöyle tanımlayıp konumuza başlayalım; Microsoft platformunun özelliklerini destekleyen Veritabanı uygulamalarına ve konumuzla ilgili olarak Kapalı Excel, Access türündeki dosyalara erişimi sağlayan, SQL sorguları ile hızlı biçimde raporlamalar ve veri gönderimleri yapma imkânı sunan bir nesnedir.

Bu nesne ile ilgili Excel dosyalarına bağlanma, o dosyalardaki tablolara (sayfalara) ulaşma ve istediğimiz raporu SQL sorguları ile elde etme noktasında sizi bilgilendirmeye çalışacağım.

Basit bir örnek yapalım ve kodları satır satır izah ederek daha iyi anlayalım.
Öncelikle makromuz içerisinde kullanacağımız Değişkenlerimizi tanımlayalım. Bize gerekli olan 3 (üç) değişkenimiz var.

ADO’nun Connection ve RecordSet özelliği (sınıfı) ile SQL sorgumuzu içine yazacağımız bir değişkene ihtiyacımız var.
Bağlantı ve Kayıt Setini Object değişkeni olarak tanımlıyoruz. SQL sorgumuz için de, sorgu adında ve metin (String) türünde bir değişken tanımlıyoruz.

Dim Con As Object, Rs As Object, Sorgu As String

Bu nesneyi VBA’da iki yöntemle oluşturabilirsiniz. (Early Binding ve Late Binding) Bu durum genel olarak tüm nesneler için geçerlidir.

1.si: VBE Menüsünde Tools/References/Microsoft Activex Data Objects XX Library referansını seçerek.
Bu işlemi yaptıktan sonra nesnenin Connection özelliğini (sınıfı) şu şekilde değişken olarak tanımlayabilirsiniz;
Dim Con As ADODB.Connection

2.si: Kod içerisinde nesneyi Set ederek. Bu yöntemde, örneğin ADO’nun Connection özelliği (sınıfı) şu şekilde oluşturulur;
Set Con = CreateObject("AdoDB.Connection")

Bir kayıt seti oluşturmak ve sorgudan alınacak verileri bu kayıt seti içerisine almak için; RecordSet özelliğini kullanabiliriz.
Kod içerisinde bu şekilde Set ediyoruz; Set Rs = CreateObject("AdoDB.RecordSet")

Şimdi kapalı bir Excel dosyasına bağlanalım. Bunu için bağlantı metnimizi (Connection String) yazıyoruz.
Öncelikle bağlantımızı açıyoruz.. (Tüm bağlantı metinleri için buraya bakabilirsiniz.)

Eğer bağlanmak istediğiniz Excel dosyanız 2007 versiyonundan önce ise; Microsoft.Jet.Oledb.4.0 veritabanı altyapısını ve Excel 8.0 versiyonunu kullanabilirsiniz. Excel dosyanız 2007 ve sonraki versiyonlarda ise; Microsoft.Ace.Oledb.12.0 veritabanı altyapısını ve Excel 12.0 versiyonunu kullanabilirsiniz.

Aşağıdaki bağlantı metninin bize ne anlattığını yazmadan önce, metinde kırmızı renk ile belirttiğim kısımları izah edeyim;
ThisWorkbook.Path şu demek; “Bu çalışma kitabının içerisinde bulunduğu dizini (dosyanın yolunu) ifade eder.
Hdr şu demek; “Hdr = Header (Üst bilgi /Başlık) ‘ın kısaltmasıdır ve bağlanacağınız Excel dosyanızdaki ilk satırı, yani başlığınızın (Adı–Soyadı–Doğum Yeri gibi) olup olmadığını, varsa onu dikkate alıp almayacağını belirttiğiniz kısımdır. Eğer Hdr=yes derseniz; sorgu içerisinde ilk satırda bulunan başlık isimlerini kullanmanız gerekir, Hdr=no derseniz de; veri olan sütunları F1, F2, F3 şeklinde belirtmelisiniz. F1 = Field1 yani 1.Alan (sütun) demektir.

Aşağıdaki bağlantı metninde şunu dedik; ADO’nun Connection özelliğini (sınıfını) con isimli bir değişkene Object türünde Set etmiştik, bu bağlantıyı Open metoduyla ile açıyoruz ve Microsoft.Ace.Oledb.12.0 veritabanı altyapısı ile, bu çalışma kitabının içinde bulunduğu dizindeki kaynak.xlsx isimli dosyaya bağlanıp, Hdr ile ilk satırda başlıklarımızın olduğunu belirtiyoruz.

Con.Open “Provider=Microsoft.Ace.Oledb.12.0;Data Source=” & ThisWorkbook.Path & “kaynak.xlsx” & _
“;Extended Properties=””Excel 12.0;Hdr=yes”””

Belirtmiş olduğumuz Excel dosyasına bağlanıp bağlanmadığını State özelliği ile bu şekilde; MsgBox con.State öğrenebilirsiniz. MsgBox sonucunda 0(sıfır) alırsanız dosyaya bağlanamadınız demektir. O zaman önce bağlantı metnini kontrol etmeniz gerekir. Eğer sonuç 1 (bir) olarak dönerse o zaman dosyaya bağlanmışsınızdır. Bağlanmadığı taktirde, bağlanana kadar bağlantı metnini kontrol edip düzeltmelisiniz.

Dosyaya bağlandığımızı düşünerek devam edelim..

Şimdi SQL sorgumuzu yazacağız. Sorgu adında String türünde bir değişken tanımlamıştık. SQL sorgumuzu bu sorgu isimli değişkenimize yazdıracağız.

sorgu = “Select Adı, Soyadı, [Doğum Yeri] From [Sayfa1$]”

Not: Select komutuyla ilgili detayları burada bulabilirsiniz.

Sonrasında bu Sorgu sonucunda oluşan verilerle işlem yapabilmek için öncelikle Kayıt Setini Open metoduyla açmamız gerekir.
RecordSet’in açılma şartları vardır.. Bunları da bilmeniz iyi olur.

Kayıt Setini açmak için şu yazım kalıbını kullanıyoruz.
Rs.Open SQL Cümlesi , Bağlantı Nesnesi, Kayıt Seti İşlem Tipi, Kayıt Seti Kilitleme Tipi

İşlem Tipi ve Kilit Tipi için, nedir, neyi ifade eder, nasıl kullanılırı başka bir makalede anlatımını yapacağım..

Kayıt Setini bu şekilde açıp, sorgu sonucunda oluşan verileri de Kayıt Seti içerisine yüklüyoruz.
Rs.Open Sorgu, Con, 1, 1

Sonrasında verileri nereye aktaracağınıza bağlı olarak değişiklik gösterir.
Verilerinizi Excel hücrelerine veya ComboBox, ListBox gibi UserForm nesnelerine aktarabilirsiniz.
Biz şimdilik A2 hücresinden itibaren tüm verilerimizi aktaracak şekilde kodlarımızı yazacağız.

Kayıt Seti içerisindeki tüm verileri A2 hücresinden itibaren tek seferde aktarmak için, Range’in CopyFromRecordSet metodunu kullanabilirsiniz.
Range("A2").CopyFromRecordset Rs

En sonunda Kayıt Seti ve Dosya Bağlantısını kapatıyoruz.
Rs.Close: Con.Close

Değişkenlerin aldıkları değerleri, değişken içeriklerini boşaltarak temizliyoruz. Bunu yapmanız şart değil ama alışkanlık edinirseniz daha sonraki kodlamalarınızda olası bazı sorunların önüne geçmiş olursunuz. Bu kodlarla, kullandığımız değişkenlerin içeriğini boşaltabilirsiniz.

Sorgu = vbNullString: Set Rs = Nothing: Set Con = Nothing 

Şimdiye kadar yaptığımız işlemleri bir listeleyelim, bundan sonraki ADO işlerinizde bu sırayla gidersiniz.

• Kullanacağımız Bağlantı, Kayıt Seti ve Sorgu değişkenlerini tanımladık.
• Late Binding yöntemiyle Bağlantı ve Kayıt Setimizi Set ederek tanımladık.
• Connection String (Bağlantı metni) belirterek, Open metoduyla kapalı dosyaya bağlandık.
• Almak istediğimiz verilere göre SQL Sorgumuzu belirttik.
• Kayıt Setimizi Open metoduyla açıp, sorgu sonucu oluşan verileri Kayıt Setine aldık.
• Kayıt Setindeki tüm verileri A2 hücresinden itibaren aktardık.
• Kayıt Seti ve Dosya Bağlantısını kapattık.
• Kullandığımız değişkenlerini içeriklerini boşalttık.

Tüm bu işlemleri aşağıda belirtmiş olduğum kodda görebilirsiniz. Bu sayede ADO & SQL ‘in gücünü kullanarak Kapalı Dosyalara bağlanıp oldukça hızlı bir şekilde raporlamalar yapabilirsiniz. Sıradan makroya göre kıyaslanamayacak derece hızlı sonuç döndürürler.