The search key was not found in any record

[GGroman]

I was also receiving this error message, with the following criteria confirmed: 1) add'l spaces in front of column headers/names were removed; 2) columns were all formatted to text; 3) file was in .txt format, or in .csv format (tried both).

The culprit was 1 field (column) contained 1,676 characters. I changed the column size to 250 or fewer (fixed position import) and it worked. Hope this helps.

Hey all,

I'm getting the same error when importing a spreadsheet from Excel into Access 2007. I tried changing all the fields to text and making sure there are no spaces in the field names/ column headers. No luck!



Any suggestions?

Gracias!

[accessnewbie]

~ Sorry, didn't see that changing the data type had already been attempted! ~

Hello! First off...love this forum!!! It has saved me many times!!

I just ran into this error. I only have 1 column in my Excel spreadsheet. The header (cell A1) is "ID". Then there is a list of 37 9-digit numbers. I have a form that I input the file name in a text box and then click a button to import the file. Here's that code:

strHoldPath = InputBox("Where is your Excel spreadsheet saved?", "Import Excel Spreadsheet", "default\path\goes\here")

strFileName = strHoldPath & "\" & nameoffile & ".xls"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportTable", strFileName, True

I received the "...search key..." error. I didn't have any spaces anywhere in the file. I changed the data type of this column to Text and was then able to import it successfully. Go figure!!

Thanks!
mw

[heyuser]

I just want to say thanks for this informative thread! Only one space before a field heading was causing all the grief. Thanks to all of you, I didn't waste days on this problem - only an hour or so.

[jsantora]

results of an experiment:
First, I did a successful import from Excel to a new table in Access. Then I made one small change to the excel file and tried the import again.

It failed with the strange "the search key was not found in any record" error message.

All I did was add a space to the left of one of the column headers - changing "System" to " System". Depending on the font and size and upper/lower case of some of the first letters of your column headers, it might be really hard to see the space on the left.

hope this helps someone.
john

[RuralGuy]

Thanks for the tip John.

[AvrohomE]

I had the same obscure error. In my case the problem was that I had coded a 300 character column as text. Once I changed it to memo, the import worked. It seems that a variety of errors generate the same meaningless message 'the search key was not found in any record'.

[rjrcooper]

Hi all!

I just joined the forum cos Google returned this post as one of the top results for the error I was getting but now it's fixed.

I was trying to import a text file into Access and was getting the "the search key was not found in any record" message when I clicked "Finish" at the end of the wizard.

I imported the file into Excel and tried to import that but got the same message. I then went and did some analysis on the Excel file. Probalby not that relevant to the final solution but here are the details:
- There are 5 columns of data
- There are 73,099 rows
- There is no field/cell that is greater than 250 characters in length
- I am importing all columns into "Text" columns in Access
- The excel spreadsheet has a header row but no title has a space character at the beginning (the same message appeared even when removing the header row and accepting the default Access column names)
- It didn't matter whether I let Access create the PK or I ask it for no PK, the same message appeared.

How did I fix it? Quit Access and re-start! Import worked like a charm.

I had been importing large txt files all day so it must have got its knickers in a twist and just needed a break.

Hope this helps someone else out there....

Cheers!

[RuralGuy]

Thanks for sharing!

[sepple]

I was not able to import a text file because I was getting the search key error.

I just found out that the database cannot be larger than 2 GB. I was trying to import the data into a file that was already 1.99GB!

I created a new database and the file imported with no problems! Hope this helps others.

[LeeDAtkinson]

Thanks for sharing everyone. My problem was the overall databae size issue which then turned into "record too large" due to too many columns and 70k rows. I imported in 2 parts and it worked.
Thanks!

[dford]

Same issue, tried removing spaces and converting all columns to text didn't fix it.

Ran a =TRIM( ) function for all my headers
Closed Excel, Closed Access,
Reopened Access
File imported fine.

Hope this helps somebody.

[Crazy_QA]

Wasted more than 30 minutes on this!
I tried everything that was in the post - changed the whole spreadsheet to text and even re-typed all the headings to make sure I didn't have any spaces - nothing worked! Finally as mick_curtis suggested above, I deleted the heading row altogether and then it worked!!! Thanks guys!!!

[dcbk]

Thanks for the solution, was driving me crazy trying to figure out what was wrong, especially since it the spreadsheet I was trying to import had been exported from the same database.

[mabs239]

Had the same problem. There was one column in excel that had one space entry in header row. Renaming it solved the problem for me.

Thanks for all the participants here.

[avingavin]

Hi folks

I found that relocating the file to a folder without putting any punctuation in the folder name ie ' " ; : sorted the import issue. I always import as text fields, tab delimited generally saves a lot of time and hassle but this error really stumped me. I had to use Access 2007 for a different import error message. Hope this helps.