large recordset - error -2147217887 (80040e21)

[oraustin]

My code, visual basic 6.3 for access 2002 SP3, returns a large
recordset
strSQL = "SELECT * FROM tblMobileCalls WHERE Client = '" &
strClient & "'"
rstCalls.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdText

400,255 rows, I loop through each record in turn setting a field equal
to some string value. The code stops on the .movenext command (.eof =
false) with 31048 rows left to process with 'Run time error 2147217887
(80040e21) - invalid argument'

Heeeeeeeelp :) !!!
Please can anyone shed light on the problem
Thanks

[teddysnips]

or******@hotmail.com wrote:

My code, visual basic 6.3 for access 2002 SP3, returns a large
recordset
strSQL = "SELECT * FROM tblMobileCalls WHERE Client = '" &
strClient & "'"
rstCalls.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdText

400,255 rows, I loop through each record in turn setting a field equal
to some string value. The code stops on the .movenext command (.eof =
false) with 31048 rows left to process with 'Run time error 2147217887
(80040e21) - invalid argument'

Try this:

http://groups.google.com/groups?q=2147217887

It may be that you need a later version of MDAC or something.

Edward

[summerwind]

I would use an UPDATE Command rather than looping through a recordset,
assuming of course that your SomeString is the same for each record you
are updating. Something like this:

Dim Comm As New ADODB.Command, ComString As String

ComString = "UPDATE tblMobileCalls SET FieldName = " & SomeString &
"WHERE Client = & "" & strClient & ""

With Comm
.Activeconnection = CurrentProject.Connection
.CommandType = adCommandText
.CommandText = ComString
.Execute
End With

Set Comm = Nothing

If I've got the syntax right, that should update your table without
using up your available memory as I think is happening with your
current method.

Even if using a recordset, you should try to only address the fields
that you are going to work with, especially if working over a network.

Good luck.

[oraustin]

I think the problem is because the database had become larger than 2GB
Can't use an UPDATE method because it is too slow - this already takes
ages to run
Don't worry :) the select * is fine - I need all the fields

Anyway - now my database is corrupt - I hate Access sometimes
arghhhhhhh!
Have to port to a proper DB now :(

[David W. Fenton]

or******@hotmail.com wrote in
news:11*********************@g14g2000cwa.googlegro ups.com:

I think the problem is because the database had become larger than
2GB Can't use an UPDATE method because it is too slow - this
already takes ages to run
???

A SQL update will *always* be faster than looping through the same
records and changing the one at a time. That doesn't mean it will be
FAST, but it will always be faster than sequential processing of the
records.
Don't worry :) the select * is fine - I need all the fields

Anyway - now my database is corrupt - I hate Access sometimes
arghhhhhhh!
Have to port to a proper DB now :(

The issue is NOT Access -- you are not using Access, but only the
Jet database engine.

And the issue is not Jet that's the problem. It's entirely pilot
error in that you didn't plan for this when choosing the data store
for your application.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[oraustin]

really? are you sure it will be faster? i'll do a little test.
does changing the recordset in effect run SQL behind the scenes anyway.
I am changing several fields on every row of the recordset to a
different value each time.

Yes I know it's my fault for not choosing a more robust data store
although I never knew it would get so big.

[David W. Fenton]

or******@hotmail.com wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

really? are you sure it will be faster? i'll do a little test.
does changing the recordset in effect run SQL behind the scenes
anyway. I am changing several fields on every row of the recordset
to a different value each time.
Well, that's somewhat different -- if you're running an update on
other tables for each row of your recordset, it's likely you *can't*
do that in plani SQL.
Yes I know it's my fault for not choosing a more robust data store
although I never knew it would get so big.

My points is: don't badmouth Jet for a mistake that is *your* fault.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc