Invalid Argument

[Bruce]

I am running Access 2002. I keep getting an Invalid Argument when ever I try
to run an append query that results in more than 13 million records. Once
this happens I have all kinds of problems trying to get the database to
respond. When I try to repair/compact I get the error message. I notice that
the size of the database exceeds 2 gig. Is there a size limitation for Access?

 

[KARL DEWEY]

2 Gig.
Try appending less records, compact and repair, repeat for the next batch or
records.

 

[Jeff Boyce]

Bruce

"13 million records" ... each one two bytes, or each one 2000 bytes?

Karl points out that you might be able to work in smaller batches, rather
than all at once.

Do you need to import/append the records? Can you link to the data source
and use it there?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Toews [MVP]]

I am running Access 2002. I keep getting an Invalid Argument when ever I try
to run an append query that results in more than 13 million records. Once
this happens I have all kinds of problems trying to get the database to
respond. When I try to repair/compact I get the error message. I notice that
the size of the database exceeds 2 gig. Is there a size limitation for Access?

13 million into 2 gb is 153. So if the data in each record is about
140 bytes or so, without any indexes, then you've hit the limits of
Access.

You might be able to use multiple MDBs but I'd suggest another product
such as possibly SQL Server 2008 Express. However that program only
allows a 4 gb data file size so you may have troubles even with it.

http://www.microsoft.com/sqlserver/2008/en/us/express.aspx

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Bruce]

Tony,

Thank you for your reply. I sort of suspected that and was going to try and
use SQL Server. It seemed like everytime the size of the database exceeded 2
gig that I got the error. Was so bad would not let me compact as I would get
the error when attempting.

Thanks again to all, this is a great site and I use it often.

 

[Tony Toews [MVP]]

Thank you for your reply. I sort of suspected that and was going to try and
use SQL Server. It seemed like everytime the size of the database exceeded 2
gig that I got the error. Was so bad would not let me compact as I would get
the error when attempting.

Correct. You do get invalid argument when the MDB exceeds 2 Gb and
you can't then compact.

Thanks again to all, this is a great site and I use it often.

Come on back anytime. That's what we're here for.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/