SQL/Access

EXCEL MAKRO İLE VERİTABANI İŞLEMLERİ

1 - ActiveX Data Object Library/DataBase Nedir?

    Excel Makro İle Veritabanı İşlemleri Dersinde Access ve SQL veri tabanlarına bağlanarak, verilerin excel worksheet üzerinde görüntülenmesi ve sorgulanması gibi işlemlerin nasıl gerçekleştirildiğini inceleyeceğiz. 

    Excel üzerinde yazdığımız makroların etki alanı genişledikçe dikkat ettiyseniz, makronun etkileceği yani çalışacağı uygulama ile ilgili olarak 

    Tools–>References penceresinden, 

    ilgili kütüphaneyi referanslar listesine ekleriz. Bu sayede ActiveX Data Object Library’e ait olan ve normalde açık olmayan metotlar, fonksiyonlar, sınıflar, enum, const v.b. açarız ve nesneler üzerinde işlem yaparken kullanabiliriz. Bir önceki Ders 29’da kullanılan kütüphane, “Microsoft Word Object Library” idi. Şimdi ise Access ve SQL veritabanlarına bağlanabilmemize olanak veren bütün metot, enum, const, property vb içinde barındıran kütüphanin adı Microsoft ActiveX Data Objects 6.1 Library‘dir.

    İlk iş olarak bu kütüphaneniyi References listesinde buluyoruz ve yanındaki kutuya tiki koyarak kullanılabilir hale getiriyoruz.

Excel Makro İle Veritabanı İşlemleri-References Penceresi

    #Aşağıdaki açıklamaları Microsoft’un iligili resmi sayfasından elimden geldiğince sizin için çevirdim. Linki altta bulabilirsiniz.(Sayfanın Türkçe’si de var ancak ben beğenmediğim için kısa ve basit şekilde tekrar çevirdim. )  

    ADO(ActiveX Data Objects), PHP gibi back-end* bir yazılımın üzerinde çalışmaz ve sadece bir programlama modelidir. Bu programlama modeli ise sadece OLE DB tarafından desteklenmektedir. OLE DB**(Object Linking and Embedding Database). 

    ADO Nesne Modeli 6 Adet Nesneden Oluşmaktadır.

    –Bağlantı: Bir OLE DB kaynağına kurulan bağlantıyı ifade eder.

    –Error:Verilere erişim esnasında yaşanan hataların ayrıntılarından oluşur.

    –Command: Verilerde gerçekleştirmek istediğiniz emirler, komutlardır.

    –Parameters: Komutlar ile beraber verebileceğiniz zorunlu olmayan bildirimlerdir.

    –Recordset: Bir tablo veya veri kaynağındaki verilerdir.

    –Field:Bir recordset içerisindeki tek sütundan oluşan veridir.

    –Property: Sağlayıcı tarafından ADO için yapılmış değerler derlemesidir. 

 

Detaylı bilgi ve websitesi:https://support.microsoft.com/en-us/help/168335/info-using-activex-data-objects-ado-via-visual-basic

Not_1:

    *Back-End: Bir program veya websitesinin bizim görmediğimiz, mantıksal yapısnı oluşturan arka planıdır. Server, uygulama ve veritabanının beraber çalışmasıdır.

    **OLE DB: Microsoft’un veritabanlarına farklı kaynaklardan bağlanılabilmesine müsade eden düşük profilli bir kullanıcı arayüzüdür. Daha detaylı açıklamalar için: https://searchsqlserver.techtarget.com/definition/OLE-DB

 

1.1 - Sub-Routine Oluşturmak-Early Binding/Late Binding

    Makro oluşturmadan önce Late Binding ve Late Binding kavramlarını tekrar açıklayalım. Eğer yazdığınız makroyu sadece siz kullanacaksanız daha doğrusu Office’in farklı bir versiyonunda çalıştırmayacaksanız o zaman Late Binding kısmını dert etmenize gerek yok. Ancak yazdığınız makro farklı kişilere dağıtılacak ve kullanılacaksa, Late Binding değişikliklerini makronuza uygulamak durumundasınız. 

    Late Binding, yazmak istenilen makronun herhangi bir kütüphaneye referanslanmadan yazılmasıdır. Bir nesne oluşturulurken “CreateObject” metodu kullanılır. Ancak en büyük dezavantajı Late Binding ile yazılan makrolarda, kod yazan kişinin en büyük yardımcısı olan “IntelliSense”in kapalı olmasıdır. Herhangi bir kütüphane açık olmadığı için, IntelliSense listesi de boştur. Kısaca Late Binding tekniği ile makro yazmak çok akıl işi değildir. Çünkü bütün metot, property, fonksiyon, enum, const ve sınıfları ezbere biliyor olmanız lazım. Böyle birşeye de gerek olmadığı için en mantıklsı, referanslama işlemi yaparak normal şekilde, yani Early Binding metodu ile makroyu oluşturmak, elimizden geldiğinde IntelliSense’den faydalanmak, sonrasında ise kodu revize etmektir. Olaya bu açıdan baktığımızdan Late Binding; “makronuzu yazdıktan sonra bütün versiyonlar tarafından kullanılabilmesi için yapacağınız değişiklikleri ifade eder”, bir şekle dönüşür. Şimdi Late Binding ve Early Binding tekniklerini, değişkenleri oluşturma evresinde görelim. 

