Every time I try and copy/paste a large amount of data, I lose the first field

[almag]

Hello!



I'm trying to add data from excel to a table in Access, but every time I copy and paste new records, my first field is replaced with what looks like a primary key; Access replaces the entire first column with numbers, the first record becoming 1, the second becoming 2, etc. Has anyone run into a similar problem?

I'm new to Access, so please let me know if my question is unclear or if you need additional information. Sorry in advance if this is a common issue or if I posted this in the wrong sub.
Any help would be greatly appreciated.

[JoeM]

Better to import the data than to use Copy/Paste.
If you make sure that the field names in Excel match the field names in Access, and the Data Types correspond, it should work (though sometimes inconsistent/flaky data can cause import issues, in which case I usually like to export my Excel file to a text file, and import that).

[almag]

That's what I'm starting to try and use, but my one worry about that method is that I'll accidentally bring in duplicate records. Will the append query automatically look out for duplicates, or is there something else that I need to do?

[rbennion]

I have dealt with this before. I hacked it by inserting a blank column in Excel titled "ID", then copy and paste. As mentioned, best way is to import it, then you can specify whether or not to include the autonumber PK.

[JoeM]

If you have a primary key and/or set indexes on certain fields, you may be able to prevent duplicates that way.

Otherwise, you can import to a temporary table and create a Query to find just the new records, and add them to your Main table using an Append Query.

[almag]

Each record does have an unique identifier, so I didn't include a primary key. Would that be enough for MS Access to recognize whether or not it's a duplicate record when appending?

[JoeM]

If you have set up the field in Access to not allow duplicates, it should automatically reject those records and not import them.

[Micron]

If you have set up the field in Access to not allow duplicates, it should automatically reject those records and not import them.

But you will get the prompt about not being able to add all the records due to key violations. If the user can simply accept and choose not to be advised of each issue, then no problem, but this scares people. Otherwise, I think a macro or code so that warnings can be disabled will be needed. Code is the more elegant way of course, and probably wont' require warnings to be cycled if the Execute method of CurrentDb is used.