Occasionally my MS Access reports:
The search key was not found in any record
After this happens the solution is to close Access, compact and repair the backend and then delete the record.
What causes this and how can I avoid it?
This question is related to
ms-access
These are the steps which i follows may be it is useful for you,
Go to menu-tools-database utilities-compact and repair database.
when repairing database delete or update that record.
it is working finely.
This may be a rookie mistake on my part, but it still caused the error message. I was importing an excel spreadsheet and had a space in front of a field heading. Once the space was removed the file imported no problem
Note: The space only appears when you look at the file in Excel; when Access tries to import, the dialog box gets rid of the space, but the space still causes problems. I learned this the hard way...
Also check the database version. I was having the problem with VBA CreateDatabase(sTempDBName, dbLangGeneral) in Access 2010 where I was using a 2003 database trying to link a table in a 2010 database. When I manually tried the link I got a message about no support for linking to a later version. Creating the temp database I was trying to link to using the option dbVersion40 "CreateDatabase(sTempDBName, dbLangGeneral, dbVersion40)" cured it.
These are the steps which i follows may be it is useful for you,
Go to menu-tools-database utilities-compact and repair database.
when repairing database delete or update that record.
it is working finely.
Following on from @Wilf's answer, I was trying to import a spreadsheet which had spaces in one of the headings, which I eliminated. I checked for leading and trailing spaces, but still had the same problem - until I used Ctrl-Right from the last real heading cell, and found another cell on the first row that looked blank but obviously contained some whitespace. After deleting this, my import works. Thanks for the pointers :)
Its an issue with one of your headers in Excel. I get this when copy pasting from other sources. Fix your headers and it should resolve the issue.
Also check the database version. I was having the problem with VBA CreateDatabase(sTempDBName, dbLangGeneral) in Access 2010 where I was using a 2003 database trying to link a table in a 2010 database. When I manually tried the link I got a message about no support for linking to a later version. Creating the temp database I was trying to link to using the option dbVersion40 "CreateDatabase(sTempDBName, dbLangGeneral, dbVersion40)" cured it.
In Access 2007 this error occurs when importing an Excel file where there are two fields with the same column header.
Following on from @Wilf's answer, I was trying to import a spreadsheet which had spaces in one of the headings, which I eliminated. I checked for leading and trailing spaces, but still had the same problem - until I used Ctrl-Right from the last real heading cell, and found another cell on the first row that looked blank but obviously contained some whitespace. After deleting this, my import works. Thanks for the pointers :)
I found a space in one of the header (titles) on the Excel sheet. Once I removed the space before the name, it went smoothly.
Another possible cause of this error is a mismatched workgroup file. That is, if you try to use a secured (or partially-secured) MDB with a workgroup file other than the one used to secure it, you can trigger the error (I've seen it myself, years ago with Access 2000).
Any spaces in the names of the columns in Excel caused the error for me. Once I removed any spaces then it imported with no problems.
The problem for me was a space BEFORE one of the column headers. Once i fixed that, no more problems
I also got the message "The search key was not found in any record". When I moved my database from the network drive to my desktop, everything ran smoothly and faster. I didn't get the same error again.
Another potential cause for this error is Sandbox Mode, which prevents MS Access from running certain statements that are considered unsafe. This can be disabled by setting the following registry key...
HKLM\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines
SandboxMode (DWORD Value)
...to either 0 or 2:
SETTING DESCRIPTION
0 Sandbox mode is disabled at all times.
1 Sandbox mode is used for Access, but not for non-Access programs.
2 Sandbox mode is used for non-Access programs, but not for Access.
3 Sandbox mode is used at all times. This is the default value.
I know this is a very old post but as I was searching for additional solutions to this same error while running my command (I'd previously encountered the spaces in the Excel wb headers and remedied it with VBA each time the file is updated so I knew it wasn't that). I considered the fact that the xlsm file and DB were on separate network drives but didn't want to explore moving one unless it was my last resort.
I attempted to run the save import manually and there it was right in front of my face. The folder containing the xlsm file had been renamed....I changed the name back to match my saved import and....smh, it was that all along.
Another possible cause of this error is a mismatched workgroup file. That is, if you try to use a secured (or partially-secured) MDB with a workgroup file other than the one used to secure it, you can trigger the error (I've seen it myself, years ago with Access 2000).
You do not mention the version of Access that you are using. Microsoft reports a bug in 2000:
BUG: You receive a "The search key was not found in any record" error message when you compact a database or save design changes in Access 2000http://support.microsoft.com/kb/301474
If this is not your problem, here is a pretty comprehensive FAQ by Tony Toews, Microsoft Access MVP:
Corrupt Microsoft Access MDBs FAQhttp://www.granite.ab.ca/access/corruptmdbs.htm
If the problem is constantly occuring, you need to find the reason for the corruption of your table, and you will find a number of suggestions for tracking the cause in the site link above.
Yep, I'm with user2315734... Had the same issue "The search key was not found in any record", where the Access db was on a local drive, but the Excel file I was importing to it was on a network drive; after trying most of above suggestions, finally resolved it by just moving the Excel file to the local drive, too.
Thanks all.
I found a space in one of the header (titles) on the Excel sheet. Once I removed the space before the name, it went smoothly.
Another potential cause for this error is Sandbox Mode, which prevents MS Access from running certain statements that are considered unsafe. This can be disabled by setting the following registry key...
HKLM\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines
SandboxMode (DWORD Value)
...to either 0 or 2:
SETTING DESCRIPTION
0 Sandbox mode is disabled at all times.
1 Sandbox mode is used for Access, but not for non-Access programs.
2 Sandbox mode is used for non-Access programs, but not for Access.
3 Sandbox mode is used at all times. This is the default value.
Thew problem is because of spaces in the titles(Headers). Remove spaces in all headers and it works fine.
I know this is a very old post but as I was searching for additional solutions to this same error while running my command (I'd previously encountered the spaces in the Excel wb headers and remedied it with VBA each time the file is updated so I knew it wasn't that). I considered the fact that the xlsm file and DB were on separate network drives but didn't want to explore moving one unless it was my last resort.
I attempted to run the save import manually and there it was right in front of my face. The folder containing the xlsm file had been renamed....I changed the name back to match my saved import and....smh, it was that all along.
Another possible cause of this error is a mismatched workgroup file. That is, if you try to use a secured (or partially-secured) MDB with a workgroup file other than the one used to secure it, you can trigger the error (I've seen it myself, years ago with Access 2000).
Any spaces in the names of the columns in Excel caused the error for me. Once I removed any spaces then it imported with no problems.
You do not mention the version of Access that you are using. Microsoft reports a bug in 2000:
BUG: You receive a "The search key was not found in any record" error message when you compact a database or save design changes in Access 2000http://support.microsoft.com/kb/301474
If this is not your problem, here is a pretty comprehensive FAQ by Tony Toews, Microsoft Access MVP:
Corrupt Microsoft Access MDBs FAQhttp://www.granite.ab.ca/access/corruptmdbs.htm
If the problem is constantly occuring, you need to find the reason for the corruption of your table, and you will find a number of suggestions for tracking the cause in the site link above.
Yep, I'm with user2315734... Had the same issue "The search key was not found in any record", where the Access db was on a local drive, but the Excel file I was importing to it was on a network drive; after trying most of above suggestions, finally resolved it by just moving the Excel file to the local drive, too.
Thanks all.
In Access 2007 this error occurs when importing an Excel file where there are two fields with the same column header.
You do not mention the version of Access that you are using. Microsoft reports a bug in 2000:
BUG: You receive a "The search key was not found in any record" error message when you compact a database or save design changes in Access 2000http://support.microsoft.com/kb/301474
If this is not your problem, here is a pretty comprehensive FAQ by Tony Toews, Microsoft Access MVP:
Corrupt Microsoft Access MDBs FAQhttp://www.granite.ab.ca/access/corruptmdbs.htm
If the problem is constantly occuring, you need to find the reason for the corruption of your table, and you will find a number of suggestions for tracking the cause in the site link above.
The problem for me was a space BEFORE one of the column headers. Once i fixed that, no more problems
I also got the message "The search key was not found in any record". When I moved my database from the network drive to my desktop, everything ran smoothly and faster. I didn't get the same error again.
Its an issue with one of your headers in Excel. I get this when copy pasting from other sources. Fix your headers and it should resolve the issue.
Thew problem is because of spaces in the titles(Headers). Remove spaces in all headers and it works fine.
This may be a rookie mistake on my part, but it still caused the error message. I was importing an excel spreadsheet and had a space in front of a field heading. Once the space was removed the file imported no problem
Note: The space only appears when you look at the file in Excel; when Access tries to import, the dialog box gets rid of the space, but the space still causes problems. I learned this the hard way...
You do not mention the version of Access that you are using. Microsoft reports a bug in 2000:
BUG: You receive a "The search key was not found in any record" error message when you compact a database or save design changes in Access 2000http://support.microsoft.com/kb/301474
If this is not your problem, here is a pretty comprehensive FAQ by Tony Toews, Microsoft Access MVP:
Corrupt Microsoft Access MDBs FAQhttp://www.granite.ab.ca/access/corruptmdbs.htm
If the problem is constantly occuring, you need to find the reason for the corruption of your table, and you will find a number of suggestions for tracking the cause in the site link above.
Source: Stackoverflow.com