1.1.1 - Early Binding

Excel Makro İle Veritabanı İşlemleri-Early Binding

    # Baglanti yerine kullanacağımız değişken direk olarak bağlantı tipinde tanımlanır ve yeni bağlantı olarak değer verilir.

    # ActiveX Data Object 6.1 Data Library Referansı yapılmıştır.

    #Intellisense Kullanılabilir

    #Bütün Constant ve Enumeration isimleri ile kullanılabilir.(LateBinding tekniğinde rakamsal karşılığı kullanılır. İsimler kullanılmaz)

1.1.2 - Late Binding

Excel Makro İle Veritabanı İşlemleri-Late Binding

 

    #Bağlantı bir nesne olarak tanımlanır ve sonrasında da “CreateObject” metodu kullanılarak bir “ADODB.Connection” oluşturulur.

    #Constant ve Enumerations isimleri ile değil, aldıkları sayılarla yazılmalıdır. Yani constant adını biliyorsak dahi yetmez, yerine tekabül eden sayıyı yazmak zorundayız. 

    #IntelliSense çalışmaz.

     Şimdi hem late ve early binding arasındaki farkı gördüğümüze göre, bize kodumuza early binding metodu ile devam edelim. Ancak makro yazmaya başlamadan önce, hepimizin en çok hata ile karşılaşabileceği kısım olan bağlantı kodunun ne olduğunu ve nasıl yazacağımızı öğrenelim. Sonra makro içerisinde kullanabiliriz.

2 - ConnectionString Nedir?

    ConnectionString, veri tabanına bağlanabilmek için, içerisinde veri tabanı adı, veritabı sağlayıcısı, veritabanı dosya yolu, güvenlik bilgileri vb gibi bilgilerin de bulunduğu bir anahtardır, bir dizidir. Her farklı veritabanı için ve her veritabanının farklı sürümleri için ConnectionString değişiklik gösterir. Bu nedenle ezberlemek yerine en mantıklı seçim internetten istediğimiz bu kodu bulmak ve yazmaktır. Bu konuda en kapsamlı site  https://www.connectionstrings.com/ sitesidir. Burada aradığınız bütün connectionstring’leri bulabilirsiniz. 

    Hem Access veritabanına hem de SQL veri tabanına bağlanacağımız için makromuzu oluşturmadan önce bu ConnectionString’leri hazırlayalım.

2.1 - Access Veritabanı İçin ConnectionString Oluşturmak

    Yukarıda bahsettiğimiz  https://www.connectionstrings.com/ sitesini açıyoruz ve Access’i tıklıyoruz.  Bu sayede Access’in farklı versiyonları ve farklı seçenekler için hazırlanmış olan ConnectionString’leri görebiliyoruz. Biz bu çalışmamızda Access 2007 2010 2013 versiyonları ile uygun olan standart sürümü kullanacağız. Eğer farklı versiyonda çalışıyorsanız, ilgili versiyonun adının altında yazılı olan ConnectionString’i seçmelisiniz. Bizim kullanacağımız Connection String’in görüntüsü;

Excel Makro İle Veritabanı İşlemleri-StandartSecurity_ConnectionString

    Bu gördüğünüz ConnectionString bizim access veritabanına bağlanmamızı sağlayacak. Ancak daha öncesinde üzerinde birkaç değişiklik yapmamız gerekiyor.

