I have made use of some similar ADO based functions to be able to lookup
matching data in Excel spreadsheets. It is a pretty powerful technique but
has its drawbacks. Calculation speed is always an issue. In addition to the
time to re-establish the connection there will always be, unavoidably, the
time needed to execute your query. This will depend on how many records are
in the database and whether or not you are selecting them based on indexed
fields. You are using the Date field for your selection criteria; if this is
not an indexed fields and if you can change the database, make sure it is
indexed.

As for the connection issue: you could design code that holds the connection
open - but then you also need to have a way to close it when done; or (and
this depends on the size of the table) perhaps you could use a disconnected
recordset where you would hit the database just once (the first time the
function is called - you would need to have some sort of switch to determine
this), bring over all applicable records, make a cloned, disconnected copy,
and then do a (much faster) seach of the entire cloned recordset for the
particular values the next time you run the function. That would be
something like the below

Public ClonedRS as ADODB.Recordset
Public GotRecords as Boolean

Function DatabaseLookup(sDate As String, sCol As String, sTable As String)

If Not GotRecords Then
' Duplicate your code here up to the point where you define your SQL string:

sSQL = "SELECT [" & sCol & "], [Date] FROM tDayData"
' Open the recordset
rst.Open sSQL, sConn
' Clone the recordset
Set ClonedRS = New ADODB.Recordset
ClonedRS.CursorLocation = adUseClient
ClonedRs.CursorType = adOpenDynamic
ClonedRS = rst.Clone
' Close the connection
rst.Close

' Tell the function from now on we have the recordset:
GotRecords = True

End If
' Now build code using the Filter or Find methods of ClonedRS to return your
values



"Gys" wrote:

Hi,
this is because each time the function is executed the data base connection
must be opened agian. Maybe you can change your function to a Sub which gets
all the data from the database in one go.

There is a very helpfull forum here : microsoft.public.vb.database.ado
hth
Gys







"Ernst Guckel" wrote in message
...
Hello,

I have a function that I call from a spreadsheet that looks up a value
in
a database based on certain criteria. IT IS VERY SLOW. I calculate
about
500 or so values on a worksheet... Could someone help me with the SLOW
code?
Should I leave the recordset open? I'm at a loss as to what to do...
Here
is the function...

Function DatabaseLookup(sDate As String, sCol As String, sTable As String)

Dim sSQL As String
Dim sConn As String

Dim fld As Field
Dim rst As ADODB.Recordset

On Error GoTo ErrHandler

' Create a new recordset object
Set rst = New ADODB.Recordset

' Connection details
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile

' SQL statement to retrieve data
sSQL = "SELECT [" & sCol & "] FROM tDayData WHERE Date=#" & sDate &
"#;"

' Open the recordset
rst.Open sSQL, sConn

' Loop through each field and retrieve it's value
For Each fld In rst.Fields

If fld.Value < "" Then
DatabaseLookup = fld.Value
Else
DatabaseLookup = ""
End If

Next

Set rst = Nothing
Exit Function

ErrHandler:
MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly
End Function

Thanks,
Ernst.