Search key not found

  • Thread starter Thread starter LAF819
  • Start date Start date
L

LAF819

I am trying to import excel data to access. I have done extensive clean up
in excel including TRIM of all records, deletion of inconsistent data types,
and saving to excel 2007. However, when using the import excel file function
in Access 2007 I continue to get the message "Search key not found in any
record."

I don't know what this means or what else to attempt at this point -- any
suggestions?
 
Can you provide a little more information about what you are doing prior to
and during the import. For example:
Are you attempting to import into an existing table?
Does the table have any relationships set?
Are you deleting all data from an existing table before importing?
If importing into an existing table, what indexing does it have?
or are you creating a new table with the import and if so what option are
you selecting for the primary key.

I Googled this just for interest and cyberspace is full of the problem but I
didn't find any real solution. However, I did see where someone had an index
on a memo field and when the index was removed, the problem went away so it
could be something as simple as a corrupt index.

Anyway I would try creating a new table during the import and then open the
table in Design mode and reset all the properties.
 
Thank you OssieMac!

When going back to re-check my excel table, I selected format cells,
protection and unchecked the locked option (it was checked locked - I assume
as the default). Then when in Access import, I selected "no primary key" and
it worked!

My import was always set to create a new table.

For anyone else with this problem here are the steps I took:
in excel --
1. TRIM on all data fields - did this by inserting an extra column running
the TRIM function then cutting and pasting values from the solution column to
the original location (time consuming, but worth the effort)
2. make sure all data is in the correctly formatted fields, ie text,
numbers, dates, etc.
3. Fixed heading names to take out slashes.
4. Format cells, protection, uncheck locked

In access:
1. From external data, select import excel file, go through the sequence and
when it asks for primary key say "no primary key." I intend to go to table
design view from to establish a primary key now that the data is in.

Hope this helps others -- it used to be much easier to import from excel,
but I'm glad to know its not impossible!
 
Back
Top