Have you tried a simple Append Query rather than VBA?
Have you ensured that the Field Data Types match, not trying to put a Memo in to Text field for instance?