Hi,

I have a problem with and excel 2007 import into Access 2007. I have setup a saved import in Access named Import_Weekly_Terms which appends records to a table in the database from an Excel file named Weekly_Terms.

When I set up the savedimport it runs fine the first time. Then I put another Weekly_Terms file in place of the first one, same columns in spreadsheet, same directory and so forth.

When I run click the button I get Runtime Error 3709. "The search key was not found in any record."

here's the code behind the command button which executes on click:

Dim Msg, Style, Title, Response, CheckInit 'Declare variables.
Msg = "Do you want to run the Weekly Terms Import?" 'Message.
Style = vbYesNo + vbExclamation + vbDefaultButton2 'Define buttons.
Title = "Warning!" 'Define title of dialogue box.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from tblWeekly_Terms"
DoCmd.RunSQL "Delete * from tblWT_Temp"
DoCmd.RunSavedImportExport "Import_Weekly_Terms" (debug takes me here)
ElseIf Response = vbNo Then
DoCmd.CloseDatabase
End If

Any thoughts? Thanks, bph

Man O Man, I'm a knucklehead!!

After 9 hours of banging my head off the wall, I found the problem. The excel file that is created and sent to us had the correct filename, but a different worksheet name than what we had set up the saved import with.

So, instead of 'Sheet1', the name of the worksheet was 'terms_1-212010'. When the savedimport looked for Sheet1, I got no search key found error. Geez!

Hope this helps someone someday.

cheers, bph