Error 3709 the search key was not found. Help with this message

[colewillm]

Hello,

Let me start off saying that I'm not working off vba code that I wrote myself, so I don't fully understand the context or purpose of all of the details.

I'm basically running a data import macro that runs some inputs through an excel model and then imports the output through an excel sheet into access.

I think I'm able to get through to DoCmd.RunSavedImportExport "np_CoverageDetail" since I am able to successfully input the table, but the code does not add an account_ID number like it's supposed to do after.

I will also post the code for the two export processes that are running. One is to collect the data the next is to clean it for importing into access. I know the 'exportdata' function runs correctly for both 'np_coveragedetail' and variables because I break the code in the middle and it has created the correct table, but the second part 'exportdatacleanup' seems to only work for the 'np_coveragedetail' tab as it correctly imports this table, but not the 'variables' tab.

The code breaks after it finishes importing the 'np_coveragedetail' table and gives the error message above. Error 3709 the search key was not found. I don't know how to interpret this message and it seems it's not clear cut from google searching it.

Sub fileImportProcess()

On Error GoTo trap


    'Dim ex As Excel.Application
    Dim wb1, wb2 As Excel.Workbook
    Dim mwb As String
    Dim fDialog As FileDialog
    Dim wbName As String
    Dim rs As Recordset
    Dim db As Database
    
    Dim accountID As Integer
    Dim acctName, polPer, uw, fName, fModDate, fPath As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select MAX(account_ID) FROM tblAccountDetails")
    accountID = rs.Fields(0).Value + 1
    Set rs = Nothing
    
    'open excel application and mainworkbook
    
    mwb = CurrentProject.Path & "\MainWorkbook.xlsb"
    Set ex = New Excel.Application
    ex.Visible = True
    ex.Workbooks.Open mwb, False
    Set wb1 = ex.ActiveWorkbook
    
    
    Set rs = db.OpenRecordset("qryTotalFilesToBeImported")
    rs.MoveFirst
    
    Do Until rs.EOF
    '    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    '    With fDialog
    '        .AllowMultiSelect = False
    '        .Title = "Please select the file to be processed"
    '        .Filters.Clear
    '        .Filters.Add "Microsoft Excel Binary File", "*.xlsb"
    '        If .Show = True Then
    '            wbName = fDialog.SelectedItems(1)
    '        Else
    '            Exit Sub
    '        End If
    '    End With
        
        Debug.Print rs.Fields("path").Value
        wbName = rs.Fields("path").Value
        
        If Len(wbName) = 0 Then
            Exit Sub
        End If
        
        ex.Workbooks.Open wbName, False
        Set wb2 = ex.ActiveWorkbook
        wb2.Sheets("Exposure - GL").Activate
        
        acctName = ex.Range("D4").Value2
        
        If acctName = "" Then
            wb2.Close False
            Set wb2 = Nothing
            MsgBox "The account name cell is empty in the selected file " & _
            vbCrLf & _
            "Please note this as an error in the table filesNotProcessedError" & _
            vbCrLf & vbCrLf & wbName, vbCritical
            Exit Sub
        End If
        
        polPer = ex.Range("D5").Value2
        uw = ex.Range("D6").Value2
        fName = ex.ActiveWorkbook.Name
        fPath = ex.ActiveWorkbook.Path
        fModDate = FileDateTime(wbName)
        DoCmd.SetWarnings False
        DoCmd.RunSQL ("INSERT INTO tblAccountDetails VALUES" & _
        "(" & """" & accountID & """" & "," & """" & acctName & """" & "," & """" & polPer & """" & "," & _
        """" & uw & """" & "," & """" & fName & """" & "," & """" & fPath & """" & "," & """" & fModDate & _
        """" & "," & """" & """" & "," & """" & """" & "," & """" & """" & ")")
        DoCmd.SetWarnings True
        
        wb1.Activate
        
        ex.Run "'C:\Global_Rate_Models\MainWorkbook.xlsb'!runAll_Click"
        
        ex.DisplayAlerts = False
        'wb2.Close
    
        If Dir("C:\Global_Rate_Models\InputWorkbook.xlsx") <> "" Then
            DoCmd.SetWarnings False
            DoCmd.RunSavedImportExport "np_CoverageDetail"
            DoCmd.RunSavedImportExport "Variables"
            DoCmd.DeleteObject acTable, "np_CoverageDetail$_ImportErrors"
            DoCmd.RunSQL "UPDATE np_CoverageDetail SET np_CoverageDetail.account_ID = " & _
            """" & accountID & """" & _
            "WHERE (((np_CoverageDetail.account_ID) Is Null));"
            
            DoCmd.RunSQL "UPDATE Variables SET Variables.account_ID = " & _
            """" & accountID & """" & _
            "WHERE (((Variables.account_ID) Is Null));"
            
            Kill "C:\Global_Rate_Models\InputWorkbook.xlsx"
        End If
        DoCmd.SetWarnings False
            DoCmd.OpenQuery ("delAccountsNotImported")
        DoCmd.SetWarnings True
        Forms("switchboard").Refresh
        accountID = accountID + 1
        rs.MoveNext
