When working with Microsoft Access databases, you may encounter the dreaded warning message “The search key was not found in any record.” This hiccup can interrupt the normal functioning of your database, leading to severe data loss. In this optimal blog, we will explore the intricacies of this error, know its origins, and how to fix this run-time error ‘3709’ and resume your work. Here’s the real interface of this error:
So, let’s begin…
Free MS Access Database Repair Tool
Repair corrupt MDB and ACCDB database files and recover deleted database tables, queries, indexes and records easily. Try Now!
By clicking the button above and installing Stellar Repair for Access (14.8 MB), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.
What Is the Error Search Key Was Not Found in Access?
The runtime error 3709 the search key was not found occurs when performing queries or trying to access specific records in MS Access. This error is a notification that specifies a failure in locating the specified search key within the database.
Well, there are some other circumstances as well in which you can get this error. They are as follows:
- When you enter a large amount of text in a Memo field that contains an index in Access.
- When you compact and repair the Access database or save design changes in the Access.
- Trying to import Excel spreadsheet to Access Database table.
Reasons for Access Run-Time Error 3709 The Search Key Was Not Found
Here are the major causes of this error:
- Inconsistent indexing- Inappropriately indexed databases might contribute to the 3709 error.
- Data Corruption- The data corruption is the most common cause behind this error.
- Faulty Queries & Search Parameters- Inaccurately configured queries or search parameters can generate the error.
You may also like: Best Fixes To Resolve Access 3021-No Current Record Error…!
How To Fix MS Access “The Search Key Was Not Found In Any Record” Occurs In Different Circumstances?
Check Out Some Basic Tips First:
- Check out if there are .inf files in the contact set folder. As the presence of these files indicates that someone has used Access to work with these files and built indexes that now no longer exist. .inf files can be deleted safely, in order to resolve this issue.
- You can also check out whether it’s a case of your Access Database corruption. As Microsoft access has an in-built utility for detecting and fixing corrupt dBase files.
Here we are going to discuss the situations when this search key not found in any record error occurs and to get rid of it.
- Case 1- When You Don’t Know the Exact Reason For Getting this Error In Access 2010/2013/2016
- Case 2- When You Enter a Large Amount of Text in a Memo Field That Contains An Index in Access
- Case 3- When You Compact a Database or Save Design Changes
So, let’s discuss them one by one in brief.
Case 1- When You Don’t Know the Exact Reason For Getting this Error In Access 2010/2013/2016
When you don’t know the exact reason for getting run time error 3709 Access, you can obtain the latest jet 4.0 service packs to resolve it. For more information, on how to download the latest Jet 4.0 service pack, tap on the following article in the Microsoft Knowledge Base:
How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine
Case 2- When You Enter a Large Amount of Text in a Memo Field That Contains An Index in Access
Symptoms
In the Access database when you create a Memo field that contains an index or you create a field name containing automatically indexed text then in that case you can’t enter the text that includes more than approximately 3450 characters into the field.
In such a situation when you try to run the existing data, you will receive the following error message.
Run-time error ‘3709’:
The search key not found in any record.
Resolution
To resolve this issue, just follow these steps:
- First of all, you need to open the table in Design view with the Memo field.
- From the View menu, click Indexes. Note:
In MS Access 2007, click the design tab and then click Indexes in the Show/Hide group.
- Tap to the index for the memo field and then delete it.
Also Read: Access Error 2105: You Can’t Go to The Specified Record
Case 3- When You Compact a Database or Save Design Changes
Symptoms
This error is frequently encountered while trying to save design changes to a new object or when you try to compact a database.
You may find that the database object gets listed in the database window, even though the saving process gets failed. So when you try to open the object in the Access database, you will get this error.
Resolution
Make sure the object exists and that you spell its name and path name correctly. Besides, you can run Compact and Repair to fix this error.
How to Search For Records In Access When Search Key is missing?
Method 1- Filter to Display Specific Records
You can apply a filter to see only a specific number of records that satisfy specific criteria and comparison operators. Such as to narrow down the record search, you just need to right-click on the field of which data you want to match. After then from the shortcut menu choose the Contains, Equals, Does Not Contain, or Does Not Equal.
You have the option to turn this filter option on/off to make an easy switch between filtered and unfiltered views of the same data. Unlike the search option, the filter only shows a limited number of records.
- To apply filter options based on selection, you need to open up your table or form first.
- Check whether the table or form is already been filtered or not. Go to the Home tab, and then in the Sort & Filter group, tap to the Advanced If your table or form has already been filtered previously then you will get the Clear All Filters option. So tap on it.
- Search for the record that contains a value that you need to use for filter search. After then tap inside the control (in Form, Report, or Layout view) or column (in Datasheet view).
As per the partial selection for the filter you just need to select the characters that you need.
- Go to the Home tab then tap on the Sort & Filter group and choose Selection Then choose the filter that you need to apply.
- For filtering out other fields as per the selection, you need to just repeat from step no. 3 and 4.
Method 2- Search Record Using Docmd.Findrecord Method In Access
The FindRecord method performs FindRecord action in Access Visual Basic.
Syntax:
expression.FindRecord (FindWhat, Match, MatchCase, Search, SearchAsFormatted, OnlyCurrentField, FindFirst)
expression A variable that represents a DoCmd object.
Remarks
When the procedure calls for the FindRecord method then Access browses for the specific data into the records. Well, the search order is arranged by making adjustments in the search argument. When the MS Access application fetches that specific data then the value is selected for the record.
This FindRecord method doesn’t return any value to indicate the failure or success of the task. For identifying whether the value is present in the recordset or not make use of the FindNext, FindPrevious, or FindFirst, FindLast method of Recordset object.
Using this method you can assign the value of NoMatch property to True if in case you are unable to find the specified value.
Example
Here is the following example to get the first encounter in the records having the name Smith.
VBCopy
DoCmd.FindRecord “Smith”,, True,, True
Related FAQs:
What Is a Search Key in Access?
A Search Key in Access is a ‘custom’ string field that is added to the database table.
Is There a Specific Error Code Associated with The Search Key Was Not Found in Any Record Access Import Error?
Yes, 3709 is a specific error code associated with the message.
Can Data Corruption Permanently Erase the Search Key?
Partial data corruption may not erase the search key but severe data corruption can potentially lead to permanent loss.
Can You Provide an Example of a Search Key Causing The “Not Found in Any Record” Error During Access Import?
Certainly, an example could be specifying a unique ID as the search key, but the dataset lacks this ID, leading to the error. Confirm that the search key you’re using exists in the source data for a successful import.
Also Read: Access Can’t Append All The Records In The Append Query Error
Final Thoughts
So, here comes the end part and I just want to say that in this blog, I have discussed several ways to resolve the search key was not found in any record import Excel to Access error. I hope the given info will surely help you to deal with the problem effortlessly.
References:

This software repairs & restores all ACCDB/MDB objects including tables, reports, queries, records, forms, and indexes along with modules, macros, and other stuffs effectively.
- Download Stellar Repair for Access rated Great on Cnet (download starts on this page).
- Click Browse and Search option to locate corrupt Access database.
- Click Repair button to repair & preview the database objects.