none
How to convert a linked table to local with Access vba RRS feed

  • Question

  •  I have some tables that are linked and I need to programatically (in vba) convert them to local.  Unfortunately those tables have calculated values and therefore I can not an INTO query.  

    I found the command docmd.runcommand (acCmdConvertLinkedTableToLocal)  But I do not know how to tell it which table to unlink.

    I know that manually I can right click on the table and select "Convert to Local Table".  But how can I do the equivalent in vba code?

    Sunday, August 20, 2017 7:51 PM

Answers

  • Hello,

    >>The search key was not found in any record

    Do you get the error for all linked tables? Please check if the field name is valid.

    Please visit the similar thread:

    MS-access reports - The search key was not found in any record - on save

    "The search key was not found in any record" importing spreadsheet to table

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Tallrey Tuesday, August 22, 2017 11:38 PM
    Monday, August 21, 2017 6:03 AM
    Moderator
  • I finally solved the problem.  Here is the code that did it:

    aPath = CurrentDb.TableDefs(aTable).Connect

    If Len(aPath) > 0 Then
                    'Convert any linked files to local (we do it this way because they may contain computed fields)
        EqualSign = InStr(1, aPath, "=")
        If EqualSign > 0 Then
                  aPath = Mid(aPath, EqualSign + 1)
                   SourceTblName = CurrentDb.TableDefs(aTable).SourceTableName
                   DoCmd.DeleteObject acTable, aTable
                    DoCmd.TransferDatabase acImport, "Microsoft Access", aPath, acTable, SourceTblName, aTable, False
          End If
    End If

    Thanks to all that gave hints and ideas.

    • Marked as answer by Tallrey Tuesday, July 17, 2018 3:41 PM
    Tuesday, August 22, 2017 11:43 PM

All replies

  • RunCommand acts on the currently selected object.

    You could select the desired table using DoCmd.SelectObject


    -Tom. Microsoft Access MVP

    Sunday, August 20, 2017 8:24 PM
  • Tom:

    Thank you very much for replying.  I had tried that and it did not work. Here is the code I used.

        

     DoCmd.SelectObject acTable, "StudyInfo1"

    DoCmd.RunCommand acCmdConvertLinkedTableToLocal

    I got the error message: "Run Time Error '2489': The Object StudyInfo1 is not open"

    Then I ran :

    DoCmd.OpenTable "StudyInfo1"
    DoCmd.SelectObject acTable, "StudyInfo1"
    DoCmd.RunCommand acCmdConvertLinkedTableToLocal

    And I got:  "Run Time Error '2046': The command or action 'acCmdConvertLinkedTableToLocal' isn't available now"

    I then ran : 

    DoCmd.SelectObject acTable, "StudyInfo1", True
    DoCmd.RunCommand acCmdConvertLinkedTableToLocal

    It does select the Table but I get the error message: "Run-time error '3709':  The search key was not found in any record." 

    I have no idea where to go from here.

    Any ideas?


    • Edited by Tallrey Sunday, August 20, 2017 10:09 PM
    Sunday, August 20, 2017 8:49 PM
  • DoCmd.SelectObject acTable, "StudyInfo1", True

    DoCmd.RunCommand acCmdConvertLinkedTableToLocal


    -Tom. Microsoft Access MVP


    Sunday, August 20, 2017 9:18 PM
  • I found the command docmd.runcommand (acCmdConvertLinkedTableToLocal)  But I do not know how to tell it which table to unlink.

    Hi Tallrey,

    As an alternative you could try the CopyObject method  DoCmd.CopyObject; as SourceObjectName you use the name of the (external) linked table. If necessary, you can delete the external table afterwards.

    Imb.

    Sunday, August 20, 2017 9:22 PM
  • DoCmd.SelectObject acTable, "StudyInfo1", True

    DoCmd.RunCommand acCmdConvertLinkedTableToLocal


    -Tom. Microsoft Access MVP


    Tried it and I get:

    It does select the Table but I get the error message: "Run-time error '3709':  The search key was not found in any record." 

    Sunday, August 20, 2017 10:12 PM
  • Hello,

    >>The search key was not found in any record

    Do you get the error for all linked tables? Please check if the field name is valid.

    Please visit the similar thread:

    MS-access reports - The search key was not found in any record - on save

    "The search key was not found in any record" importing spreadsheet to table

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Tallrey Tuesday, August 22, 2017 11:38 PM
    Monday, August 21, 2017 6:03 AM
    Moderator
  • I finally solved the problem.  Here is the code that did it:

    aPath = CurrentDb.TableDefs(aTable).Connect

    If Len(aPath) > 0 Then
                    'Convert any linked files to local (we do it this way because they may contain computed fields)
        EqualSign = InStr(1, aPath, "=")
        If EqualSign > 0 Then
                  aPath = Mid(aPath, EqualSign + 1)
                   SourceTblName = CurrentDb.TableDefs(aTable).SourceTableName
                   DoCmd.DeleteObject acTable, aTable
                    DoCmd.TransferDatabase acImport, "Microsoft Access", aPath, acTable, SourceTblName, aTable, False
          End If
    End If

    Thanks to all that gave hints and ideas.

    • Marked as answer by Tallrey Tuesday, July 17, 2018 3:41 PM
    Tuesday, August 22, 2017 11:43 PM
  • Hi Tallrey, I'm completely new to vb and only somewhat familiar with Access, but I need to do exactly what you have done. *Where* exactly are you executing this code? (From within Access? In Visual Studio?) I'm working with an adp file and need to create the tables locally rather than just the linked tables that are already in there. Any specific instructions or directions would be much appreciated! Thank you!
    • Marked as answer by Tallrey Tuesday, July 17, 2018 3:33 PM
    • Unmarked as answer by Tallrey Tuesday, July 17, 2018 3:41 PM
    Tuesday, July 17, 2018 3:12 PM
  • Katie:

    The code above is run from a Module within an Access database.  When you are in an Access database, use "Create" on the ribbon and then "Module".  You can then write the code there and execute it.

    Tuesday, July 17, 2018 3:41 PM
  • Out of interest did the table in question have a Lookup Field linking back to itself (with _1 on the end) in the Relationship Manager?

    I've had a massive issue with this previously and had to remove the Lookup Field

    Tuesday, April 30, 2019 5:05 PM