CopyFromRecordset with large amount of data

[rweide]

Hi, all,

I am trying to load a spreadsheet (OWC) on my ASP page directly from the
RecordSet.

However, since the returned recordset can be huge, I will only load the
spreadsheet with first 1000 rows, if users choose to see the next 1000 rows,
I will load the next 1000 rows - pretty much treating the spreadsheet as a
list box but with Excel functions.

I do by something like this:

rs.CursorLocation = 3 'adUseClient
rs.CursorType = 1 'adOpenKeyset
rs.LockType = 3 'adLockOptimistic
rs.PageSize = 1000
rs.Open sSQL, Cnn
SS1.ActiveSheet.Cells.copyFromRecordset rs,1000
And this has been successful. But when I try to load the next 1000 rows, it
still displays the first 1000 rows. I do this by:

rs.AbsolutePage = 2
SS1.ActiveSheet.UsedRange.ClearContents
SS1.ActiveSheet.Cells.copyFromRecordset rs,1000

I check the recordset before the "copyFromRecordset" method, and the
recordset is at the correct position. But the "copyFromRecordset" seems to
ignore it, and load the spreadsheet from the BOF of the recordset.
Anyone knows about this? Thanks.

 

[keepITcool]

I've dispensed with the pagesize..
also..
Do you really need the Keyset? adopenForwardonly (0)
seems enough and is "lighter"


on a local test querying an excel file..this works for me..
(I realise OWC doesnt have the 65k row limit)

cn.Cursorlocation = 3
cn.Open

Set rs = CreateObject("adodb.recordset")
rs.Open "Select * from [Sheet1$]", cn, 0 'Static Forward cursor

nStep = 1000
nMax = rs.RecordCount
If nMax > 65535 Then nMax = 65535

For n = 1 To nMax Step nStep
ActiveSheet.Cells(n, 1).CopyFromRecordset rs, nStep
Next

rs.Close
cn.Close




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[rweide]

Very excited to receive this reply so fast. I'll need to try it first thing
Monday morning, since I'll be off until then. Thank you!!!

I've dispensed with the pagesize..
also..
Do you really need the Keyset? adopenForwardonly (0)
seems enough and is "lighter"


on a local test querying an excel file..this works for me..
(I realise OWC doesnt have the 65k row limit)

cn.Cursorlocation = 3
cn.Open

Set rs = CreateObject("adodb.recordset")
rs.Open "Select * from [Sheet1$]", cn, 0 'Static Forward cursor

nStep = 1000
nMax = rs.RecordCount
If nMax > 65535 Then nMax = 65535

For n = 1 To nMax Step nStep
ActiveSheet.Cells(n, 1).CopyFromRecordset rs, nStep
Next

rs.Close
cn.Close




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

Hi, all,

I am trying to load a spreadsheet (OWC) on my ASP page directly from
the RecordSet.

However, since the returned recordset can be huge, I will only load
the spreadsheet with first 1000 rows, if users choose to see the next
1000 rows, I will load the next 1000 rows - pretty much treating the
spreadsheet as a list box but with Excel functions.

I do by something like this:

rs.CursorLocation = 3 'adUseClient
rs.CursorType = 1 'adOpenKeyset
rs.LockType = 3 'adLockOptimistic
rs.PageSize = 1000
rs.Open sSQL, Cnn
SS1.ActiveSheet.Cells.copyFromRecordset rs,1000
And this has been successful. But when I try to load the next 1000
rows, it still displays the first 1000 rows. I do this by:

rs.AbsolutePage = 2
SS1.ActiveSheet.UsedRange.ClearContents
SS1.ActiveSheet.Cells.copyFromRecordset rs,1000

I check the recordset before the "copyFromRecordset" method, and the
recordset is at the correct position. But the "copyFromRecordset"
seems to ignore it, and load the spreadsheet from the BOF of the
recordset. Anyone knows about this? Thanks.

 

[Jamie Collins]

I realise OWC doesnt have the 65k row limit

