Strange behavior when copying and pasting records

[ultrarunner2017]

Hello;
I have experienced a very strange problem with Access 365 when copying records from one database into a table in another database.
I have a relational db set up for the catalog of my music library. I have a hierarchy of tables as follows:
Artists > Albums > Songs. I have additional tables for Genre and Media.
I am copying records from an older database containing the same information into a new database.
I have been working directly with the tables, not with forms.
I click the artist to open the albums table, and copy the album title from the old db into the new.
Then I click each album for that artist and copy the songs from the old to the new db, selecting all tracks in the old, copying, then selecting the entire (empty) record in the new database, and paste.


For almost all of the records, the paste works without errors, but every now and then I get paste errors. I don't recall the exact wording, but basically, Access isn't happy with the field types, even though I cannot see anything wrong with it.

Most of the time when I get the paste errors (which are copied into a table titled 'paste errors', nothing bad happens, except that the record will not paste into the new db.
But occasionally - seemingly out of the blue - after the paste error, I find that the albums table in the new db now contains additional fields. These fields come from the songs table of the old database.
I don't know what's going on, as I am unable to repeat the problem when I delete the extra fields, and do the copy and paste again.

I would expect that if there was embedded code in any of the fields in the old database, I would be able to repeat the problem, but that is not the case. Sometimes I can repeat the paste errors, but the additional fields do not appear in the new table.

I am at a loss to explain why this is happening, and have tried several Google searches, but found nothing relevant.
Maybe someone here has seen this strange behavior?

Note: Before I installed MS 365, I had Office 2010 Professional installed. The old database I am copying from was created in Access 2010.
After having this problem, and another issue with Word 365, I un-installed Office 2010. Doing that seems to have fixed the Word issue, but apparently not the Access problem.

Thanks for your help
Ultrarunner

[Micron]

Sounds like you have field type conflicts between the tables (e.g. text vs number) or constraints (nulls not allowed, character length limits, etc.). Or maybe you're trying to copy/paste record id fields (like autonumber pk). The error message(s) would help.
Is there a reason why you cannot use the old tables with the existing data, or just the tables structures?

[davegri]

I am copying records from an older database containing the same information into a new database.

Why all the copy/paste? Why didn't you just open your existing Access 2010 DB in Office 365? No conversion necessary.

[ultrarunner2017]

I did open the original 2010 database in Access 365. But I decided to rebuild the database, to make it more 'functional'.
I created a new database in 365, but after entering all my data into it, I suddenly understood why one should never - ever - use a data field as the primary key of a table. I had done exactly that, thinking that since I didn't want to allow duplicates in at least one field in each table, I thought I could just use that field as the primary key.
After trying to run my first query in the new db, I realized that I had made a big mistake.
I thought I could repair the database (and perhaps I could have) by adding an auto-number field and assigning it the primary key. But it didn't work, so I gave up and rebuilt the table using the correct protocol. So now, I'm stuck with the job of re-entering the data into the new database.

Since this is a personal - and not work - project, I don't have any worries about having to rebuild the database and re-enter the data. It's kind of relaxing, and I always listen to music or an audiobook while entering data.

[davegri]

I can't help with the copy/paste as I have never done that with Access other than the contents of a field or two within the same table.
What I would have done is import the necessary tables from the old DB to the new, thus eliminating the need for both DBs to be open at the same time.
Then I would have modified the structure of those tables to accommodate the new schema, with autonumber PKs and empty FKs as required.

Then I would have written VBA procedures to loop thru parent tables (Artist and Album) to update the Artist table Album FK with the PK extracted from the Album table. Same for Song FK into the Album table extracted from the Song table PK. The FKs would be found via DLookups in the code. The DLookups would use your currently existing fields to find the proper matchups.

Not very efficient, but not hard to understand once you understand and establish the table relationships. And it's a one-off.

I have written an Access app to read the entire Windows Media Library (audio) into a DB, extracting necessary metadata for title, album, artist, genre, duration and so on, with a form to display and search the data and play the songs.
This reduces manual data entry to zero (other than keeping consistent MP3 metadata outside of Access, and there are free programs to assist with that). If you'd like to see it, PM me.