Populate Excel From Access Database - user defined function 1

[maximusimo]

http://www.tek-tips.com/viewthread.cfm?qid=1524341&page=1

 

[SkipVought]

Please post the entire question here rather than a link. It will greatly aid in solving your questions.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[maximusimo]

Hi,

I have an Access Database containing all customer details.

What I want is that when I put a customer account number in a cell in Excel that customer details are automatically retrieved from the database.

I have been able to populate one row using a parameter query (with SkipVoughts help) and works fine. But i want to be able to populate a whole worksheet with multiple parameter queries?

I need my end result to much like this:

code name contact
code name contact
code name contact
code name contact
code name contact
code name contact
code name contact
code name contact
code name contact
code name contact
code name contact

etc, etc.

The number of rows will change from one day to the next.

 

[SkipVought]

Here is an example of a UDF accessing an MS Access database...

Function GetHrsOfOper(sResource As String, dDateIn As Date)
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    Dim sPrevCC As String, BCC As Boolean
    
    sPath = "\\bhdfwfp426.bh.textron.com\M_Ctr$\1_Supply Chain\FP\Procedures\NewAdmin"
    sDB = "APS UNIVERSE"
    
    Set cnn = New ADODB.Connection

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & sPath & "\" & sDB & ".mdb;"
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT (1-TypeValue)*24 "

    sSQL = sSQL & "FROM Resource_calendar_data "

    sSQL = sSQL & "WHERE Resource Like '" & sResource & "%'"
    sSQL = sSQL & "  AND #" & Format(dDateIn, "mm/dd/yyyy") & "# >=FromDate"
    sSQL = sSQL & "  AND #" & Format(dDateIn, "mm/dd/yyyy") & "# <=ToDate"
        
    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
        On Error Resume Next
        .MoveFirst
        If Err.Number = 0 Then
            GetHrsOfOper = rst(0)
        Else
            GetHrsOfOper = 24
        End If
        
        .Close
    End With
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[RoyVidar]

One would need to take the date formatting one step further, for it to work places that use other date separators than US.

Either use ISO 8601, which is designed to work for all database platforms

Format(dDateIn, "yyyy-mm-dd")

or

Format(dDateIn, "mm\/dd\/yyyy")

More information at Allen Browne's

http://allenbrowne.com/ser-36.html

Roy-Vidar

 

[maximusimo]

Sorry but not too familiar with VBA, makes very little sense to me.

 

[SkipVought]

Roy,

Thanks for that info! My application is in-house, but I ought to make it as universal as possible. ==> *

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[RoyVidar]

Thanx,

there's one additional info - lot of places uses comma as decimalseparator, so when concatenating numbers with decimals into strings, you'd need to ensure comma becomes dot, by

[tt]...TheField=" & Str(TheVariable) & " And...[/tt]

say, where I live, the following in the immediate pane (ctrl+g)

[tt]s=3/2
?s -> 1,5
?str(s) -> 1.5[/tt]

;-)

Roy-Vidar