翻译：tmtony(王宇虹) www.office-cn.net
一般使用 数据-》获取外部数据 或 vdookups来获取外部数据，但当数据量比较大，可直接使用ADO来获取外部数据。
代码如下：
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
'如果第一次调用创建记录集
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
'如果第一次调用创建记录集
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
运行后的效果：
