Split Access table into 65000 record tables

[knippysing]

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.

Thanks

[txbakers]

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.

[Prolific]

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.

[knippysing]

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.
C-