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.