使用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