翻譯：tmtony(王宇虹) m.mzhfr.cn
一般使用 數(shù)據(jù)-》獲取外部數(shù)據(jù) 或 vdookups來獲取外部數(shù)據(jù)，但當數(shù)據(jù)量比較大，可直接使用ADO來獲取外部數(shù)據(jù)。
代碼如下：
Dim adoCn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Function GetFields(sKey As String, lField As Long) As Variant
Dim sCon As String, sSql As String
'如果第一次調(diào)用創(chuàng)建記錄集
If adoCn Is Nothing Or adoRs Is Nothing Then
sCon = "DSN=MS Access Database;" & _
"DBQ=C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind.mdb;" & _
"DefaultDir=C:Program FilesMicrosoft Office 2000OfficeSamples;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
sSql = "SELECT ProductID, ProductName, QuantityPerUnit, Products.UnitPrice " & _
"FROM Products"
Set adoCn = New ADODB.Connection
adoCn.Open sCon
Set adoRs = New ADODB.Recordset
adoRs.CursorType = adOpenDynamic
adoRs.CursorLocation = adUseClient
adoRs.Open sSql, adoCn
End If
adoRs.MoveFirst
adoRs.Find "ProductID=" & sKey
If adoRs.EOF Or adoRs.BOF Then
GetFields = "Not found"
Else
GetFields = adoRs.Fields(lField).Value
End If
End Function
Dim adoRs As ADODB.Recordset
Function GetFields(sKey As String, lField As Long) As Variant
Dim sCon As String, sSql As String
'如果第一次調(diào)用創(chuàng)建記錄集
If adoCn Is Nothing Or adoRs Is Nothing Then
sCon = "DSN=MS Access Database;" & _
"DBQ=C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind.mdb;" & _
"DefaultDir=C:Program FilesMicrosoft Office 2000OfficeSamples;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
sSql = "SELECT ProductID, ProductName, QuantityPerUnit, Products.UnitPrice " & _
"FROM Products"
Set adoCn = New ADODB.Connection
adoCn.Open sCon
Set adoRs = New ADODB.Recordset
adoRs.CursorType = adOpenDynamic
adoRs.CursorLocation = adUseClient
adoRs.Open sSql, adoCn
End If
adoRs.MoveFirst
adoRs.Find "ProductID=" & sKey
If adoRs.EOF Or adoRs.BOF Then
GetFields = "Not found"
Else
GetFields = adoRs.Fields(lField).Value
End If
End Function
運行后的效果：