Does it required? The OP is clearing the 1000 rows each time, so they
will never hit the max rows limit in Excel.

nStep = 1000
nMax = rs.RecordCount
If nMax > 65535 Then nMax = 65535

For n = 1 To nMax Step nStep
ActiveSheet.Cells(n, 1).CopyFromRecordset rs, nStep
Next

I'm not sure this is what the OP wanted.

FWIW, for me the OP's code worked as expected i.e. for each page in
the recordset, the sheet is cleared and the next 1000 rows appears in
its place from the current bookmark rather than BOF. I was using a Jet
..mdb with the OLEDB provider for Jet 4.0 (i.e. fully supports
bookmarks) on my local machine (i.e. no ASP).

Jamie.

--

 

[keepITcool]

again correct.

Jamie?

offtopic...

the other day tou mentioned something re protected mdb's and xls
insert.. is following what you need?

Sub MakeFromLocked()
Dim oCN As New ADODB.Connection

If Dir("d:\fromlocked.xls") <> "" Then Kill "d:\fromlocked.xls"

oCN.Provider = "Microsoft.Jet.OLEDB.4.0"
oCN.Properties("Extended Properties") = "Excel 8.0;Header=Yes;"
oCN.Properties("Jet OLEDB:System database") = "D:\xlSupport.mdw"
oCN.Properties("Data Source") = "d:\fromlocked.xls"
oCN.Open UserID:="keepITcool", Password:="cia"

oCN.Execute "Select * INTO Dump FROM [MS Access;Database=d:
\locked.mdb;PWD=nsa].[Table1]"
oCN.Close
Set oCN = Nothing

End Sub



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[rweide]

Back on topic...=)

I did load an activesheet with about 280,000 rows, it's slow but it works.
However, when I need to leave this page (ASP page), it took forever and
ever..... That's why I am trying to load partially, with 1000 rows at a time,
which speed is acceptable.

But as you've mentioned, my code SHOULD work, but it keeps going back to
BOF, although, I checked, and I know the CURRENT rows is at 1001?!

I still have not tried KeepITcool's method yet. I am off, but anxious to go
back and try it. Even though, this is an ASP, I am using client-side VBScript
to handle this, since OWC spreadsheet is staying in client side.

 

[Jamie Collins]

Jamie?

offtopic...

the other day tou mentioned something re protected mdb's and xls
insert.. is following what you need?

Sub MakeFromLocked()
Dim oCN As New ADODB.Connection

If Dir("d:\fromlocked.xls") <> "" Then Kill "d:\fromlocked.xls"

oCN.Provider = "Microsoft.Jet.OLEDB.4.0"
oCN.Properties("Extended Properties") = "Excel 8.0;Header=Yes;"
oCN.Properties("Jet OLEDB:System database") = "D:\xlSupport.mdw"
oCN.Properties("Data Source") = "d:\fromlocked.xls"
oCN.Open UserID:="keepITcool", Password:="cia"

oCN.Execute "Select * INTO Dump FROM [MS Access;Database=d:
\locked.mdb;PWD=nsa].[Table1]"
oCN.Close
Set oCN = Nothing

End Sub

keepITcool,
Thanks for this. What I actually meant, is it possible to put the path
to the System database in the sql text? i.e. within the bracketed
connection string:

Select * INTO Dump FROM [<<connection string>>].Table1

I strongly suspect that it is not possible.

You I think you can specify the UID and PWD but I assume it uses the
'default' workgroup file (whatever that means!) rather than a specific
workgroup file. Or, looking at your example, perhaps it just ignores
the UID and uses PWD for the database file's password. Whatever, I
don't think I'd get it to work as I would've liked.

Thanks again,

Jamie.

--

 

[keepITcool]

According to :

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/odbc/htm/odbcjetsetting_options_programmatically_for_the_access_drive
r.asp

the keyword is SYSTEMDB


so this might work
(but while experimenting i locked myself (and Admin) out of the test
database : <LOL>

oCN.Execute "Select * INTO Dump FROM [MS Access;Database=d:
\locked.mdb;SYSTEMDB=d:\xlSupport.mdw;Pwd=nsa].[Table1]"


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

