none
Import Access to Office Sharepoint 365 - Field lookup failed

    Question

  • Hi,

    I have an Access database on my local drive and would like to import to Office 365 Sharepoint online.

    First, I have created an Access App on Office 365.

    Then I import my Desktop database using Access database.

    The problems are:

    • The imported tables lost primary keys that I set to one of my columns.
    • The lookup rows are set to a standard "short text" which do not have the lookup function in desktop database.

    Do you know any tutorials or any tips that allow to import the relationship from desktop to online database ?

    Many thanks.

    Saturday, January 21, 2017 10:00 AM

Answers

  • Hi all,

    I found the problem. The lookup table works only with "Lookup Wizard ..." type.

    Here is what I fix it:

    1. Open local desktop Access
    2. In data view of Table, create new field. On Data Type, select "Lookup Wizard ..."
    3. Follow instructions to create relationship between tables
    4. Use desktop Access to copy paste the data which need to be lookup. 
    5. From Access Web App, use Import Access.

    DNN

    • Marked as answer by GauAli Monday, January 23, 2017 10:13 PM
    Monday, January 23, 2017 10:13 PM

All replies

  • Hi,

    You can export your tables from Access database as SharePoint lists to SharePoint Online.

    Right click table->Export->SharePoint List, then enter your SharePoint Online site URL.

    In my case, I create a lookup field in table "List2" to lookup the table "List1".

    If you want to store the data in Access app in SharePoint Online, please check the video below:

    How to migrate access database to office365

    https://www.youtube.com/watch?v=4tpgX2tQDBE

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, January 23, 2017 7:59 AM
    Moderator
  • Hi Dennis,

    Many thanks for your answer.

    I did try your method but it failed in my case. 
    After exporting my table to Sharepoint List, the lookupfield becomes "single line text" type and there is no drop-down menu available.

    In my desktop access database, the lookupfield is set in Lookup tab - Row Source code.

    Do I miss some steps ?

    I will try "How to migrate access database to office365" method later.



    DNN


    • Edited by GauAli Monday, January 23, 2017 9:12 PM correct info
    Monday, January 23, 2017 5:18 PM
  • Hi all,

    I found the problem. The lookup table works only with "Lookup Wizard ..." type.

    Here is what I fix it:

    1. Open local desktop Access
    2. In data view of Table, create new field. On Data Type, select "Lookup Wizard ..."
    3. Follow instructions to create relationship between tables
    4. Use desktop Access to copy paste the data which need to be lookup. 
    5. From Access Web App, use Import Access.

    DNN

    • Marked as answer by GauAli Monday, January 23, 2017 10:13 PM
    Monday, January 23, 2017 10:13 PM