What is a search key to import Excel to Access?

[Sue]

Trying to import an Excel spreadsheet into an Access 2007 table. Get the
error "The search key was not found in any record". I never encountered this
in earlier Access versions and can't find anything in Help about a search key.

 

[Ken Snell [MVP]]

That error message typically indicates a corruption of the ACCESS database.
Typical way to fix is to create a new database, and import all the objects
from the older database into the new database. In ACCESS 2007, this is done
by clicking on External Data tab on the ribbon, clicking on Access icon in
Import group, and follow the wizard's prompts.

 

[Kaykayme]

I had the same error but by removing the spaces at the beginning of the field
names in my spreadsheet I was able to import my file.

 

[beejay56]

Agree with kaykayme

I was having the same problem when I found this forum and posting. I took another look at the spreadsheet I was trying to import (it was created by another person) and there was a column heading with a space in front. Once I removed that space, the file imported fine. This is something you might want to just try first.

 

[toon999]

I had this problem.
1. when trying to use the first row for field names had the word 'access' in some - when removed the error about bad names went away
2: while it looked as though there was no white space in the field names, Had the search key error. when I used =Trim(<fieldname>) then pasted the names back in it all imported a treat.

all this about compacting and the like not important.

 

[NottsGuy]

I found Access didn't like a column header "inc VAT". I changed it to "Inc VAT" and all was OK.

I found out by opening the Excel spreadsheet and copying each column individually, then pasting it into a new Access table, each one worked until I got to the culprit. I made the change in Excel and then the import worked without a hitch.

Try this before assuming that your entire Access database is corrupt.

 

[Craig Cover]

In a little different light, my macro needed to delete a row above the column headers. The file was read-only, which gave no error indication until I went back to import the data. Since the column headers were still in row 2, I got the same error as you all did.

 

[Dick Pierce]

Thank you all so much - I'm at the very end of a month-long access/excel project - have endured crashes, viruses, a lost file, tec. - final step is to export excel to access and I get this message - The search key was not found in any record. Anxiety level very high - exasperation on steroids - nothing in the access 2010 help stuff.

Then I stumbled on your site - got the hint on spaces before field names - took spaces out of 4 field names and Wonders happened - it worked like always!! Thanks so much to all of you who had a hand in the fix of this most obtuse and abstruse problem - how in the world does a blank in a field name cause this. Don't care now - it's fixed - I'll never do it again - it's behind me now - thanks to you. YIPPPPPEEE