Makale Microsoft Activex Data Objects (ADO)

Bu Makale Yararlı Oldu mu?

  • Evet

  • Hayır


Sonuçlar yalnızca oylamadan sonra görülebilir.

Admin

Yönetici
Site Yöneticisi
Katılım
25 May 2018
Mesajlar
1,054
En İyi Yanıtlar
77
Beğeniler
855
Puanları
113
Konum
İstanbul
#1
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. (y)

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 Seti'ndeki 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.

VBA:
Sub ExcelTurkey()
    Dim Con As Object, Rs As Object, Sorgu As String
    Set Con = CreateObject("AdoDB.Connection")
    Set Rs = CreateObject("AdoDB.RecordSet")
    Con.Open "Provider=Microsoft.Ace.Oledb.12.0;Data Source=" & ThisWorkbook.Path & "\kaynak.xlsx" & _
    ";Extended Properties=""Excel 12.0;Hdr=yes"""
    Sorgu = "Select Adı, Soyadı, [Doğum Yeri] From [Sayfa1$]"
    Rs.Open Sorgu, Con, 1, 1
    Range("A2").CopyFromRecordset Rs
    Rs.Close: Con.Close
    Sorgu = vbNullString: Set Rs = Nothing: Set Con = Nothing
End Sub
Ek'te örnek dosyaları da bulabilirsiniz. (y)

devam edecek.. (y)
 

Ekli dosyalar

behçet

Yeni Üye
Katılım
3 Tem 2018
Mesajlar
1
En İyi Yanıtlar
0
Beğeniler
0
Puanları
1
Yaş
63
Konum
erbaa
Ad Soyad
Behçet Yayıkçı
#2
Güzel bir anlatım. Teşekkür ederiz.
 
Moderatör tarafında düzenlendi:
Katılım
3 Tem 2018
Mesajlar
3
En İyi Yanıtlar
0
Beğeniler
0
Puanları
1
Yaş
37
Konum
antalya
Ad Soyad
Ahmet Şahin
#3
Sayın Admin merhaba,

Dosyalarda şifre varsa veya bağlantıdan sonra şifrelenirse ado bağlantı dosyayı açarken sorun yaşar mı?

Aşağıdaki gibi dosya şifreleme yapılırsa yani.

1530688735794.png

Teşekkürler
 

Admin

Yönetici
Site Yöneticisi
Katılım
25 May 2018
Mesajlar
1,054
En İyi Yanıtlar
77
Beğeniler
855
Puanları
113
Konum
İstanbul
#4
Merhaba @ahmetsahin,

Açılış şifresi olan dosyalara da bağlanıp veri çekebilir veya gönderebilirsiniz fakat şifreyi sizden isteyecektir.
Şifreyi girdikten sonra işlemlerinizi gerçekleştirebilirsiniz. Ya da arka plânda şifreli dosyayı açtırırsınız ama göstermezsiniz, sonra da o açık olan dosyadan veriler çekebilir veya gönderebilirsiniz.
 
Katılım
3 Tem 2018
Mesajlar
3
En İyi Yanıtlar
0
Beğeniler
0
Puanları
1
Yaş
37
Konum
antalya
Ad Soyad
Ahmet Şahin
#5
Teşekkür ederim.

Dosyaları arka planda açtırmak istemiyorum çünkü birkaç farklı dosyaya arka arkaya otomatik bağlanınca hepsi bilgisayarın ram kapasitesini dolduruyor ve bir süre sonra hata veriyor. dosyayı açmadan direk olarak verileri alsın istiyorum ama her seferinde şifre girmek istemiyorum.

Peki server üzerinde 5 ayrı kullanıcının doldurduğu aynı formattaki 5 dosyadan verileri çekip tek bir dosyada toplamak için;

tüm dosyaların şifreli olduğunu düşünürsek, Pivot tablo/power pivot ile mi verileri çekmek daha mantıklı, yoksa ado ile mi çekmek daha mantıklı?

Saygılarımla
 

Admin

Yönetici
Site Yöneticisi
Katılım
25 May 2018
Mesajlar
1,054
En İyi Yanıtlar
77
Beğeniler
855
Puanları
113
Konum
İstanbul
#6
Arka plânda açmaktan kastım tam olarak anladığınız biçimde değil.
Arka plânda açtığınızda görev yöneticisinde de görünmeyen, hepsi için bir Excel oluşturmayan, dolayısıyla ram kapasitenizi kullanmayan, ayrı bir Excel olarak değil de tek bir Excel ile açılmasını sağlayan bir durumdan bahsediyorum.

Kod haricinde parola korumalı dosyalardan veri alırken bildiğim kadarıyla "Dosya bozuk veya içeriği okunamıyor" hatası alırsınız. O yüzden ADO ile parola girerek sorgulamak veya birleştirmek daha uygun olacaktır.
 
Katılım
3 Tem 2018
Mesajlar
3
En İyi Yanıtlar
0
Beğeniler
0
Puanları
1
Yaş
37
Konum
antalya
Ad Soyad
Ahmet Şahin
#7
Anladım sayın Admin. Teşekkür ederim.
Ado ile parola girerek sorgulama yaparken, şifreyi makro içeriğine yazma imkanı oluyor mu yoksa her seferinde elle mi girmemiz gerekiyor?

Saygılarımla
 
Üst Alt