Invalid Argument

[SWMagic]

I am performing an append query and the Invalid Argument message appears.
This code has been working for years. I've tried to isolate based on some
particular record but that seems to produce nonsensel results. For example I
extract and isolate records 3000 to 7000 on the incoming file and the error
appears. I redo on records 3000 to 6500 and no message. but when I process
4000 to 7000 it is no error...one would have expected a data error to appear
somewhere in the 500 records betweem 6500 to 7000, right?

I've tried compact and repair but that has no effect.

Last I tried to strip down the fields to just the key and it still
fails...and they're just text.

Anyone have any bananas to throw my way?

Paul

 

[Gary Walter]

I am performing an append query and the Invalid Argument message appears.
This code has been working for years. I've tried to isolate based on some
particular record but that seems to produce nonsensel results. For
example I
extract and isolate records 3000 to 7000 on the incoming file and the
error
appears. I redo on records 3000 to 6500 and no message. but when I
process
4000 to 7000 it is no error...one would have expected a data error to
appear
somewhere in the 500 records betweem 6500 to 7000, right?

I've tried compact and repair but that has no effect.

Last I tried to strip down the fields to just the key and it still
fails...and they're just text.

Anyone have any bananas to throw my way?

Yes we have no bananas today...

First thought is that if not coming from a specific record
(which your testing seems to indicate that is not the case),
then maybe it is the "span of records." Does the message
appear if you isolate 4000 different records?

Does "working for years" mean that a field in the table
you are appending to has reached the max for its type.

The classic test is to start a new db, turn Name Autocorrect off,
then import incoming file data, and structure-only of table you are
appending to. Do you get message when run append query in new db?

tests...but no bananas...

 

[Dale Fye]

1. What are you appending from (another Access table, a linked Excel
spreadsheet, a linked SQL or ORACLE table)?

2. How are you isolating records? Is there an autonumber field in your
table?

3. Is there an integer field in the destination table that may have long
integer values in the table you are appending from?

When I get the invalid argument error, it us usually because I am trying to
append a NULL into a non-null field. Check to see if you have any records
that meet this critieria.

Dale

 

[SWMagic]

Hi Dale:

1. Yes, I am appending from a linked table from another file. Does that
matter?

2. There is something odd here. There is in fact an autoumber field as the
key which is about on 3209520. It is a long integer which allows about
2,147,483,648 (per Field Type in Help). The To table is only at 2,373,117.
That should be well below the long integer limit. However, I created a new
empty table (not linked, identical strucutre) and everthing works fine. What
does THAT mean?

Thanks for your input.

 

[SWMagic]

Hi Gary:

Yes, I think you are right in that it is not coming from a specific record.
As I wrote to Dale, I recreated the destination table with no values in it
and the append query worked fine, all records...obviously a problem with the
destination table. But that has only about 2.7 million records and the only
long integer autoincrement key field should allow for about 2 billion. What
gives?

 

[Gary Walter]

I believe you are coming up against the limit
of the size of the table (and/or the file size),
not the bound of the autoincrement key field.

A while back on Access-L listserve

http://peach.ease.lsoft.com/archives/access-l.html

they tried to see how many records they could
pump into an Access table.

In one test, Shamil created a simple 2000 format table
(I believe in its own separate file, then linked
to it)

CREATE TABLE t (f Counter not null constraint p PRIMARY KEY);

93,774,465 rows were first inserted via code until got "Invalid Argument"
then compacted,
and repeated until could not insert anymore.

"in the end 133,676,610 rows inserted (MS Access 2000 mdb format)
File size is 2,147,467,264 bytes (2,097,136KB)."

You obviously will be inserting more than just a pk counter...

Sounds like it is time to start thinking SQL Server or juggling
data somehow in linked tables...