MS-access reports - The search key was not found in any record - on save

M

16

22

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?

Mccartan answered 15/10, 2008 at 22:18 Comment(0)

B

20

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...

Breastbeating answered 20/6, 2011 at 21:45 Comment(3)

good suggestion. any spaces in field names causes me problems. – Personable 21/2, 2018 at 22:2

I had already removed the spaces in the middle of the headers, but removing both leading and trailing spaces fixed the issue for me – Realtor 1/1, 2020 at 21:8

I followed this pointer and found an extra twist: 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 :) – Assizes 10/6, 2022 at 5:10

M

7

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 2000

http://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 FAQ

http://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.

Mollie answered 16/10, 2008 at 8:27 Comment(0)

E

6

The problem for me was a space BEFORE one of the column headers. Once i fixed that, no more problems

Embolectomy answered 3/12, 2013 at 1:21 Comment(0)

S

4

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.

Suneya answered 29/10, 2012 at 14:31 Comment(0)

V

3

Thew problem is because of spaces in the titles(Headers). Remove spaces in all headers and it works fine.

Versicolor answered 25/3, 2014 at 17:23 Comment(0)

E

1

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.

Epp answered 24/11, 2012 at 4:2 Comment(0)

A

1

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.

Anthracosilicosis answered 13/11, 2013 at 15:49 Comment(0)

A

1

Another possibility is hitting the file size limit.

I just got this old chestnut again. I tried the usual removing any spaces from fieldnames, but no joy. Finally, I believe it was because I was importing a seriously large CSV file, which caused the Access .accdb file to hit the 2GB file size limit. (I actually tried importing the CSV file twice without deleting the old version - after I deleted the first version, the second import went without errors.)

Assizes answered 17/8, 2020 at 3:54 Comment(0)

C

0

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).

Counterforce answered 19/10, 2008 at 15:54 Comment(0)

F

0

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.

Fowl answered 12/5, 2009 at 7:56 Comment(0)

T

0

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.

Throwaway answered 24/4, 2013 at 13:24 Comment(0)

M

0

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.

Matthus answered 14/9, 2013 at 15:32 Comment(0)

K

0

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.

Kilowatt answered 31/10, 2013 at 17:10 Comment(0)

R

0

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.

Rawdon answered 18/2, 2014 at 14:41 Comment(0)

C

0

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.

Craftwork answered 3/10, 2019 at 12:30 Comment(0)

D

-1

In Access 2007 this error occurs when importing an Excel file where there are two fields with the same column header.

Dancer answered 26/8, 2010 at 14:17 Comment(1)

Er, no, it doesn't happen in that circumstance -- I just tested (with the wizard and with TransferSpreadsheet) and it imports it just like it did in earlier versions of Access, providing a unique name for the duplicate column. It may be that this error also happens sometimes when importing, but it's independent of having duplicate column headers in the source spreadsheet. – Counterforce 27/8, 2010 at 2:59

Recommended topics

Hot tags