The search key was not found in any record

[fscafidi]

I had the same issue, what I found was: the Excel spreadsheet that I was trying to import had columns with a leading space. This causes MS Access a lot of problems because MS Access doesn't allow for leading spaces. Once I removed the leading spaces from the couple of fields, the import worked fine.

[RuralGuy]

Thanks for sharing.

[Bosman]

I have also tried several of the suggested solutions (none worked) and then read this suggestion with disbelief - but it worked for me! Quit Access and restart!

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!

[jatilsharma]

Thanks All for valuable inputs!

[evhfan2000]

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.

THANKS FOR THIS SUGGESTION! My file size was a hair over 2 GB. Created new file from zero, import worked perfectly. I spent well over an hour trying everything under the sun until GOOGLE brought me here.

Thanks everyone, learned a little bit from all the posts here today.

[tgnana]

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!

Figure out whether there's any hidden columns or empty columns in the spreadsheet. if present either remove/ populate with a heading and at least one data item. this worked for me.

[matrixdev]

I removed the leading spaces in first row field names- problem solved.

[patjivan]

removing header row solved problem!

[jnaastad]

Not wanting to lose my field names I was finally able to narrow this down to a column name that had 'id' in it. Changed my Excel, exited and re-opened Access and no more problem.
Note that 'id' name did cause Access import to assign it as an index. Even changing the field properties during the import did not disconnect the association. ergo, exit and reopen Access cleared that glitch.

[AccessMasterFromTheStart]

Alright Ladies and Gents,
The solution or work-around is at the end

This has been tested and is consistent in:
Win7-64Bit with 32 bit MS Access 2010
Win7-64Bit with 64 bit MS Access 2010
Win Vista-32Bit with 32 MS Access 2000

I have the problem of the problem narrowed down to a simple text file.
I am an Access Master and I cannot overcome the “The Search Key Was Not Found In Any Record” Error at times.

Here is what happens:
I Copy text from a notepad text file (Highlight, copy then paste) Usually detailed code for my person programming code database. Then I paste the text into an Access Memo Field then try to delete or change it. This is when I get the “The Search Key Was Not Found in Any Record” error. This would occur repeatedly with certain files.

Please understand that I do not mind changing things in my text so that this does not occur. However, changing my text has no bearing on why this curious problem occurs to begin with. So to further explore the issue I proceeded to eliminate everything until I had the file down to the absolute minimum and simplicity while still receiving the error. In essence I was trying to keep it broken.

I then simplified text and I replaced all of the characters with an (x) to make sure the error was not something to do with code within. It consists of several lines of text with a standard windows [carriage return] or [Enter Key] after each line. The entire file is made up of only the letter “x” or any other letter repeated and carriage returns “vbCrlf”.

Upon writing this pos,t I tested it in a fresh database to confirm my findings. The new data base had only one table with one field. I found that the record could be deleted. Then I proceeded to compare the newly created Memo field of the new database’s new table to the original by importing the original table into the new virgin MS Access database.

The problem consistently occurred in my old table but not in the new table. At that point I deleted all of the fields in the old table until I had only the Memo field left. Still the problems persisted. After examining the actual field properties in the “design view” of the table I found that the Memo field in the old table was indexed but it was not indexed in the new table.

I am unsure as to how I ended up with the Memo field being indexed??? Anyway, when I changed the Memo field in the old table from [Indexed]“Yes (Duplicates OK)” to “No” I was then able to change and or delete the Memo Field Data. Further when I set the new table’s Memo field Indexed from “No” to “Yes (Duplicates OK)” it too would not allow changes or deletion.

Understandably we often do not want to index memo fields. However, this work around does not explain why the curious pattern of characters breaks MS Access.

In the end for those who only need to get the project done make sure that your Memo field is not indexed. (Indexed = “No”) is where it needs to be. Upon further testing it seems that if you change the Indexed property at all after this problem occurs it will then allow you to delete or alter the record.

If anyone from Microsoft would like a copy of the text file that can produce a repeatable outcome of this very curious error please contact me.

[mike.fuller]

I located some leading spaces on the header words of the excel file i was attempting to import. after i removed the leading spaces, the errors stopped.

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![/QUOTE]

[Cryptic]

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.

That was my problem. Was appending a spreadsheet into a table. I got an error that the DB could be corrupted--ran repair, etc., no dice. When I tried to import the table by itself, I got the search key error. Checked my headers--I had imported previous quarters without a problem. (I've tried splitting the DB, but the problem is with the size of the tables. ) I think, however, we can pare down the number of columns we really need and that will help. (These are spreadsheets of ~100MB to start with, so it doesn't take long to get to 2GB!)

It's been a while since I've used access so the reminder about the size limitation is a good one!

[kthomas]

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!

I found that Access was trying to import a column that didn't have any data, or so I though. However, it turned out that the 1st row in this column had a single space. The clue was when I selected the top row as headers, I noticed the last column that it was trying to import didn't have a header name, and gave me the generic warning about the header names. Access could not give the column the header name of "Field 1" since it had a blank. When I imported without headers this is when I found the blank in the last column first row.

[pacittk]

I am using Windows 7 32 bit and Access 2010 and I get the same message, The search key was not found in any record Anyone know why????

[pacittk]

Any way around this???