Paste Append not working in Access 2007

[jsbdives]

I have a table where I need to be able to paste data from Excel into the table in Access. I'm usually pasting about 100-300 records when I do this. I've used paste append successfully for years in Access 2003 for years. Now that I've upgraded to 2007 it no longer works and I get this error message (also attached as image so I don't have to type out the entire error message): "The data on the clipboard is damaged, so Access can't paste it". Now normally I would think there really was something wrong with the data. I tried saving it into a new spreadsheet in Excel before copying and paste appending in Access - didn't work. I tried different paste options - nothing. I tried highlighting some records rather than all the records - nothing. Then I went to my coworker's computer, still running Access 2003 - and took all my normal steps to paste append and it worked instantly.
Soooo - any ideas? I really would like this to work in 2007 and I'm not great at trouble-shooting in Access (I'm a bit above a beginner but can't write code or fix the problems I end up in).

 

[GinaWhipp]

See...
http://support.microsoft.com/kb/2221635

 

[jsbdives]

Thanks for the link. I was really excited about it at first because it directly addressed the error message I was dealing with, but sadly none of the suggestions to fix the problem worked. Not being able to use paste append may render my database useless, as I need to be able to import latitude/longitude points by the hundreds when we do a survey. Do you or anyone else have other ideas that may help?

 

[spikepl]

Not being able to use paste append may render my database useless, as I need to be able to import latitude/longitude points by the hundreds when we do a survey.

You are using copy/paste to transfer data - what about all the other ways of exporting/importing data?

 

[jsbdives]

I'm not that awesome at Access and this way worked for me. I'm always open to any other suggestions. I had another person suggest linking to the excel table and running an append query to pull the data in. I've currently linked the data but I'm trying to learn how to run an append query right now. =)
Any ideas of fixing the problem?

 

[spikepl]

"Another person"'s idea sounds fine.

 

[GinaWhipp]

Hmm, oh well, I thought it would help. So, let's try this...

1. Open the Access database
2. Open the Excel spreadsheet
3. Put both windows next to each other
4. Drag worksheet over to Access and drop in Navigation Pane.

I know that works in Access 2010 and it should work for Access 2007.

 

[jsbdives]

Thanks for the suggestion! I'm dropping the worksheet into the table section of the navigation pane and it isn't working. Was I dropping in the wrong spot?.

I also tried linking to the data and then creating an append query (first time I've done that). I get to the point in the append query where it tells me I'm about to append 166 records to the table, I get excited it will work, and then I get this error message (see attached screenshot). Any other suggestions? =)

 

[GinaWhipp]

My mistake , you can only highlight the columns that have data select copy than move over to Access and paste. I forgot the drag and drop doesn't work because Excel had more columns then Access will allow.

 

[GinaWhipp]

Oops, that message is telling you the Table can't add the records because their some field in their set you be unique and the ones you are pasting to it break that validation.

 

[jsbdives]

I may have figured it out! I thought I had created a record in the table above the table I was trying to import data into but I had not. Silly me! I needed that initial data in order to know the correct autonumber so the imported data would relate to the tables above it. Once I created that record, I was able to get the paste append to work following the methods I described above. So I may have it working! I just want to try one more time to make sure I can replicate the methods again.

 

[GinaWhipp]

Well, standing by just in case!

 

[jsbdives]

It works - but I don't understand why a reader in a different Access forum (not this one) suggested "linking" to the excel spreadsheet rather than just importing the data (no append queries needed). I followed these steps below and I was able to get the data into the table I needed with far less steps than the append query. Can any of you tell me if there is anything wrong with doing it this way?
1. Open Access and click on “external data”
2. Under the “import” section of external data click on “excel”
3. Browse to the excel file with the data and click on the “append a copy of the records to the table” and select the table to append the data to
4. Run

 

[GinaWhipp]

There is nothing wrong in doing it that way. I am gathering you are getting different solutions because...

1. Question interpretation
2. Because there is always more than one way and everyone posts their *favorite/easiest* way
3. It's the way they would do

 

[jsbdives]

Oh lovely! Thanks for the help - I appreciate it.