' Excel的Tools->References中要选中Microsoft
ActiveX Data Objects 2.0 Library
1、执行SQL语句
Sub执行SQL语句()
Dim strSQL As String
strSQL = "insert into TableName(recNum, personName)
values(1,'LEE')"
Call ExecuteSQL(strSQL)
MsgBox "Completed."
End Sub
Sub ExecuteSQL(strSQL)
Dim sqlserver, database, username, password As String
Dim cn As New ADODB.Connection
'定义数据链接对象，保存连接数据库信息
Dim strCn As String
Set workSht = ThisWorkbook.Worksheets("测试数据")
sqlserver = workSht.Cells(2, 1).Value
database = workSht.Cells(2, 2).Value
username = workSht.Cells(2, 3).Value
password = workSht.Cells(2, 4).Value
'定义数据库链接字符串
strCn = "Provider=sqloledb;Server=" & sqlserver & _
";Database=" & database & _
";Uid=" &
username & _
";Pwd=" & password & ";"
'与数据库建立连接
cn.Open strCn
'执行该SQL命令串
cn.Execute strSQL
'关闭数据库链接,释放资源
cn.Close
End Sub
2、获取SQL数据库记录
Sub返回表中Remark()
Dim orderSht As Worksheet
Dim beginrow, endrow, runFlag, RecNum, pbuType, pbuCode, confirmTable,
retRemark As String
Dim icounter As Integer
Set rderSht = ThisWorkbook.Worksheets("测试数据")
beginrow = orderSht.Cells(1, 2).Value '获取开始行，假设为6
endrow = orderSht.Cells(2, 2).Value '获取结束行，假设为10
For icounter = CLng(beginrow) To CLng(endrow)
runFlag = orderSht.Cells(icounter, 1).Value '检查6~10行A列的标志是否为Y，是就执行，否则不执行
If LCase(runFlag) = "y" Then
RecNum = orderSht.Cells(icounter, 2).Value
TableName = orderSht.Cells(icounter, 3).Value
retRemark = ReturnRemark(RecNum, TableName) '将TableName中RecNum对应记录的Remark字段值返回
orderSht.Cells(icounter, 4).Value = retRemark
End If
Next
MsgBox "Completed."
End Sub
Function ReturnRemark(RecNum, confirmTable)
Dim sqlserver, database, username, password As String
sqlserver = "198.168.1.1"
database = "testDB"
username = "sa"
password = "sa"
Dim strCn, strSQL As String
Dim cn As New ADODB.Connection
'定义数据链接对象，保存连接数据库信息
Dim rs As New ADODB.Recordset
'定义记录集对象，保存数据表
Dim remark As Variant
'定义数据库链接字符串
strCn = "Provider=sqloledb;Server=" & sqlserver & _
";Database=" & database & _
";Uid=" & username & _
";Pwd=" & password & ";"
strSQL = "select rec_num,remark from " & confirmTable
& " where rec_num = " & RecNum
cn.Open strCn
'与数据库建立连接
rs.Open strSQL, cn
'执行该SQL命令串
remark = rs("remark") '返回确认表remark
rs.Close
'关闭记录集
cn.Close
'关闭数据库链接,释放资源
ReturnRemark = remark
End Function