Jamie?

offtopic...

the other day tou mentioned something re protected mdb's and xls
insert.. is following what you need?

Sub MakeFromLocked()
Dim oCN As New ADODB.Connection

If Dir("d:\fromlocked.xls") <> "" Then Kill "d:\fromlocked.xls"

oCN.Provider = "Microsoft.Jet.OLEDB.4.0"
oCN.Properties("Extended Properties") = "Excel 8.0;Header=Yes;"
oCN.Properties("Jet OLEDB:System database") = "D:\xlSupport.mdw"
oCN.Properties("Data Source") = "d:\fromlocked.xls"
oCN.Open UserID:="keepITcool", Password:="cia"

oCN.Execute "Select * INTO Dump FROM [MS Access;Database=d:
\locked.mdb;PWD=nsa].[Table1]" oCN.Close
Set oCN = Nothing

End Sub

keepITcool,
Thanks for this. What I actually meant, is it possible to put the path
to the System database in the sql text? i.e. within the bracketed
connection string:

Select * INTO Dump FROM [<<connection string>>].Table1

I strongly suspect that it is not possible.

You I think you can specify the UID and PWD but I assume it uses the
'default' workgroup file (whatever that means!) rather than a specific
workgroup file. Or, looking at your example, perhaps it just ignores
the UID and uses PWD for the database file's password. Whatever, I
don't think I'd get it to work as I would've liked.

Thanks again,

Jamie.

 

[Jamie Collins]

According to :

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/odbc/htm/odbcjetsetting_options_programmatically_for_the_access_drive
r.asp

the keyword is SYSTEMDB

so this might work

It's a good guess but not right :-(

I think a completely different syntax is required e.g. the syntax in
you link for the datasource is 'DBQ' but in the required syntax it is
'Database'.

Cheers,
Jamie.

--

 

[rweide]

I've tried this code. It is still giving me the first 1000 rows. I think this
code, basically, is doing just that, loading the first 1000 rows only?? Am I
wrong? How does it load the SECOND 1000 rows?

I've dispensed with the pagesize..
also..
Do you really need the Keyset? adopenForwardonly (0)
seems enough and is "lighter"


on a local test querying an excel file..this works for me..
(I realise OWC doesnt have the 65k row limit)

cn.Cursorlocation = 3
cn.Open

Set rs = CreateObject("adodb.recordset")
rs.Open "Select * from [Sheet1$]", cn, 0 'Static Forward cursor

nStep = 1000
nMax = rs.RecordCount
If nMax > 65535 Then nMax = 65535

For n = 1 To nMax Step nStep
ActiveSheet.Cells(n, 1).CopyFromRecordset rs, nStep
Next

rs.Close
cn.Close




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

Hi, all,

I am trying to load a spreadsheet (OWC) on my ASP page directly from
the RecordSet.

However, since the returned recordset can be huge, I will only load
the spreadsheet with first 1000 rows, if users choose to see the next
1000 rows, I will load the next 1000 rows - pretty much treating the
spreadsheet as a list box but with Excel functions.

I do by something like this:

rs.CursorLocation = 3 'adUseClient
rs.CursorType = 1 'adOpenKeyset
rs.LockType = 3 'adLockOptimistic
rs.PageSize = 1000
rs.Open sSQL, Cnn
SS1.ActiveSheet.Cells.copyFromRecordset rs,1000
And this has been successful. But when I try to load the next 1000
rows, it still displays the first 1000 rows. I do this by:

rs.AbsolutePage = 2
SS1.ActiveSheet.UsedRange.ClearContents
SS1.ActiveSheet.Cells.copyFromRecordset rs,1000

I check the recordset before the "copyFromRecordset" method, and the
recordset is at the correct position. But the "copyFromRecordset"
seems to ignore it, and load the spreadsheet from the BOF of the
recordset. Anyone knows about this? Thanks.