Runtime errors: ADO (80040e21), DAO (3001)

[ET]

Hi, I need help with run time errors.

The application is running on Access 2002.

It is recordset, loops the records from query, one by one row,
in ADO it gives error 80040e21, invalid argument, -2147217887,
the same code but adjusted for DAO gives the following error:
run time error '3001', invalid argument.

Here is the code for DAO:
=======================
------------------------------------------------
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

strSQL = "SELECT " & _ and bla, bla, bla... SQL code goes after
this line...

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
------------------------------------------------

.... basically, the SQL query will pull records from 2 tables, by
matching
their IDs which should be identical, and then VBA code will try to
match
the records based on Item Description field (string), that Description
will be first copied to 2 arrays of strings (WORDS_TB and WORDS_WO
bellow):

(more code for DAO):
------------------------------------------------
If CountWordsInTB = 1 And CountWordsInWO = 2 Then
If WORDS_TB(0) = WORDS_WO(0) Or WORDS_TB(0) = WORDS_WO(1) Then
With rst
.Edit
!FILTER = "OK"
.Update
.Bookmark = .LastModified
End With
NumOfOK = NumOfOK + 1
GoTo lastline
End If
End If
------------------------------------------------

The error message in DAO always comes on the line with .Update

It chokes on something, don't know what and why,
if there are 20,000 records to be updated, it might give error on
the record number 99, but if query sorts records by IDs,
then it gives error on record 2730...


<><><><><><><><><><><><><><><><>
<><><><><><><><><><><><><><><><>


ADO error message:
=======================

The error message with ADO is diferent...
It always points on the line rst.MoveNext
which is located, naturally, at the end of the loop,
right before "Loop" command:

Here is ADO code:
------------------------------------------------
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

rst.Open ("SELECT " & _... bla, bla, the SQL query is identical
with query in ADO...
------------------------------------------------

.... then, Item Description fields from both tables go to String array
named WORDS_TB and WORDS_WO, and there are many if statements
that try to compare description, if it matches it will set the FILTER
field to "OK":
------------------------------------------------
If CountWordsInTB = 1 And CountWordsInWO = 2 Then
If WORDS_TB(0) = WORDS_WO(0) Or WORDS_TB(0) = WORDS_WO(1) Then
rst("FILTER") = "OK"
NumOfOK = NumOfOK + 1
GoTo lastline
End If
End If
------------------------------------------------

In this ADO case, the command "GoTo lastline"
points to the code:
------------------------------------------------
lastline:
DoEvents
updateCount = updateCount + 1
Me.Label11.Caption = updateCount
Me.Label14.Caption = NumOfOK
Me.Label15.Caption = GP_NUM_1
Me.Label17.Caption = GP_NUM_2
DoEvents

rst.MoveNext
------------------------------------------------

The error in ADO always come on the line "rst.MoveNext".

And it comes on different record then in DAO...
In DAO it was 2730 and in ADO it was on 3442 record, with the same sort
order in query...
This is valid for about 20,000 records, which is smaller amount of data
set up just for testing...

But, with real data, which has around 680,000 records, the ADO code
gives error on the line 363,872...

I don't know what else to add... please help, I tried even Repair
option for MS office
on "Add or Remove programs" in Control Panel...
actually, I had to do Repair because yesterday Access couldn't save
the simplies SELECT query, it give error... after repair it was fine.


Thank you!

 

[ET]

With ADO, I'm using ADO 2.7 library, and also 2.7 recordset library.

With DAO, it is DAO 3.6 version.

ADO - Both of the tables are linked tables, one is Access table,
second table is DBase table.

DAO - One table is linked (DBase), second table (Access) has been
imported to local machine.

 

[ET]

With ADO, I'm using ADO 2.7 library, and also 2.7 recordset library.

With DAO, it is DAO 3.6 version.

ADO - Both of the tables are linked tables, one is Access table,
second table is DBase table.

DAO - One table is linked (DBase), second table (Access) has been
imported to local machine.

 

[ET]

Here is the solution:

You need to run server side cursor for Recordset, not the client side
cursor...

Then, the server has to be ONE database, not linked tables, one here,
two on shared drive etc,
like I did originally had... if query runs on few tables and tables are
on different databases,
then even thought you specify server side cursor, it will go back to
client side cursor
and with cliend side cursor you get the error from subject, since they
are Microsoft bug...

Then, make sure the database will not grow above 2 gigs during the
update/append of the tables...
If you want to update text field, from NULL to some value, no matter
how short, even one character "a"
still, the windows will allocate 4KB for every record... since I had
over 600.000 records in table,
you can imagine that limit of 2 gigs was achieved very fast...
The solution to this problem is to set text field you want later to
update, not to NULL,
but rather to, for example "NOTOK", and then later, in loop within
Recordset
update the field to "OK"... this way, no new memory allocation occurs,
since field value is
being replaced with new value...

 

[otterbyte]

ET,

This is almost exactly the problem I'm running up against - thank you
for your post. I have a question, though - here is my setup:

Access 2k database
DAO 3.51
Local table - though this will eventually be a linked ODBC table when I
am done coding and testing

Basically, I am shoehorning data from a new, normalized db into an
older denormalized db for backward compatibility until we can get
several db's that depend on the old data updated. This means I have to
denormalize a bunch of test results from one record per test to one
column per test, each record showing the combined result of all the
tests. The new table has over a million records. The denormalized
version will have over 200,000 records. But I'm running into that 2gig
limit by the time I'm 25% of the way through reading the new table. The
denormalized table starts as a shell and I append/update the
information using .AddNew and .Edit. So what is the best way to set the
text field to a value that I later update? There are no records in the
table before I start the code. Should I have default values of " " for
all my text fields?

TIA,
Erika Sparks

 

[ET]

Erika,

I already give short explanation what I did to solve that problem:
----------------------------------------------------------------------------------------------
The solution to this problem is to set text field you want later to
update, not to NULL,
but rather to, for example "NOTOK", and then later, in loop within
Recordset
update the field to "OK"...
------------------------------------------------------------------------------------------------

Here it is again:
This is valid if records are set to NULL before update:
When you use Recordset to update text field (table in general)
then, for every new record Windows operating system
allocates 4KB of memory... maybe more or less, depends
on Windows settings.

In order to avoid allocating of 4KB for every record,
use Access database engine who will pack records
so they use minimum amount of memory space.
To do this, first run query on the text field you want to update,
set it to some value, for example:
UPDATE TABLETEST SET TEXTFIELD TO "NOTOK"
Now text field is ready... In general, preset the text field value
with string longer then expected values in it after
update with Recordset.

Later, when you run Recordset update, no new memory allocation
will occur ever, the database size stays the same,
because - there is no need for new memory space,
since there are already some values in that table in that field,
thus, memory is already allocated.

All that will happen with Recordset update is replacement
of one value (for example "NOTOK" with some other value
(for example "OK").

 

[otterbyte]

Ah, I see! The part I was not getting was to use Jet/SQL instead of VBA
to set the initial value. I will try this right away and thank you
again for your incredibly timely assistance.

Take care,
Erika