Big dataset - batch process

[Sunstormrider]

Hi

I've got a huge (14,000,000+) dataset I need to process. At present,
this is sitting in an Access db in one table. I have
code ready to go to split it into around 39,000 separate tables and
export that as (if necessary) 39,000 separate Excel files.

What I need to do is perform a regression (data analysis toolpak) on
each separate file. To the best of my knowledge this is best
achieved in Excel not Access but if it's easier to have Excel connect
to the Access db to retrieve the data I have no problem with this.

The code to run the regression is simple enough -

Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$E$2:$E
$1930") _
, ActiveSheet.Range("$D$2:$D$1930"), False, False, 95, "",
False, False _
, False, True, , True

but how do I find out which row contains the last row of data (it will
vary from file to file) to plug into that code?

But my main question is - is this the best approach? So far as I
know, you can't run regressions in Access so this is, as far as I can
tell, the best method isn't it?

Thx

 

[Don Guillett]

Hi

I've got a huge (14,000,000+) dataset I need to process. At present,
this is sitting in an Access db in one table. I have
code ready to go to split it into around 39,000 separate tables and
export that as (if necessary) 39,000 separate Excel files.

What I need to do is perform a regression (data analysis toolpak) on
each separate file. To the best of my knowledge this is best
achieved in Excel not Access but if it's easier to have Excel connect
to the Access db to retrieve the data I have no problem with this.

The code to run the regression is simple enough -

Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$E$2:$E
$1930") _
, ActiveSheet.Range("$D$2:$D$1930"), False, False, 95, "",
False, False _
, False, True, , True

but how do I find out which row contains the last row of data (it will
vary from file to file) to plug into that code?

But my main question is - is this the best approach? So far as I
know, you can't run regressions in Access so this is, as far as I can
tell, the best method isn't it?

Thx

I have no experience with Access or regression but maybe this helps
===============
with activesheet
lr = .Cells.find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
.Range("$E$2:$E$" & lr") _
.Range("$D$2:$D$" & lr), False, False, 95, "", _
False, False, False, True, , True
end with