I'm currently connecting through MS Access via an ADP connection to SQL Server. I'm copying 253,000 records from an MS Access db on my desktop through the ADP to SQL Server.
Local machine: 1.8Ghz, 500MB RAM, 253,000 record MS Access table
SomeName.adp table on shared server (don't know the specs on the server but I'm sure it's beefy).
I'm copying the records 50,000 at a time, and the whole process of pasting all 253,000 records takes about 3 hours.
Is this normal for data this size? Also, my memory usage shoots up dramatically with the paste operation, rather than the copy operation (seems backwards?). I just sit here and watch memory usage climb as it pastes to the second database table. Then, at the point (which is reached very quickly) where I've run out of available RAM, I assume Windows starts paging.
Am I correct in assuming that I may need more RAM (to avoid disk IO). Also, why does memory climb steadily during a paste operation?
Have you tried using an update or make table query instead of copy and pasting the information. I am guessing that to cut and past such significant amounts of information over a network using local memory creates such overhead because the information is not stored in its ideal method.
Perhaps you could do a delete query, then an update query driven by a select statement from your ADP tables.