Search Key Not Found?

[Randy Rich]

I've been trying to import an excel spreadsheet (very wide) and I keep
getting a message that "The search key was not found in any record". The
information in the sheet is about 225 columns wide. I've tried to import it
and using the wizard I've skipped all the rows but 10-12, and still get the
same error message. The Help file does not seem to address it.
The sheet itsel is 290 columns wide, and I've established named ranges that
should fit the 255 column design restriction, but still no luck.
Can anybody help me understand what is really happening with the "Search Key"?

 

[Jeff Boyce]

Randy

Not sure about the "search key" message, but...

Have you tried using File | Get External ... | Link ...
instead of importing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Randy Rich]

Links do work, but I need to update information, and as luck would have it,
you can't update linked spreadsheets. I really need to pull in the data into
internal tables. And since this is Access 2007, there isn't a "File" drop
down list anymore. Thanks for looking into this Jeff. If you've got any
other insights, I'm sure interested! I'm stumped.

 

[Jeff Boyce]

Randy

I'm probably a 'geezer' -- I didn't realize you were working in Access '07.
You still can link tables in '07, but the new pathway through the Ribbon
does not match the old sequence.

It might be a total kludge, but if you first linked to the Excel data, then
created a make-table query against that linked Excel data, can you get a
"local" copy in Access?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Randy Rich]

Great idea about the making a local copy! However, if I link to the
spreadsheet, and then export the file as a renamed spreadsheet, when I try to
import the new and renamed spreadsheet into a table, it comes in, but it is
just another linked file, again without the ability to edit.

I've kind of worked through the issue this mornig. I got error messages
telling me that my MaxLocksPerFile registry entry should go higher. I reset
it from 9000 to 9900. Still no luck. I tried again and uped it to 50000. I
also set up named ranges in my source spreadsheet, one name for each 26
alphabet group. Before the registry change it would just hang, and give me a
message that my maxlocksperfile was too low. I'd have an imported file, kind
of, but the unique key autonumber field wouldn't be there. After it was
raised, it would run to completion successfully. I then built a query
connecting them all, and I'm working with that. But I still don't know any
more about the Search Key Not Found error - that I still get if I try to do a
normal import. Thanks again so much for your interest, concern, and Ideas.
It is a puzzle for everyone I ask. Regards, Randy