AutoNumbered PK, "Invalid Argument" At 20,847,689?

[PeteCresswell]

Iterative routine, creating a bunch of yields for investment vehicles
into a work table.

I threw in a autonumber "RecordID" to give it a PK just on GPs.

My understanding is that an AutoNumber field is Numeric Type = Long
and that Long is good up to a value of 2,147,483,647.

But this thing is going belly-up when it tries to create record
#20,847,689.

The error thrown is "3001: Invalid argument.".

I can replicate it by opening up the partially-filled work table,
adding a new line, and trying to tab out of the line - so I don't
think it's anything with my VBA routine.

Also, if it go directly to the work DB where the table lives and
remove the AutoNumber field from the table I can successfully add a
record.

Finally, the size of the work DB is 2,097152 kb.... which makes me a
little suspicious....yet I can still add that record after the
offending field has been removed.

Anybody seen this?

(I should add that this is something of idle curiousity because I
should be able to just remove the autonumber PK ...)

 

[John Spencer (MVP)]

Well an Access (JET) Database is limited to 2 Gigs in size. So the error
probably has more to do with exceeding the maximum size than with the
AutoNumber being too large a value.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[PeteCresswell]

Well an Access (JET) Database is limited to 2 Gigs in size. So the error
probably has more to do with exceeding the maximum size than with the
AutoNumber being too large a value.

I think you've nailed it.

Backtracked and began with an empty work DB, eliminating the
AutoNumber for good measure.

Size went up to a little over a gig with 40+ million records, and the
routine did not abend.

But when I ran a query to copy the work table contents to the back
end's table it threw the same error as when I tried adding a record
the first time -when the work DB was at 2 gigs.

Post-copy-attempt, the back end was at 2, 095, 108 mb.

Oh well.... -)

 

[John Spencer]

Perhaps you can simply link to the table in the new database. You can't
import the records if it makes things to large, but you can work with
the data in the table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[(PeteCresswell)]

Per John Spencer:

Perhaps you can simply link to the table in the new database. You can't
import the records if it makes things to large, but you can work with
the data in the table.

That's certainly an option.

I'm not wild about bco the loss of relational integrity
enforcement and cascading deleted.

Still might do it, though.