I. 基本ADO連接
(關於User name/Password 或 Integrated 請看2.連接方法)
1. 到"工具"->"設定引用項目"
找出併打勾"Microsoft ActiveX Data Objects 2.8 Library" (2.7 也可)
Windows XP 已經預帶2.7版本(MDAC 2.7), 如果還是找不到, 可以到這個位置下載
2. 連接方法(connection)
引用好ADO後, 就可以在VBA使用ADO部件
以下是用User Name和password 連接數據庫的方法
Dim Conn As ADODB.Connection Dim sConnect As String Dim strSqlInstance As String Dim strSqlDB As String Dim strSqlUser As String Dim strSqlPWD As String strSqlInstance = "SERVER_NAME\INSTANCE" 'SQL SERVER實例 strSqlDB = "DATABASE NAME" '數據庫名稱 strSqlUser = "SA" 'SQL實例用戶名 strSqlPWD = "PASSWORD" 'SQL實例用戶密碼 sConnect = "PROVIDER=SQLOLEDB;" sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";" sConnect = sConnect & " User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";" Set Conn = New ADODB.Connection Conn.ConnectionString = sConnect Conn.Open … Conn.Close
如果客戶機有設Active directory等, 可以用
sConnect = sConnect & " INTEGRATED SECURITY=sspi;"來取代
sConnect = sConnect & " User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"
II. 拿出資料表(table)數據 (SQL SELECT)
把數據表 Information_Schema.tables 的數據
用sql “Select * From ..." 抽出 放入 工作頁 "DATA”
Sub subGetTableValues()
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim intColCounter As Integer
Dim sConnect As String
Dim strSqlInstance As String
Dim strSqlDB As String
Dim strSql As String
strSqlInstance = "SERVER_INSTANCE"
strSqlDB = "database"
sConnect = "PROVIDER=SQLOLEDB;"
sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
sConnect = sConnect & " INTEGRATED SECURITY=sspi;"
'Establish connection
Set Conn = New ADODB.Connection
strSql = "SELECT * FROM Information_Schema.tables" 'SQL在這裡定義,可加上 where group by...
With Conn
.ConnectionString = sConnect
.CursorLocation = adUseClient
.Open
.CommandTimeout = 0
Set rs = .Execute(strSql)
End With
Worksheets("Data").Cells.Clear
If rs.RecordCount > 0 Then '如果有數據
'寫出欄名
For intColCounter = 0 To rs.Fields.Count - 1
Worksheets("Data").Range("A1").Offset(0, intColCounter) = rs.Fields(intColCounter).Name
Next
'寫出內容
Worksheets("Data").Range("A2").CopyFromRecordset rs
Else
MsgBox ("找不到數據")
End If
rs.Close
Conn.Close
Set rs = Nothing
Set Conn = Nothing
End Sub
另一個方法可代替CopyFromRecordset,一格一格寫上.
Application.ScreenUpdating = False '先關掉顯示,寫好再補上,可以省"點"時間
Dim intRow as long, intCol as long
intRow = 0
rs.MoveFirst
Do While rs.EOF = False
For intCol = 0 To rs.Fields.Count - 1
Range("A2").Offset(intRow, intCol).Value = rs.Fields(intCol).Value
Next intCol
rs.MoveNext
intRow = intRow + 1
Loop
Application.ScreenUpdating = True
III. 拿出儲存程序(stored procedure, sp)結果集
假設要呼叫的SP. SP有3組參數:
1. 字串輸入: astring_input
2. 數字輸入: aint_input
3. 可變數字輸入: aint_output
最後SP會把 3組參數用1個結果集回傳出來, 和把aint_output(3)設定為999
例如, exec usp_test_vba 'abc',10,100 會回傳以下的表(第13-16行)
| string input | integer input | integer output |
| abc | 10 | 999 |
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE usp_test_vba @astring_input varchar(50), @aint_input integer, @aint_output integer output AS set @aint_output = 999 select @astring_input as [string input], @aint_input as [integer input], @aint_output as [integer output] RETURN 777 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
呼叫方法:
Dim Conn As ADODB.Connection
Dim ADODBCmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnect As String
Dim intArgOutput As Integer
Dim intReturn As Integer
intArgOutput = 30
spName = "usp_test_vba"
Dim strSqlInstance As String
Dim strSqlDB As String
strSqlInstance = "SQL SERVER INSTANCE"
strSqlDB = "DATABASE NAME"
sConnect = "PROVIDER=SQLOLEDB;"
sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
sConnect = sConnect & " INTEGRATED SECURITY=sspi;"
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open
Set ADODBCmd = New ADODB.Command
Conn.CommandTimeout = 0
With ADODBCmd
.ActiveConnection = Conn
.CommandText = spName
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters(1).Value = "abc" '放入第1個參數
.Parameters(2).Value = 5 '放入第2個參數
.Parameters(3).Value = intArgOutput '放入第3個參數
Set rs = .Execute()
intArgOutput = .Parameters(3).Value '回傳第3個參數,經SP處理後數值, 即999
intReturn = .Parameters(0).Value '回傳SP RETURN出來的數值 即777
End With
Worksheets("Data").Cells.Clear
If Not (rs.EOF) Then
For intColCounter = 0 To rs.Fields.Count - 1
Worksheets("Data").Range("A1").Offset(0, intColCounter) = rs.Fields(intColCounter).Name
Next
Worksheets("Data").Range("A2").CopyFromRecordset rs
Else
MsgBox ("沒有回傳表")
End If
rs.Close
Set rs = Nothing
Conn.Close
Set Conn = Nothing
IV. 拿出儲存程序(SP)結果集, SP上有臨時表 (#TEMP TABLE)的問題假設要呼叫的SP,中間有使用臨時表.
用上III方法,運行時可能會出現這樣的錯誤:
"當物件關閉時, 不允許操作"
但是直接在QA/SSMS運行是沒有問題的.
這個問題的解決方法 ,可以在SP上要加上這一行
SET NOCOUNT ON
詳情可以參考這裡. (實際上我也沒有看詳情, 為了找出這個BUG,已經花了半天來Google...)
下一篇會說一下UPDATE/DELETE/INSERT等的
本篇最新更新 2012-07-02
