Access 2013 VBA DoCMD Transfer spreadsheet error 3709 Search Key not found...

[RCBricker]

Hi all.

I have some code for a import of an Excel file. The code uses variables for the strings for table name, file path, and range. (code below).

This code works for all of our clients except one AND for that one it works for the first 6 monthly reports. None of the last 6 monthly reports will import without throwing the Runtime error 3709 'The Search Key was not found in any Record'.

I check the field headers and there are no special characters used in the headers except the underscore (_) and the single quote ('). These are both found in the first 6 monthly reports for this client.

I also checked and there are no leading or trailing spaces in the field headers.

I am about at my wits end over this.

   Set ws = ActiveSheet
    Set db = CurrentDb

    strRNG = strSNAME & strRNG
    DoCmd.TransferSpreadsheet _
    acImport, _
    acSpreadsheetTypeExcel12, _
    strTBLNAME, _
    strFILENAME, _
    True, _
    strRNG

This is the code without the variables - still throws error

    DoCmd.TransferSpreadsheet _
    acImport, _
    acSpreadsheetTypeExcel12, _
    "TEMP_TBL_GFEBS_JUN_2015", _
    "G:\Financial Reconciliations\Data Files\ERP ARMY_GFEBS\4.1 CBDP " _
    & "- GFEBS GL Data\GFEBS_FY15_09_097_CBDP_P.xlsx", _
    False, _
    "GFEBS_FY15_JUN_097_CBDP!A1:FN37085"

Any ideas?

 

[xenou]

Have you tried with the ' character removed? I would expect problems with that (not valid in a field name, so far as I know). Even if it is valid, it still might be a problem in some situations so definitely unwelcome as part of a field name.

 

[RCBricker]

Have you tried with the ' character removed? I would expect problems with that (not valid in a field name, so far as I know). Even if it is valid, it still might be a problem in some situations so definitely unwelcome as part of a field name.

I haven't removed it (yet) as it has not been a problem with the other reports. All of them carry the single quote at the start of all field headers. Only these last six reports are erroring out.

 

[xenou]

Ah, a single quote at the start is a text marker in Excel, so that is probably no a problem. I didn't realize that's what you meant.

Any specific reason why you suspect the field headers are the problem?

 

[Oriel Tzvi Shaer]

I had the same error.
In my case, the error was that one of field headers started with the Space character.
I hope this helps