Kết nối Excel với SQL Server

Dim conSQL As New ADODB.Connection

    Dim record As New ADODB.Recordset

    

    ' bien de luu tru MAT KHAU ket SQL

    cnString = "PROVIDER=SQLOLEDB;Server=hongnhat2\SQLEXPRESS;DATABASE=PhanMemBanHang;UID=sa;PWD=123"

    

    On Error GoTo tbLoi

    conSQL.ConnectionString = cnString

    conSQL.Open

    MsgBox conSQL.State ' 1 kết nối thành công, 0 kết nối SQL thất bị 

  

tbLoi:

    MsgBox Err.Description

   

    Dim rcs As New ADODB.Recordset

    Dim strCn As String

    strCn = "PROVIDER=SQLOLEDB;SERVER=HONGNHAT2\SQLEXPRESS;DATABASE=nhanvien;UID=sa;PWD=123"



    Dim cn As New ADODB.Connection

    cn.ConnectionString = strCn

    cn.CommandTimeout = 30

    cn.Mode = adModeReadWrite

    cn.Open

    

    Dim cmd As New ADODB.Command

    With cmd

        .ActiveConnection = cn

        .CommandType = adCmdText

        .CommandTimeout = 60

        .CommandText = "Select * From dbo.tbNhanVien"

     End With

     Dim prm As Object

     

    rcs.CursorType = adOpenKeyset

    rcs.LockType = adLockBatchOptimistic

    rcs.Open cmd

    

    

    Set rcs = cmd.Execute

    

    Dim pos_row As Integer

    pos_row = 1

    

    Do While (Not rcs Is Nothing)

        If rcs.State = adStateClosed Then Exit Do

        While Not rcs.EOF         

           Cells(pos_row, 1) = rcs!HoTen

            rcs.MoveNext

            pos_row = pos_row + 1

        Wend

        Set rcs = rcs.NextRecordset

    Loop

 

    Dim rcs As New ADODB.Recordset

    Dim strCn As String

    strCn = "PROVIDER=SQLOLEDB;SERVER=HONGNHAT2\SQLEXPRESS;DATABASE=nhanvien;UID=sa;PWD=123"



    Dim cn As New ADODB.Connection

    cn.ConnectionString = strCn

    cn.CommandTimeout = 30

    cn.Mode = adModeReadWrite

    cn.Open

    

    Dim cmd As New ADODB.Command

    With cmd

        .ActiveConnection = cn

        .CommandType = adCmdText

        .CommandTimeout = 60

        .CommandText = "Select * From dbo.tbNhanVien"

     End With

     Dim prm As Object

     

    rcs.CursorType = adOpenKeyset

    rcs.LockType = adLockBatchOptimistic

    rcs.Open cmd

    

    

    Set rcs = cmd.Execute

    

    Dim pos_row As Integer

    pos_row = 1 ' vi tri dong bat dau in

    pos_col = 1 ' vi tri cot bat dau in

    

    ' In tieu de

    For i = 0 To rcs.Fields.Count - 1

        Cells(pos_row, i + pos_col) = rcs(i).Name

    Next i

    

    pos_row = pos_row + 1

    

    Do While (Not rcs Is Nothing)

        If rcs.State = adStateClosed Then Exit Do

        While Not rcs.EOF

            For i = 0 To rcs.Fields.Count - 1

                 Cells(pos_row, i + pos_col) = rcs(i)

            Next i

            rcs.MoveNext

            pos_row = pos_row + 1

        Wend

        Set rcs = rcs.NextRecordset

    Loop