2.1.1 - Connection String'in Referans Olarak Atanması ve Dosya Yolunun Değiştirilmesi(accdb)

    Sub-Routine içerisinde tanımlamış olduğumuz 

                     Dim Baglanti_Envanter As New ADODB.Connection

                     Set Baglanti_Envanter = New ADODB.Connection

    Baglanti_Envanter değişkenimizin ConnectionString özelliğine, yukardaki kodu tırnak işareti içinde yapıştırıyoruz. Ve sonrasın ise kendi Access dosyamızın dosya yolunu ve adını “Source” içerisine yazıyoruz. 

    Excel Makro İle Veritabanı İşlemleri-ConnectionString-Modification

    Biraz daha yaklaşırsak aradaki farkı daha rahat görebiliriz.

    Gördüğünüz gibi yaptığımız 2 adet işlem var. 

    ilk işlem: Baglanti_Envanter.ConnectionString=”” –>Tırnak işareti içerisine internet sitesinden aldığımız ConnectionString’i yazıyoruz.

   İkinici İşlem: Source, yani kaynağa kendi access dsoyamızın olduğu dosya yolunu ve dosya adını girerek, kendi dosyamıza bağlanabilmeyi sağlıyoruz.

   

2.1.2 - Bağlantının Çalışıp Çalışmadığının Kontrol Edilmesi

    Bağlantıyı kontrol etmek için bağlantımızı açıyoruz ve kapatıyoruz. Eğer bu işlemler esnasında herhangi bir hata ile karşılaşmazsak eğer bağlantının sorunsuz kurulduğunu rahatlıkla söyleyebiliriz. Bu aşamaya kadar bağlantının açılıp kapatılması dahil hazırlamış olduğumuz kodumuz;

Excel Makro İle Veritabanı İşlemleri-Sub_1

     Bağlantınızı, nesneleri görmemizi sağlayan “Locals” penceresinde de görebilirsiniz. Tam olarak ConnectionString sütun genişliğine sığmamış olsa da,

 karşılaşacağınız görüntü aşağıdaki gibi olacaktır.

  Excel Makro İle Veritabanı İşlemleri- LocalsViewOfConnectionString

2.1.3 - Access ConnectionString'in Bir Constant Olarak Tanımlanması

    Bu yaptığımız işlem mecburi bir işlem değildir ancak yapmamız kendi işlerimizi kolaylaştıracaktır. Bu kullandığımız ConnectionString, bir proje içerisinde birden fazla Sub-Routine içerisinde kullanılabilir. Bu durumda da tek tek bütün Sub-Routine’lerde birçok kez bütün ConnectionString’i yazmak yerine, bir constant haline getirip, constant halinde kullanmak bizim için daha avantajlı olacaktır. 

    Bu çerçevede modülün en üstünde bu ConnectionString’i constant olarak tanımlayıp, Sub-Routine’de de bu constant’ı kullanıyor olacağız. Aşağıda bu işlemler sonrasındaki Sub-Routine’i görebilirsiniz.

Excel Makro İle Veritabanı İşlemleri-ConnectionStringAsConstant

    Artık bu modül içerisindeki bütün Sub-Routine’ler AccBaglanti constant’ı ile Access Veritabanına bağlanabilecek. 

   Access için ConnectionString’i oluşturduğumuza göre, şimdi sıra SQL için ConnectionString oluşturmada.

2.2 - SQL Veritabanı İçin ConnectionString Oluşturmak

    Excel makro ile veritabanı işlemleri dersinde bağlantı kurmaya çalışacağımız diğer bir veritabanı ise dünyada kullanılan en yaygın veritabanı olan SQL Veritabanıdır. Yine ilgili ConnectionString’i internet sitesinden temin edebilirsiniz. Burada ben direk olarak aşağıda sizinle kodu paylaşıyorum.

Not_2:

    Bu noktadan sonra işlemleri devam ettirebilmek açısından SQL Server Management Studio’nun bilgisayarınızda yüklü olduğundan emin olmanız gerekmektedir.

SQL veritabanı için kullanacağımız ConnectionString;

    Provider=SQLNCLI11;SERVER=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=Yes;

2.2.1 - Connection String'in Referans Olarak Atanması ve Dosya Yolunun Değiştirilmesi(SQL)

SQL veritabanına bağlanmak için hazırladığımız Sub-Routine ile Access veritabanına bağlanmak için hazırladığımız veritabanı arasında tek fark kullandığımız connectionstring’lerdir. Bu sebepten ötürü, değişken tanımlama ve referanslama tanımını burada es geçiyoruz ve direk olarak connection string’in nasıl kendi bilgisayarımızda uyarlayacağımıza geçiyoruz. 

    Sub-Routine’i İnceleyelim;

