How many rows can Access 2003 handle?

[angelesta]

Hi All,

I've read a couple of posts that mention that once Access is over 2GB it becomes unstable.
That definitely explains the problems I've been having!

My last query is a make table query that has over 2mm rows - I've added some filters to make this a little smaller so that my database remains stable however, I think it's still too big.

Any idea if that is what is making my database too big? I link a pretty large external file, then I run 8-9 append queries.
My final query has 3 outer joins for the make table query but that is where it all goes haywire - it does not like making a table of 2.1mm rows I guess!
Any suggestions on how to minimize this database?
Thanks!

 

[CT Witter]

2 million rows is large and will slow access down, but it can still be useable.

Have you tried a compact and repair (prior to creating a 2 million row table)? If you do after the large table, it will take a long time. PS. make a backup first.

Do you have indexes setup? If not, indexing may help with performance as well.

If you will be adding new data and the table will keep growing, I would suggest looking into SQL (SQL server Express is free).

HTH,
CT

 

[angelesta]

Awesome thank you!

No, no indexes but should probably set those up because something my final make table query is making my database explode from 1GB to 2GB and then I keep getting "Invalid Argument."
I usually do everything in Design View and let Access figure the rest because I'm not very experienced but I'll have to consult my book.
Thanks so much! This is really helpful!

 

[boblarson]

Access can only handle 2Gb of data (including system files). If you are getting close to that or exceeding it, I would highly suggest moving to SQL Server or SQL Server Express. SQL Server Express 2008 r2 can handle up to 10 Gb of data.

Also, you should be making the table ONCE and not using make table queries to remake a table each time. Delete the data and repopulate it with an append query instead. But for this large set of data, you probably are going to have problems regardless.

One more thing - Access can handle millions of rows of data. However, it is limited to a certain page size per record, although I don't know what it is.

 

[angelesta]

Thank you! That is helpful to know! My final table is the make table query - I am using multiple append queries and my database is around 1GB. Once I make my final make table query, that is when the database explodes to over 2GB and shuts down.
Thanks for telling me about this other program - I will get in touch with my IT department to see if we have it.
THanks everyone!

 

[HiTechCoach]

Thank you! That is helpful to know! My final table is the make table query - I am using multiple append queries and my database is around 1GB. Once I make my final make table query, that is when the database explodes to over 2GB and shuts down.
Thanks for telling me about this other program - I will get in touch with my IT department to see if we have it.
THanks everyone!

At 1 gig I would definitely move to an SQL server.

I you must absolutely stay with Aces then you may need to go to using multiple back ends.

 

[angelesta]

Because I'm being asked to update these massive tables of data, I am now appending different tables to different databases to keep the size down.
However, as the year continues forward and more orders come in, there are more lines of data...so eventually there comes a point where even appending all these tables to other databases is blowing it up.
Have to get SQL server!
Thanks everyone!