none
How to Resolve "Run time error 3709 The search key was not found in any record" ?

    Question

  • Hello people,

    I am trying to import sharepoint list "Task" from multiple share point sub sites by using following code.

    Sub mainprocess()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSite As String
    Dim strList As String
    Dim strTable As String
    Dim strURL As String

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Dashboard")

    'List to import
    strList = "Task"

    'Name of new Access table
    strTable = "Task"

    Do Until rs.EOF

        'Get the address from the table
        strSite = HyperlinkPart(rs![project name], acAddress)
       
       
        DoCmd.TransferSharePointList acImportSharePointList, Replace(strSite, "/default.aspx", ""), strList, , strTable
        
     
        
        
        rs.MoveNext
       
    Loop
     
    End Sub

    out of 70 list "Task" i am able to import 67 but 3 list are thowing error

    Run time error 3709 The search key was not found in any record

    When i use following code than i was able to import this 3 list (but i want to import all of my list using above code only because as per below code i have to provide list GUID for all the list).

    DoCmd.TransferDatabase acImport, "WSS", _
     "WSS;HDR=NO;IMEX=2;" & _
     "DATABASE=http://office-sitepath;" & _
     "LIST={32674B6F-E4D3-41EA-9BE1-1A2B55C6C6AA};" & _
     "VIEW=;RetrieveIds=Yes;TABLE=Task", acTable, , _
     "Task"

    Thanks in advance.

    Thursday, October 6, 2011 4:48 PM

All replies

  • That sounds like corruption.  Make a copy of the database just as a precaution and then do a COMPACT AND REPAIR.  Then search through the table for a record that has no PK value.  Delete it and then it should be fine (you'll need to make sure to add that record back and you will need to change the FK for that record in any child tables.
    Bob Larson, Access MVP 2008-2010, 2011
    Thursday, October 6, 2011 7:57 PM
  • Thanks BoB for your suggestion. I created new database and imported all the table and modules into it than did compact and repair in both database but throwing same error.

    When i use following code than all 70 list are imported.

    DoCmd.TransferDatabase acImport, "WSS", _
     "WSS;HDR=NO;IMEX=2;" & _
     "DATABASE=http://office-sitepath;" & _
     "LIST={32674B6F-E4D3-41EA-9BE1-1A2B55C6C6AA};" & _
     "VIEW=;RetrieveIds=Yes;TABLE=Task", acTable, , _
     "Task"

    I did'nt understand how to search for a record in a table that has no primary key value. There is no primary or foreign key assing to any field.

    strangely when i am importing another list "Risk/Issues" from same 70 subsites than  i am getting all 70 list without any error.


    • Edited by zaveri cc Thursday, October 6, 2011 9:13 PM
    Thursday, October 6, 2011 9:11 PM
  • strangely when i am importing another list "Risk/Issues" from same 70 subsites than  i am getting all 70 list without any error.

    Well then, this appears to be definitely DATA SPECIFIC. Also, be aware that error messages can be misleading. Perhaps in this case, you have rows with invalid data like Longs that should be Doubles, Text instead of numeric data, etc.

     

    Friday, October 7, 2011 3:46 AM
  • I am wondering if prolem is data specifiec than how come i am able to import those 3 list using my code where i have to supply list GUID?

    My code

    DoCmd.TransferDatabase acImport, "WSS", _
     "WSS;HDR=NO;IMEX=2;" & _
     "DATABASE=http://office-sitepath;" & _
     "LIST={32674B6F-E4D3-41EA-9BE1-1A2B55C6C6AA};" & _
     "VIEW=;RetrieveIds=Yes;TABLE=Task", acTable, , _
     "Task"

    My problem is still not resolved. Any thoughts? thanks for your help so far.

    Friday, October 7, 2011 4:52 PM
  • Late to the party, but I had a similar issue. One way to diagnose the issue is to confirm that the Menu External Data--> Excel -> Import works. In my case I was getting 3709 because the first line was not the headers. Doing the manual import highlighted the issue.  Once the first line was deleted and the column headers where good, the transferDatabase was fine.

    I expect other column header issues may produce a 3709 as well.

    Wednesday, March 21, 2012 8:10 PM
  • It would appear the the link between Task and the GUID is broken on the three sites where your original code is failing.  Therefore you can reference the data through the GUID but not through the Task reference.  Can you get the SharePoint administrator to check for this?


    http://www.saberman.com

    Thursday, March 22, 2012 5:04 PM
  • i located some leading spaces in the headers of the data when i encountered this error.  after removing the spaces, the error was gone.

    http://home.comcast.net/~mfuller6_jam1/site/

    http://home.comcast.net/~mfuller6_jam1/site/?/blog/

    Friday, June 8, 2012 6:55 PM