Access – Copy/Paste Into a Table

I thought I’d quickly post a little performance tip for copying and pasting data to populate tables.

A Quick Note About This Tips
The following tips doesn’t truly apply when copying and pasting small datasets, but is aimed at copying and pasting large amounts of data.

Access Copy/Paste Tip

I was working on a personal project, a demo for this site, in which I had created the simplest of tables:

  • 2 Columns
    • an AutoNumber Primary Key
    • a Text field

The data was coming from a simple text file and was comprised of some +450k entries that I simply wanted to import into the table to initially populate it with.

I tried to copy/paste the data into the column and after 5 minutes of watching the progress bar slowly creep forward I canceled out of that operation.

Instead, I copied the text file data into Excel which took a split second!  Then I imported the Excel worksheet into Access and ran an Append query to push the data to my table and then deleted the imported Excel worksheet table.  Even with such a convoluted approach, it was much faster.

Conclusion

So word to the wise, when importing large amounts of data, consider using Excel as an intermediary, it may just save you some serious time and frustrations!

But Why?

The real question here is why is a simple Copy/Paste into Access so slow that resorting to such a workaround is the better options?  What exactly is going on behind the scenes?  Why can I Copy/Paste into Excel without issue and not Access?

Sadly and don’t know the answer to any of these questions, but did find this to be very interesting to say the least.