vba基本操作 — MS SQL Server操作

• 讀取MS SQL Server資料表資料，並將它儲存到excel工作表中

Sub ReturnSQLrecord()    'sht 為excel工作表物件變數,指向某一工作表    Dim i As Integer, sht As Worksheet        '定義資料連結對象 ，儲存串連資料庫資訊    '使用ADODB，須在菜單的Tools->References中添加引用“Microsoft ActiveX Data Objects library 2.x”    Dim cn As New ADODB.Connection        '定義記錄集對象，儲存資料表    Dim rs As New ADODB.Recordset    Dim strCn As String, strSQL As String        '定義資料庫連結字串,Server=伺服器名稱或IP地址(本地可填寫“.”);Database=資料庫名稱;Uid=使用者登入名稱;Pwd=密碼    strCn = "Provider=sqloledb;Server=NIKEY-980114BB0;Database=pubs;Uid=sa;Pwd=sa;"        '定義SQL查詢命令字串    strSQL = "select job_id, job_desc from dbo.jobs"        '與資料庫建立串連，如果成功，返回連線物件cn    cn.Open strCn        '執行strSQL所含的SQL命令，結果儲存在rs記錄集對象中    rs.Open strSQL, cn        i = 1    '把sht指向當前活頁簿的sheet1工作表    Set sht = ThisWorkbook.Worksheets("sheet1")        '當資料指標未移到記錄集末尾時，迴圈下列操作    Do While Not rs.EOF                '把目前記錄的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        '關閉記錄集    rs.Close        '關閉資料庫連結,釋放資源    cn.CloseEnd Sub

 

•  讀取excel工作表資料，並將之插入到資料庫中（將sheet1工作表中的A2:D6的記錄插入到資料庫pubs的jobs資料表中） 

Sub ReturnSQLrecord()    Dim i As Integer, sht As Worksheet        '定義資料連結對象 ，儲存串連資料庫資訊    '使用ADODB，須在菜單的Tools->References中添加引用“Microsoft ActiveX Data Objects library 2.x”    Dim cn As New ADODB.Connection        Dim strCn As String, strSQL As String        '定義資料庫連結字串,Server=伺服器名稱或IP地址(本地可填寫“.”);Database=資料庫名稱;Uid=使用者登入名稱;Pwd=密碼    strCn = "Provider=sqloledb;Server=.;Database=pubs;Uid=sa;Pwd=sa;"        '清空定義的變數    strSQL = ""        '與資料庫建立串連，如果成功，返回連線物件cn    cn.Open strCn        Set sht = ThisWorkbook.Worksheets("sheet1")    For i = 2 To 6        '構造SQL命令串,對識別欄位job_id執行插入操作時，要設定表的IDENTITY_INSERT為開啟，否則會插入失敗        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    '執行該SQL命令串，如果SQL命令沒有錯誤，將在資料庫中添加5個記錄;也可以用rs.open strSQL,cn 執行    cn.Execute strSQL        '關閉資料庫連結,釋放資源    cn.CloseEnd Sub

 

	A	B	C	D
1	job_id	job_desc	min_lvl	max_lvl
2	30	'test30'	20	100
3	31	'test31'	20	100
4	32	'test32'	20	100
5	33	'test33'	20	100
6	34	'test34'	20	100