Major Boating of backend

[dbay]

I need to know the best way to insert data into the backend without cause major bloating of the backend. I am currently inserting about 20k rocords at a time using an INSERT INTO statement. The backend went from 83mb to 511mb trying to insert the last 20k records. Thank you.

 

[dbay]

Have you tried a compact and repair on the BE?

Yes, it brings it back down to 81mb. The boating happens when there are more than one person accessing the backend while inserting around 20k records. If only one person is dumping records the bloating is not that bad. There are no bound forms in this split database. Everything is handled with either recordsets or docmd.runSQl.(Yes, I am closing the recordsets)

Reading this from MS my be my answer: http://support.microsoft.com/?kbid=174341

The linked article above is exactly what is happening to my database, except mine is in Access 2007. A co-worker was testing the 20K record dump into the backend. The table has a primary key that does not allow duplicates. The 20k records are all duplicates and were rejected. Using the docmd.runSQL "INSERT INTO" method can cause bloating when there are rejected records involved. So tomorrow I will try the execute method instead and see if that fixes it.