Excel-SQL Arasında Bağlantı Kurma

Bu bölümde hem veri sekmesi hem de makro kodları kullanarak Excel ile Sql Server arasında bağlantı kurmayı öğreneceğiz.

Veri Sekmesi ile SQL Server Bağlantısı Kurmak

Veri-> Verileri Al -> Veritabanından -> SQL Server Veritabanından’a tıklayın:

Varsa kullanıcı adı ve şifrenizi girin:

Üzerinde çalışmak istediğimiz tabloyu seçip Yükle’ye tıklayalım:

Sonuç:

Makro Kullanarak SQL Server Bağlantısı Kurmak

Excel ile Sql Server arasında bağlantı kurmak için ADO (ActiveX Data Objects) kullanılır.

VBE’de References Bölümünden ActiveX Data Objects’in son sürümünü seçip OK’e tıklayın:

Öncelikle Veri Tabanı ile bağlantı kurmak gerekir. Bağlantı kurulduktan sonra sonra sorgulamalar yapabiliriz. Her bir sorgulama Recordset objesine dönüşür. Geriye sadece Recordset içindeki veriyi worksheet’e yazmak kalır:

Aşağıdaki linkten, Microsoft SQL Server ODBC Driver sekmesi altında, Sql Server bağlantı komutu görülebilir:

connectionstrings.com/sql-server

Not: ODBC (Open DataBase Connectivity), Microsoft SQL Server gibi bir dış veri kaynağına bağlamak için kullanabileceğimiz bir protokoldür.

Aşağıdaki gibi komut girip bağlantımızı gerçekleştiriyoruz

Sub makro_sql() 

On Error GoTo HATA 

Dim baglanti As New ADODB.Connection 
Dim rs As New ADODB.Recordset 
Dim sunucu, veritabani, id, sifre, sorgu As String 

sunucu = "DESKTOP-ABC1234\SQLEXPRESS" 
veritabani = "ilkVeritabani" 
id = "SA" 
sifre = "1234" 
sorgu = "SELECT * FROM [personel]" 

'Aşağıdaki komut Windows authentication için geçerlidir. 
baglanti.Open "Driver={SQL SERVER};Server=" & sunucu & ";Database=" & veritabani & ";" 

'Aşağıdaki komut Sql Server authentication için geçerlidir. 
'baglanti.Open "Driver={SQL SERVER};Server=" & sunucu & ";Database=" & veritabani & ";Uid=" & id & ";Pwd=" & sifre & ";" 

'Parametreleri ayrı ayrı ayarlıyoruz 
rs.ActiveConnection = baglanti 
rs.Source = sorgu 
rs.LockType = adLockReadOnly 
rs.CursorType = adOpenStatic 
rs.Open 

'önce sayfa içeriğini siliyoruz 
Cells.ClearContents 

'Sütun başlıklarını ekliyoruz 
Dim i As Long 
For i = 0 To rs.Fields.Count - 1
     Range("A1").Offset(0, i).Value = rs.Fields(i).Name 
Next i 

'Sorguyu sayfaya yazdırıyoruz 
Range("A2").CopyFromRecordset rs 

'Bağlantıları kapatıyoruz 
rs.Close 
Set rs = Nothing 
baglanti.Close 
Set baglanti = Nothing 
Exit Sub 

HATA:
     MsgBox Err.Description

     If Not (rs Is Nothing) Then
         If (rs.State And adStateOpen) = adStateOpen Then rs.Close
         Set rs = Nothing
     End If
     If Not (baglanti Is Nothing) Then
         If (baglanti.State And adStateOpen) = adStateOpen Then baglanti.Close
         Set baglanti = Nothing
     End If 

End Sub Code language: VB.NET (vbnet)

Sonuç:

Not: Data miktarının fazla olmasına karşı aşağıdaki komutun sonuna 5 yazarak data sayısını 5 ile sınırladık:

Range("A2").CopyFromRecordset rs, 5 Code language: VB.NET (vbnet)

Sonuç:

BAĞLANTI PARAMETRELERİ

CursorType

CursorType, arama yönünü ve verinin görüntüleme tipini ifade eder:

• adOpenForwardOnly: Default şeçenek budur. Sadece ileri yönlü hareket eder. Aksi gerekmedikçe bu seçenek kullanılarak daha hızlı erişim sağlanır.
• adOpenStatic: Bu seçenek tüm yönlere izin verir ve başkaları tarafından yapılan değişiklikler o an size görünmez. Yani siz veriye eriştiğiniz anda ilgili veri setinin resmi çekilir ve siz hep onu görürsünüz.
• adOpenDynamic: Bu seçenek tüm yönlere izin verir ama bu sefer başkaları tarafından yapılan değişiklikler size anında görünür.
• adOpenKeyset: adOpenDynamic’e benzer, ama silinen veya eklenenler size o an görünmez, sadece değişiklikleri görebilirsiniz.

LockType

LockType, kayıtlar güncellenirken ne tür kilit konacağını ifade eder. Çok kullanıcının eriştiği bir dosyada aynı anda birden çok kullanıcı dataya erişmeye veya değiştirmeye çalışırsa nasıl davranılması gerektiğini belirler.

• adLockReadOnly :Default seçenek budur. Kayıtlar herkeste salt okunur açılır ve kimse güncelleyemez.
• adLockOptimistic: Update sırasında(Update metodu çağrıldığında) kilitler. Başkaları da o sırada görebilir ve güncelleyebilir.
• adLockPessimistic: Güncellemeye başladığınız anda kilitler. Başkaları o sırada bu kaydı okuyamaz ve güncelleyemez.
• adLockBatchOptimistic: adLockOptimistic’in aynısıdır, sadece toplu güncelleme yapıldığında kullanılır.