Loop

________________________________________________

Private Function exportData(Optional wb As String, Optional source As Workbook) As String
On Error Resume Next
    Dim c, co, nT, naic, naics, accName As String
    Dim tot
    
    c = Range("np_Country").Value2
    co = c
    naic = Range("np_NAICS").Value2
    naics = naic


    accName = Sheets("Exposure - GL").Range("D4").Value2
    tot = Sheets("Exposure - GR").Range("H6").Value2
    
    Dim nb As Workbook
    Dim n As Names
    
    Dim rc As String
    
    c = Application.WorksheetFunction.Proper(Left(c, 3))
    nT = c
    
    Sheets("Variables").Activate
    If wb = "" Then
        Sheets("np_CoverageDetail").Activate
        Range("A1:u27").Select
        Selection.Copy
        
        Application.Workbooks.Add
        Set nb = ActiveWorkbook
        Sheets(1).Activate
        ActiveSheet.Name = "np_CoverageDetail"
        
        rc = ActiveCell.Address
        
        ActiveSheet.Select
        Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=False
        
        Range(rc).Select
        ActiveCell.End(xlToRight).Select
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Formula = "Country"
        
        Range(Cells(ActiveCell.Offset(1, 0).Row, ActiveCell.Column), Cells(ActiveCell.Offset(26, 0).Row, ActiveCell.Column)).Select
        Selection.FormulaR1C1 = co
        
        Range(rc).Select
        ActiveCell.End(xlToRight).Select
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Formula = "NAICS"
        
        Range(Cells(ActiveCell.Offset(1, 0).Row, ActiveCell.Column), Cells(ActiveCell.Offset(26, 0).Row, ActiveCell.Column)).Select
        Selection.FormulaR1C1 = naics
        
        Range(rc).Select
        ActiveCell.End(xlToRight).Select
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Formula = "Account_Name"
        
        Range(Cells(ActiveCell.Offset(1, 0).Row, ActiveCell.Column), Cells(ActiveCell.Offset(26, 0).Row, ActiveCell.Column)).Select
        Selection.FormulaR1C1 = accName
        
        Range(rc).Activate
        ActiveCell.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
            
        


        Sheets(2).Activate
        ActiveSheet.Name = "Variables"
        source.Activate
        Sheets("Variables").Activate
        Range("A1:B102").Copy
        nb.Activate
        ActiveSheet.Select
        Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=True
        rc = ActiveCell.Address
        Range(rc).Select
        ActiveCell.FormulaR1C1 = "Account_Name"
        ActiveCell.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = accName
        ActiveCell.Offset(1, 0).Select
        
        Range(rc).Select
        ActiveCell.End(xlToRight).Select
        ActiveCell.Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = "Total"
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value2 = tot
        Range(rc).Select
        ActiveCell.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        Application.CutCopyMode = False
    Else
        Set nb = Workbooks(wb)
        Sheets("np_CoverageDetail").Activate
        Range("A2:U27").Select
        Selection.Copy
        
        nb.Activate
        Sheets("np_CoverageDetail").Activate
        
        rc = ActiveCell.Address
        
        ActiveSheet.Select
        Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=False
        
        Range(rc).Select
        ActiveCell.End(xlToRight).Select
        ActiveCell.Offset(0, 1).Select
        Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Offset(25, 0).Row, ActiveCell.Column)).Select
        Selection.FormulaR1C1 = co
        Range(rc).Select
        ActiveCell.End(xlToRight).Select
        ActiveCell.Offset(0, 1).Select
        Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Offset(25, 0).Row, ActiveCell.Column)).Select
        Selection.Formula = naics
        Range(rc).Select
        ActiveCell.End(xlToRight).Select
        ActiveCell.Offset(0, 1).Select
        Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Offset(25, 0).Row, ActiveCell.Column)).Select
        Selection.FormulaR1C1 = accName
        
        Range(rc).Activate
        ActiveCell.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
            
        
        
        Sheets(2).Activate
        Sheets("Variables").Activate
        source.Activate
        Sheets("Variables").Activate
        Range("B1:B102").Copy
        nb.Activate
        ActiveSheet.Select
        Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=True
        ActiveCell.Select
        rc = ActiveCell.Address
        ActiveCell.FormulaR1C1 = accName
        Cells(ActiveCell.SpecialCells(xlCellTypeLastCell).Row, ActiveCell.SpecialCells(xlCellTypeLastCell).Column).Select
        ActiveCell.Value2 = tot
        Range(rc).Select
        ActiveCell.Offset(1, 0).Select


        
        Application.CutCopyMode = False
    End If
    
    For Each n In ActiveWorkbook.Names
        n.Delete
    Next n
    
    exportData = ActiveWorkbook.Name
    source.Activate
