Sub ReturnSQLrecord()
    'sht 為excel工作表對(duì)象變量,指向某一工作表
    Dim i As Integer, sht As Worksheet
    
    '定義數(shù)據(jù)鏈接對(duì)象 ，保存連接數(shù)據(jù)庫(kù)信息
    '使用ADODB，須在菜單的Tools->References中添加引用“Microsoft ActiveX Data Objects library 2.x”
    Dim cn As New ADODB.Connection
    
    '定義記錄集對(duì)象，保存數(shù)據(jù)表
    Dim rs As New ADODB.Recordset
    Dim strCn As String, strSQL As String
    
    '定義數(shù)據(jù)庫(kù)鏈接字符串,Server=服務(wù)器名稱或IP地址(本地可填寫(xiě)“.”);Database=數(shù)據(jù)庫(kù)名稱;Uid=用戶登錄名;Pwd=密碼
    strCn = 'Provider=sqloledb;Server=NIKEY-980114BB0;Database=pubs;Uid=sa;Pwd=sa;'
    
    '定義SQL查詢命令字符串
    strSQL = 'select job_id, job_desc from dbo.jobs'
    
    '與數(shù)據(jù)庫(kù)建立連接，如果成功，返回連接對(duì)象cn
    cn.Open strCn
    
    '執(zhí)行strSQL所含的SQL命令，結(jié)果保存在rs記錄集對(duì)象中
    rs.Open strSQL, cn
    
    i = 1
    '把sht指向當(dāng)前工作簿的sheet1工作表
    Set sht = ThisWorkbook.Worksheets('sheet1')
    
    '當(dāng)數(shù)據(jù)指針未移到記錄集末尾時(shí)，循環(huán)下列操作
    Do While Not rs.EOF
        
        '把當(dāng)前記錄的job_id字段的值保存到sheet1工作表的第i行第1列
        sht.Cells(i, 1) = rs('job_id')
        sht.Cells(i, 2) = rs('job_desc')
        
        '把指針移向下一條記錄
        rs.MoveNext
        i = i + 1
    Loop
    
    '關(guān)閉記錄集
    rs.Close
    
    '關(guān)閉數(shù)據(jù)庫(kù)鏈接,釋放資源
    cn.Close
End Sub

Sub ReturnSQLrecord()
    Dim i As Integer, sht As Worksheet
    
    '定義數(shù)據(jù)鏈接對(duì)象 ，保存連接數(shù)據(jù)庫(kù)信息
    '使用ADODB，須在菜單的Tools->References中添加引用“Microsoft ActiveX Data Objects library 2.x”
    Dim cn As New ADODB.Connection
    
    Dim strCn As String, strSQL As String
    
    '定義數(shù)據(jù)庫(kù)鏈接字符串,Server=服務(wù)器名稱或IP地址(本地可填寫(xiě)“.”);Database=數(shù)據(jù)庫(kù)名稱;Uid=用戶登錄名;Pwd=密碼
    strCn = 'Provider=sqloledb;Server=.;Database=pubs;Uid=sa;Pwd=sa;'
    
    '清空定義的變量
    strSQL = ''
    
    '與數(shù)據(jù)庫(kù)建立連接，如果成功，返回連接對(duì)象cn
    cn.Open strCn
    
    Set sht = ThisWorkbook.Worksheets('sheet1')
    For i = 2 To 6
        '構(gòu)造SQL命令串,對(duì)標(biāo)識(shí)列job_id執(zhí)行插入操作時(shí)，要設(shè)置表的IDENTITY_INSERT為打開(kāi)，否則會(huì)插入失敗
        strSQL = strSQL & 'SET IDENTITY_INSERT dbo.jobs ON;insert into dbo.jobs(job_id,job_desc,min_lvl,max_lvl) values(' _
        & sht.Cells(i, 1) & ',' & CStr(sht.Cells(i, 2)) & ',' & sht.Cells(i, 3) & ',' & sht.Cells(i, 4) & ') ;'
    Next

    '執(zhí)行該SQL命令串，如果SQL命令沒(méi)有錯(cuò)誤，將在數(shù)據(jù)庫(kù)中添加5個(gè)記錄;也可以用rs.open strSQL,cn 執(zhí)行
    cn.Execute strSQL
    
    '關(guān)閉數(shù)據(jù)庫(kù)鏈接,釋放資源
    cn.Close
End Sub