Excel Makro İle Veritabanı İşlemleri - SQLServer ConnectionString

    Access veritabanına bağlanmak için yaptığımız işlemin aynısını gerçekleştiriyoruz. ConnectionString’i kendi Sub-Routine’imiz içerisine kopyaladıktan sonra değiştirmemiz gereken yerleri değiştiriyoruz. Yukarıdaki örnekte gayet net bir şekilde hangi kısımları kendimize göre uyarlamamız gerektiğini görebiliriz. Ayrıca connectionstring’i tırnak işareti içerisine almayı da kesinlikle unutmuyoruz.(zaten bir string diye düşünürsek, tırnak işareti içine almamak gibi bir seçeneğimiz zaten yok.)

    Yazdığımız kod ile veritabanına bağlanmakta sıkıntı yaşayıp yaşamadığımızı görmek için, kodu yine çalıştırarak deneme yapıyoruz. Eğer bir hata ile karşılaşmadıysanız, büyük ihtimalle kod sağlıklı bir şekilde çalışıyor demektir.

    Peki biz myServerName, theInstance ve myDataBase ifadelerinin yerine ne yazacağımızı nereden öğreneceğiz??

2.2.2 - myServerName, theInstanceName ve myDataBase

    SQL veritabanına bağlandığınız pencerede direk olarak bu bilgileri bulabilirsiniz. Aşağıdaki görselde bütün ihtiyacınız olan alanlar işaretlenmiştir. Bu görseli klavuz alarak, ‘connectionstring’ içerisine yazacağınız verilere kolayca erişebilirsiniz.

Excel Marko İle Veritabanı İşlemleri - SqlExpress

2.2.3 - SQL ConnectionString'in Bir Constant Olarak Tanımlanması

    2.1.3 numaralı maddede detaylı olarak bu işlemi açıklamıştık. Bu yüzden burada sadece constant’ın tanımlanmış haline paylaşıyoruz.

Excel Makro İle Veritabanı İşlemleri - ConnectionStrings As Constants

    Artık bağlantı için en önemli unsurları geride bıraktık. Şimdi sıra bu bağlatıları kullanarak neler yapabileceğimizde.

3 - RecordSet Nedir?

     RecordSet, bir veritabanından veya bir birine bağlı veritabanı tablolarından çekilen düzenli veridir. Bir access veya sql veritabanında yer alan ve bizim kopyaladığımız veriler artık bizim için ‘RecordSet’tir.   Peki bu belirlediğimiz RecordSet’i yani veritabanından çektiğim kayıtları nasıl excel tablolarımız içersine aktarırız. 

3.1 - Access Veritabanından Excel'e Veri Aktarımı

3.1.1. - Hazırlanan Access Veritabanı Tablosu

    Bu örnek için ilk önce access veritabanında ‘Marka ve Modeline Göre Otomobil Fiyatları’ tablomuzu oluşturduk. 

Excel Makro İle Veritabanı İşlemleri - Access Örnek Tablosu

    Şimdi ise sıra bu tablonun excel’e aktarılmasını sağlayacak kodu incelemekte. 

3.1.2. - Access Veritabanından Veri Aktarılmasını Sağlayan Kodun İncelenmesi

    Aşağıda paylaşılan kod içerisinde ters parantez içerisinde kalan kısım RecordSet’in excel worksheet’e aktarılmasını sağlayan kısımdır.  Ayrıca Access ve SQL’e ait connection string’lerin constant olarak module’un tepesinde tanımlı olduğunu da unutmayalım. 

    Şimdi madde madde kodu inceleyelim.   

 

Excel Makro İle Veritabanı İşlemleri-Access Veritabanı Recordset Makrosu

 

        #1 – ACC_Veri.ActiveConnection = Baglanti_Envanter

        #2 – ACC_Veri.Source = “OtomobilSatisRakamlari”

        #3 – ACC_Veri.LockType = adLockReadOnly

        #4 – ACC_Veri.CursorType = adOpenForwardOnly

        #5 – ACC_Veri.Open

        #6 – Worksheets.Add

        #7 – ACC_Veri.Close

        #8 – Baglanti_Envanter.Close

 

Not_1:

    NotBiçim

    Dünya Çapında Araştırmacılara Ait Çalışmaları Okumak İsterseniz

 https://www.researchgate.net/profile/Ender_Dagdelen 

adresini Ziyaret Edebilirsiniz. Ana Sayfaya Dönmek İsterseniz De Aşağıdaki Tuşu Tıklayınız.

18.05.2020 

İtibariyle Sayfaya İçerik Eklenmektedir.

macro[email protected]

Herkese Kolay Gelsin