Custom Function That Gets MS Access Data

[stephenj789]

Hello folks. I would like to create a function in Excel as such:

Function(KeyItem, InfoType)

Where the KeyItem is a key item from from an Access database table, and the InfoType one of a number of fields from that table.

Basically, I would like to create a connection to the DB instead of opening up the DB and copying the data into Excel and doing vlookups (on the KeyItem). I have been unable to find out how to do this via internet search or looking at the Excel help files. If anybody could point me to a webpage or provide an example, it would be appreciated.

The following is an example of what I would like it to do:

=SALESPERSON(SalesPersonID, 1) would bring back the salesperson's region

=SALESPERSON(SalesPersonID, 2) would bring back the salesperson's first name

=SALESPERSON(SalesPersonID, 3) would bring back the salesperson's last name

etc.

Thank you.

 

[SkipVought]

Hi,

I make lots of Db functions. I don't have an Access one but this demonstrates the principle. Just use an Access Connect String...

Function GetDaysLate(sTRAV As String) As Integer
'Skip Metzger/2005 Oct 12/817-280-5438
'--------------------------------------------------
' Access: A010PROD.FPRPTSAR.READ
'--------------------------------------------------
'gets Days Late for a given Traveler
'--------------------------------------------------
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    sServer = "A010PROD"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT Sysdate-LPST "
    sSQL = sSQL & "FROM FPRPTSAR.MFG_ORDER_INFO "
    sSQL = sSQL & "WHERE MFG_ORD = '" & sTRAV & "'"
        
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    On Error Resume Next
                          
    rst.MoveFirst

    If Err.Number = 0 Then
        If rst(0) < 0 Then
            GetDaysLate = 0
        Else
            GetDaysLate = rst(0)
        End If
    Else
        GetDaysLate = 0
    End If
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Just set a reference for the MS ActiveX Data Objects m.n Object Library.

Skip,

_[red][/red]