Correlation Analysis - 'Not Enough Memory to Run Excel' & other strange behavior

[Ben]

I have written a procedure which calls the CORREL function of Excel to
run correlation analysis on two arrays, then populate a table with the
resulting correlation coefficient. This process loops through several
records and recordsets. The procedure works well for awhile
(sometimes upwards of 10,000 times!), but then mysteriously begins
populating "0" as the correlation coefficient (even though I know this
to be inaccurate). Sometimes, I receive the error 'Not Enough Memory
to Run Excel', sometimes not. Sometimes, the computer freaks out
after I break the code, diplaying various pieces of open windows,
flashing, etc.

HOWEVER, once I shut down/restart the computer and start the procedure
again from where I left off, it ALWAYS works properly again for
another 10,000 or so passes, then repeats the error(s). Is there some
way I can prevent this behavior??
Thank you.
Ben.

Using:
Win2K
Access 2000

and tried on:

WinXP
Access2000
(same results)

below is the code I'm using to create Excel object and run correl
analysis:
(slightly modifed code of Jeff Brady)

IS IT NECESSARY TO CREATE/QUIT EXCEL OBJECT EACH TIME I RUN ANALYSIS,
OR CAN I JUST CREATE IT ONCE, RUN ANALYSIS THOUSANDS OF TIME, THEN
QUIT??

Public Function PValue(sSQL As String) As Single
'Function receives SQL string as argument and returns paired TTest
value
'Note: No error checking is done in this function so be sure that the
SQL
' string passed has the data to be calculated in the first two
columns.
'Author: Jeff Brady 11/29/96

Dim xlApp As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim A1() As Double
Dim A2() As Double
Dim idx As Integer

Set xlApp = CreateObject("Excel.application")
Set db = CurrentDb()
Set rs = db.OpenRecordset(sSQL)

Do Until rs.EOF
ReDim Preserve A1(idx)
ReDim Preserve A2(idx)
A1(idx) = rs.Fields(0)
A2(idx) = rs.Fields(1)
idx = idx + 1
rs.MoveNext
Loop

PValue = xlApp.CORREL(A1, A2)
xlApp.Quit
Set xlApp = Nothing
rs.Close

End Function

[Lyle Fairfield]

br*******@yahoo.com (Ben) wrote in news:6b67292f.0310030819.90fb202
@posting.google.com:

I have written a procedure which calls the CORREL function of Excel to
run correlation analysis on two arrays, then populate a table with the
resulting correlation coefficient. This process loops through several
records and recordsets. The procedure works well for awhile
(sometimes upwards of 10,000 times!), but then mysteriously begins
populating "0" as the correlation coefficient (even though I know this
to be inaccurate). Sometimes, I receive the error 'Not Enough Memory
to Run Excel', sometimes not. Sometimes, the computer freaks out
after I break the code, diplaying various pieces of open windows,
flashing, etc.

HOWEVER, once I shut down/restart the computer and start the procedure
again from where I left off, it ALWAYS works properly again for
another 10,000 or so passes, then repeats the error(s). Is there some
way I can prevent this behavior??
Thank you.
Ben.

Using:
Win2K
Access 2000

and tried on:

WinXP
Access2000
(same results)

below is the code I'm using to create Excel object and run correl
analysis:
(slightly modifed code of Jeff Brady)

IS IT NECESSARY TO CREATE/QUIT EXCEL OBJECT EACH TIME I RUN ANALYSIS,
OR CAN I JUST CREATE IT ONCE, RUN ANALYSIS THOUSANDS OF TIME, THEN
QUIT??

Public Function PValue(sSQL As String) As Single
'Function receives SQL string as argument and returns paired TTest
value
'Note: No error checking is done in this function so be sure that the
SQL
' string passed has the data to be calculated in the first two
columns.
'Author: Jeff Brady 11/29/96

Dim xlApp As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim A1() As Double
Dim A2() As Double
Dim idx As Integer

Set xlApp = CreateObject("Excel.application")
Set db = CurrentDb()
Set rs = db.OpenRecordset(sSQL)

Do Until rs.EOF
ReDim Preserve A1(idx)
ReDim Preserve A2(idx)
A1(idx) = rs.Fields(0)
A2(idx) = rs.Fields(1)
idx = idx + 1
rs.MoveNext
Loop

PValue = xlApp.CORREL(A1, A2)
xlApp.Quit
Set xlApp = Nothing
rs.Close

End Function

The function seems to be stunningly inefficient. Redimming the arrays and
slam-dunking values when GetRows is available? Where do you release the
recordset object pointer, rs? Why the late binding?

Access is completely capable of calculating simple stats without going to
Excel, if one is familiar with the required formula. Probably a
"programmer" could speed this up by a factor of a few thousand. Pop it into
VB, do a native code compile and you might hit a million.

My advice is trash this and find a new function.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)