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.

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.

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.

Ernst,

Just spotted this and here's a belated idea. If you can structure your
function calls so that they will work within array functions, then you can
minimise the number of hits to the db. Eg. if you are getting annual data
over a five year period, you would have five cells in the array, the db
would return five data points, and you return an array from the function
that will fill all five cells.

Robin Hammond
www.enhanceddatasystems.com

"K Dales" wrote in message
...
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.