Excel Forum

Excel Forum (http://www.excel.web.tr/index.php)
-   SQL (http://www.excel.web.tr/forumdisplay.php?f=66)
-   -   excel 2007 userformda sql server baglantı kodları (http://www.excel.web.tr/showthread.php?t=111045)

dincay67 01-02-2012 17:14

excel 2007 userformda sql server baglantı kodları
 
userformdaki listboxa sql serverdan nasıl bilgi aktarabilirim

zafer 01-02-2012 17:41

Merhaba

Kodları inceleyiniz.
Gerekli yerleri doldurup çalıştırınız

Kod:


servername=""
databasename=""
user=""
password=""
 
Set cnt = New ADODB.Connection
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=" & servername & ";INITIAL CATALOG=" & databasename & ";"
strConn = strConn & "UID=" & user & ";PWD=" & password

 
cnt.Open strConn
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = cnt
.Open "SELECT * FROM Tabloismi
 
Do While Not rst.EOF
For i=0 To rst.Fields.Count
 
    ListBox1.AddItem
          ListBox1.COLUMN(i, x) = rst(i)
 
    Next 
    rst.MoveNext
  x = x + 1
Loop
.Close
End With
 
cnt.Close
Set rst = Nothing
Set cnt = Nothing


sarigozoglu 18-08-2017 12:36

Merhaba,
Bu kodları userformun "INITALIZE" Bölümüne mi kopyalıyoruz ?

zafer 21-08-2017 08:34

Merhaba

Evet, userformun "INITALIZE" Bölümüne kopyalıyoruz.Açılışta listbox'a bilgiler geliyor

sarigozoglu 21-08-2017 15:02

1 Eklenti(ler)
Kod:

Private Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

servername = "192.168.1.8"
databasename = "PERSONEL"
user = "1"
Password = "1"

strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=" & servername & ";INITIAL CATALOG=" & databasename & ";"
strConn = strConn & "UID=" & user & ";PWD=" & Password



    cnn.Open strConn
    Set rst = New ADODB.Recordset
With rst
.ActiveConnection = cnt
.Open "SELECT SICILNO, ADI, SOYADI FROM PERSONEL1"
 
Do While Not rst.EOF
For i = 0 To rst.Fields.Count
 
    ListBox1.AddItem
          ListBox1.Column(i, x) = rst(i)
 
    Next
    rst.MoveNext
  x = x + 1
 Loop
.Close
End With
UserForm_Initialize_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
UserForm_Initialize_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume UserForm_Initialize_Exit
End Sub

Hata veriyor, ekte gönderdim


Saat 16:55

Powered by vBulletin Version 3.7.2
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.