本文主要涉及：VBA中的SQL Server環(huán)境配置 VBA連接SQL Server數(shù)據(jù)庫 VBA讀寫SQL Server數(shù)據(jù) 如何安裝SQL Client
系統(tǒng)環(huán)境： Windows 7 64bit Excel 2016 64bit
1. VBA連接SQL Server前的環(huán)境配置在Excel這邊，需要先在VBE中啟動數(shù)據(jù)庫連接支持。按下Alt+F11打開VBE，在菜單欄選擇“工具”－“引用”，在彈出的引用窗口中，找到"Microsoft ActiveX Data Objects 6.1 Library"和"Microsoft ActiveX Data Objects Recordset 2.8 Library"，把前面的框勾選上，點擊確定即可。 （如果不是這兩個版本，則選擇一個版本號最高的勾選即可，如果是需要分享給office2003版的用戶，建議勾選版本最低的）  
2. VBA連接SQL Server在按照上述步驟配置了環(huán)境支持后，就可以在VBA中使用代碼連接SQL Server了。 首先需定義連接對象： Dim conn as ADODB.Connection
Set conn = new ADODB.Connection 這里也可以簡寫為： Dim con As New ADODB.Connection 連接數(shù)據(jù)庫 conn.ConnectionString = "Provider=SQLOLEDB;Server=192.168.1.1;Database=XXXXX;Uid=sa;Pwd=123456"
conn.Open 連接字符串ConnectionString中的各個參數(shù)應(yīng)該很明了，就不一一解釋了。 上一段代碼也可以簡寫為 con.Open "Provider=SQLOLEDB;Server=192.168.1.1;Database=XXXXX;Uid=sa;Pwd=123456" 至此，數(shù)據(jù)庫連接成功！ 可以使用連接對象的State屬性和Version屬性查看數(shù)據(jù)庫狀態(tài)和版本（檢查是否連接成功） MsgBox("連接成功！" & vbCrLf & "數(shù)據(jù)庫狀態(tài)：" & con.State & vbCrLf & "數(shù)據(jù)庫版本：" & con.Version)最后關(guān)閉數(shù)據(jù)庫連接 con.Close
Set con = Nothing 整個過程的完整代碼如下： Sub 連接SQL Server數(shù)據(jù)庫()'1. 引用ADO工具'2. 創(chuàng)建連接對象Dim con As New ADODB.Connection'3. 建立數(shù)據(jù)庫的連接con.ConnectionString = "Provider=SQLOLEDB;Server=192.168.1.1;Database=XXXXX;Uid=sa;Pwd=123456"con.Open MsgBox ("連接成功！" & vbCrLf & "數(shù)據(jù)庫狀態(tài)：" & con.State & vbCrLf & "數(shù)據(jù)庫版本：" & con.Version) con.Close Set con = Nothing End Sub3. VBA讀寫SQL Server數(shù)據(jù)表3.1 讀取SQL Server數(shù)據(jù)到Excel代碼如下： Sub linkSQL Server() Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Set conn = New ADODB.Connection Set rs = New ADODB.Recordset'配置連接串
conn.ConnectionString = "Provider=SQLOLEDB;Server=192.168.1.1;Database=XXXXX;Uid=sa;Pwd=123456"
conn.Open'從test數(shù)據(jù)庫的YGXM表中取出所有數(shù)據(jù)
rs.Open "select * from `YGXM`", conn'設(shè)置表頭
Range("A1:B1").Value = Array("ID", "Name")'將數(shù)據(jù)輸出到工作表
Range("A2").CopyFromRecordset rs'關(guān)閉連接
rs.Close: Set rs = Nothing
conn.Close: Set conn = NothingEnd Sub相比前面的代碼，以上代碼多了 ADODB.Recordset 和 rs.Open，ADODB.Recordset 用于執(zhí)行SQL語句并接收查詢語句返回的結(jié)果集。
這里需要提一下的是，在VBA中執(zhí)行SQL語句有兩種方式，其一是使用連接對象執(zhí)行： conn.Execute ，其第一個參數(shù)就是SQL語句；另一種則使用結(jié)果集對象執(zhí)行： rs.Open ，這種方式有兩個必要參數(shù)，分別是SQL語句和連接對象，如上例中的 rs.Open "select * from `test`", conn 。
接下來的兩行Range是用于把查詢結(jié)果復(fù)制到Excel表格中的。 3.2 寫入數(shù)據(jù)到SQL Server其實寫入數(shù)據(jù)，只需要把上例中的SQL語句改成 UPDATE 或者 INSERT 即可，就不多說了。 番外篇—— 安裝SQL Server client 服務(wù)
如果你正好需要使用其他語言通過ODBC連接SQL Server，可能需要先安裝SQL Server client服務(wù)。 可以選擇使用官方安裝包，或者使用Navicat連接一次SQL Server（第一次連接時如果沒安裝會提示你安裝） 一路下一步，在這一步選擇“此功能及所有子功能將安裝到本地硬盤上 
然后繼續(xù)一路下一步即可。 ODBC的設(shè)置和MySQL或Oracle類似，在此不再贅述，如需要可以留言或者發(fā)郵件討論。 PS：數(shù)據(jù)庫連接工具推薦使用Navicat，可以同時連接不同的數(shù)據(jù)庫，非常方便。
|
