We help IT Professionals succeed at work.

error in Access "The search key was not found in any record"

Ian Bell
Ian Bell asked
on
135 Views
Last Modified: 2020-11-13
Hi,
I am getting the following error while trying to import a csv file into Access 2016
"THE SEARCH KEY WAS NOT FOUND IN ANY RECORD"
followed by
"an error occurred trying to import file (filename) the file was not imported"
Any help appreciated
Ian
Comment
Watch Question

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Ian

Do any of the field names in the CSV contain unusual characters, e.g. *,? etc.?

Or are there any with reserved words e.g. Date, Name etc.?
Ian Bellretired

Author

Commented:
Hi Norie,
I changed date to days and removed a full stop (.) after a fieldname and this has had no effect.
I successfully imported a similar file even with a date field but this one proves to be a problem..
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Ian

What are the field names?

Also, how exactly are you importing the file?
Ian Bellretired

Author

Commented:
I am importing as a text file csv.
I chose headers has field names
please see attached
EE-Fieldnames.xlsx
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Ian

I don't see anything wrong with those field names, what happens if you choose the option that the header doesn't contain field names?
Ian Bellretired

Author

Commented:
same problem
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Last shot, for now, what happens if you import into an existing table with matching field names?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Your problem is that "Days" starts with a space.

Jim.
Ian Bellretired

Author

Commented:
I wish that were the case Jim. I had put the space in to see if that made any difference. It doesn't work with or without a space.
Excuse my ignorance as I am a novice when it comes to SQL... but what exactly is a search key ?
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
The fields ' UKHR_EntryID',' UKHR_HorseID',' UKHR_TrainerID',' UKHR_JockeyID',' UKHR_CourseID',' LengthsBehind',' LengthsBehindTotal' and ' Duration' also have a space at the start.:)
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
LOL....I stopped with Days as soon as I found it.  I guess I should have looked further.

<<Excuse my ignorance as I am a novice when it comes to SQL... but what exactly is a search key ?>>

 That's not part of SQL, but rather just something you'd do with a DB in general.   Access is saying it has a key it needs to look for and can't, which shouldn't happen.

Jim.
Ian Bellretired

Author

Commented:
All spaces were removed and I was able to import header only but when I went to saved imports and tried to append data it asked if I wanted to overwrite existing file etc. then a series of error messages then I gave up.
I don't wish to continue with this approach as I've spent two days trying all kinds of things and cannot afford to spend another minute on it.
Life is too short. I wish to thank you all for your generous support, and will probably stick to Excel on this occasion.
Ian
retired
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION