Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Custom Function That Gets MS Access Data

Status
Not open for further replies.

stephenj789

Technical User
Jan 28, 2002
58
US
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.
 




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...
Code:
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,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top