Welcome to WebmasterWorld Guest from

Forum Moderators: ocean10000

Message Too Old, No Replies

Split Access table into 65000 record tables

Split Access table into 65000 record tables



3:38 pm on Aug 17, 2005 (gmt 0)

10+ Year Member

Does anyone know how to write a query so that I can take a table with 1.6 million rows and split it out into multiple tables of 65000 rows or less? I'm trying to do an APPEND query to another table and it gives a key violation, I'm assuming because of the number of rows.



4:17 pm on Aug 17, 2005 (gmt 0)

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member

sounds to me that Access is probably not the best tool for this job.

Have you looked at other databases? The query is pretty simple once you have the tables built, but access won't handle that much data.


4:27 pm on Aug 17, 2005 (gmt 0)

10+ Year Member

Access can easily handle that much data. I've seen Access databases with over 10million rows in a table. It's just not all that fast when you get more than one person using it.

However, i agree with txbakers - it's probably not the right tool for the job. you may want to try out MySQL.


4:34 pm on Aug 17, 2005 (gmt 0)

10+ Year Member

Thanks. I use mysql a lot. Will just have to play around with ODBC connections with it.
I figured out the problem with this particular issue. It was a problem where my primary key was not set to "auto number" so it was looking for a number to stick in there. I switched it and away we went.

I will look into using mysql for this project though. Thanks for the advice.