Append Named Range from Excel into Access 2010 Table

[Paul Howarth]

My Access 2010 database is suddenly not appending Named Ranges from Excel into and Existing Access 2010 Table. I have not written any code. I simply right-click the Table-Import-Excel and follow the prompts to Append the Excel Named Range (ML) to the Access Table (Mailing List).

I'm stumped. Normally when Access is unable to append all the data, it will indicate the number of records lost due to key violations. But for some reason it is suddenly Appending NONE of the records in the Named Range and not letting me know via an error msg.

For trouble-shooting purposes, I am successful when I manually import the Named Range into a NEW Table (EXTERNAL DATA-EXCEL).

Just can't get "Append a copy of the records to the table:" to work like it has been working all year long.

Any suggestions?

[twinnyfo]

Have there been ANY changes to how the data in Excel is saved? I.e., have any date fields suddenly become text fields? You can check this by clicking in the cell and changing the format to a different style of date and if it changes, then it is a date.

I am familiar with Access having a mind of its own that changes as often as I change my socks... But there is usually something behind the mood swings.

[Paul Howarth]

Hi Twinnyfo,

My initial reaction to your question was "no". But after spending several hours testing different things, I realized that I had used the =Upper function in Excel to convert all the mailing addresses to Upper Case. This was followed by a Paste-Values onto my original Named Range which is what gets appended into my Access Mailing Table.

Sure enough, there must be some invisible gremlins that crept into the Named Range by my actions because it would NOT Append and Access would not notify me that the Append failed - as it usually does.

My final test - retyping the addresses into a fresh Excel import spreadsheet template - was successful, and the Named Range imported just like it always does.

Thanks for the nudge.