End Function

______________________________________________

Private Function exportDataCleanup(Optional wb As String) As String
    Workbooks(wb).Activate
    Sheets("np_CoverageDetail").Activate
    Range("E:H,R:T").Select
    Selection.Delete
    Range("B:B,C:C,D:D,I:M").Select
    Selection.NumberFormat = "0.000000"
    Range("A1").Value = "Coverage"
    Range("B1").Value = "Exposure"
    Range("C1").Value = "Model_Rate"
    Range("D1").Value = "Model_Premium"
    Range("E1").Value = "Limit"
    Range("F1").Value = "Limit_Currency"
    Range("G1").Value = "Deductible"
    Range("H1").Value = "Deductible_Currency"
    Range("I1").Value = "BenchmarkRate_xComm_preAdjustments"
    Range("J1").Value = "Deductible_Adjustment"
    Range("K1").Value = "Size_Adjustment"
    Range("L1").Value = "BenchmarkRate_xComm_postAdjustments"
    Range("M1").Value = "BenchmarkPrem_xComm"
    Range("N1").Value = "Benchmark_DeviationReason"
    Range("O1").Value = "Country"
    Range("P1").Value = "NAICS"
    Range("Q1").Value = "Account_Name"
    Cells(1, 1).Select
    Sheets("Variables").Activate
    Range("B:B,O:O,P:P,R:R,S:S,BE:BE,BF:BF,BH:BH,BI:BI,CK:CK,CL:CL,CM:CM,CN:CN,CU:CU,CV:CV,CW:CW,CX:CX").Select
    Selection.Delete
    Columns("W:X").Select
    Range("W:X,AB:AB,AD:AE").Select
    Range("W:X,AB:AB,AD:AE,AF:AF").Select
    Range("W:X,AB:AB,AD:AE,AF:AF,AJ:AJ").Select
    Range("W:X,AB:AB,AD:AE,AF:AF,AJ:AJ,CC:CC, CD:CD, CE:CE, CF:CF").Select
    Selection.NumberFormat = "0.000000"
    Columns("E:F").Select
    Range("E:F,AQ:AR").Select
    Selection.NumberFormat = "m/d/yyyy"
    Cells(1, 1).Select
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "C:\Global_Rate_Models\InputWorkbook.xlsx", xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    exportDataCleanup = ActiveWorkbook.Name
End Function

 

[Norie]

Where exactly in the code do you get the error?

 

[colewillm]

I can't pinpoint where the error is coming from, but since I successfully am able to import the np_Coveragedetail data table from excel in access with DoCmd.RunSavedImportExport "np_CoverageDetail" and nothing else is completed after, I would guess something to do with this, or the next saved importexport for "variables" is causing the problem

 

[james_lankford]

try compacting your database
and then running your code

 

[Micron]

You should be able to step through any of this code and report on which line is raising the error.
First create a backup copy of the target table if you're going to play with table design as suggested in this post. lf one or more of the target table fields is a memo (long text) type with an index, try removing the index. Also check that you're not trying to import a spreadsheet column name that starts with one or more spaces.

The code is odd looking and has some potential pitfalls:
Dim acctName, polPer, uw, fName, fModDate, fPath As String
- fPath is the only string variable in this line. The rest are variants. Same goes for any other multi-variable declaration on one line that does not explicitly declare EACH variable. This can cause problems but I doubt it is your current issue, unless perhaps it is due to wb1 being a variant when the code is trying to treat it as an object.

This does not look right:

"UPDATE np_CoverageDetail SET np_CoverageDetail.account_ID = " & _
            """" & accountID & """" &

Substituting 123 for accountID, I see it being translated to

UPDATE np_CoverageDetail SET np_CoverageDetail.account_ID = ""123""

which, even if you removed one double quote on each side, you'd be treating this as a string value when (if I understand the post) it's a number.
There are other examples. Unless you are certain that this works, you should assign the sql statement to a declared variable (Dim strSql As String), step through until the next line after the concatenation is complete for the variable, then output the result in the immediate window (?strSql), hit Enter and copy/paste the sql into a new query in sql view and run it. I always assign sql to variables so that they can be tested. Given that this approach is repeated, I also wonder if checking for Not Null has been mistaken anywhere for empty strings - they are not the same thing.

 

[xenou]

"UPDATE np_CoverageDetail SET np_CoverageDetail.account_ID = " & _
            """" & accountID & """" &

I also thought the string concatenation looks wrong - but how did it work before (assuming your inherited code worked for the person who wrote it? Weird. And ugly the way it's written.