locked
Refresh Linked SQL table in Access Web App RRS feed

  • Question

  • Scenario:

    I created a table from an ODBC SQL database. I added 2 fields to the SQL table but they do not show up in Access Web App. The Linked Table Manager is greyed out so I am not sure how to refresh the Access Web App table.


    John Fletcher

    Thursday, June 4, 2015 4:06 PM

Answers

  • Hi John,

    Unless I am misunderstanding what you did to create your web app, this is how I understand the process works. You have to use Access 2013 to create an Access Web App in SharePoint 2013 (Office 365), which in turn creates the objects (tables, views, triggers, etc.) in an instance of SQL Azure. If you created your web app from local Access tables and a linked SQL Server (not Azure) table, then I believe the process would have imported those data into the newly created SQL Azure tables, which means your Access Web App is not, in any way, connected to those original tables (local and SQL Server).

    Now, if that's what you did, it is possible to create a desktop version of your web app that can connect to those SQL Azure tables. Is that what you're asking to do?

    Saturday, June 6, 2015 7:13 PM
  • Hi SpaceData,

    As you found, Access includes import wizards to help you bring the data into your new web app. You can import data into a web app that’s in text files, spreadsheets, other Access desktop databases, SharePoint lists, and any SQL database that supports the Open Database Connectivity (ODBC) software standard. You can also create read-only links to SharePoint lists inside the same SharePoint. As well known, Import and link are different. You can find that SharePoint list provide two options (Import and Link), but in the ODBC Database, there is only Import Option. Since this, if you import a table form an ODBC SQL database, it would not keep refreshing when your SQL table changed.

    If this is a feature which you want to include in the further version of Access 2013 web app, I suggest you submit a feedback.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, June 9, 2015 9:06 AM

All replies

  • Hi SpaceData,

    >>I added 2 fields to the SQL table but they do not show up in Access Web App. The Linked Table Manager is greyed out so I am not sure how to refresh the Access Web App table.

    First of all, could you share us your Access version? Is it Access 2010 web database or Access 2013 web app? I checked the Access 2010 web database and Access 2013 web app, there is a Linked Table Manager button in Access 2010 web database which is greyed, and there is no Linked Table Manager button in Access 2013 web app. In my option, whichever access version you used, you could not use the Linked Table Manager in the web apps. In other words, you could not use the Linked Table Manager both in Access 2010 web database and Access 2013 web app.

    If this is a feature which you want to include in the further version of Access 2010 web database, I suggest you submit a feedback from the link below:

    http://support2.microsoft.com/common/survey.aspx?scid=sw%3ben-us%3b2222&altStyle=MFE&renderOption=OverrideDefault&showpage=1&fr=1&nofrbrand=1

    If this is a feature which you want to include in the further version of Access 2013 web app, I suggest you submit a feedback as picture below:

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, June 5, 2015 8:15 AM
  • Edward,

    I am using Access 2013 Web App. Several tables were imported from Access 2013 desktop but one table was created from a SQL database via ODBC. Everything works great except the linked ODBC table does not update when new data has been added. Is it not like connecting to a SharePoint List?

    Thank you


    John Fletcher

    Saturday, June 6, 2015 12:59 AM
  • Hi John,

    I could be wrong, but I believe when you created your web app from those tables, Access more than likely imported the data into new SQL Azure tables, so the web app is not really linked to the original tables at all.

    Saturday, June 6, 2015 3:04 AM
  • Thanks DB Guy,

    I assumed that since you could link to a SharePoint list (be it read-only) I could do the same with a SQL Azure table via ODBC. Do you know if there are plans to connect to other data sources in the future?


    John Fletcher

    Saturday, June 6, 2015 7:03 PM
  • Hi John,

    Unless I am misunderstanding what you did to create your web app, this is how I understand the process works. You have to use Access 2013 to create an Access Web App in SharePoint 2013 (Office 365), which in turn creates the objects (tables, views, triggers, etc.) in an instance of SQL Azure. If you created your web app from local Access tables and a linked SQL Server (not Azure) table, then I believe the process would have imported those data into the newly created SQL Azure tables, which means your Access Web App is not, in any way, connected to those original tables (local and SQL Server).

    Now, if that's what you did, it is possible to create a desktop version of your web app that can connect to those SQL Azure tables. Is that what you're asking to do?

    Saturday, June 6, 2015 7:13 PM
  • I created the Web App in SharePoint and imported all data from an existing Access 2013 desktop database. Everything was created properly. Later I decided to add a new table from SharePoint. This also worked as advertised - even thought the connection was one direction (SharePoint to Access Web app) the data is updated automatically when a change occurs on the SharePoint list. Finally, using the same Wizard to add a new table I selected SQL instead of SharePoint assuming it would provide a similar updatable connection. Apparently, this only works with a SharePoint list.

    John Fletcher

    Sunday, June 7, 2015 4:41 PM
  • I created the Web App in SharePoint...

    Hi John,

    I'm not sure we're using the same terminology. Did you create the web app using Access 2013 into a SharePoint 2013 site? If so, then the tables should have been created in SQL Azure.

    Later I decided to add a new table from SharePoint. This also worked as advertised - even thought the connection was one direction (SharePoint to Access Web app) the data is updated automatically when a change occurs on the SharePoint list.

    I'm not sure I follow this one, if it's a true web app. It sounds more to me like a desktop app at this point. But without seeing what you actually got, I really can't tell.

    Finally, using the same Wizard to add a new table I selected SQL instead of SharePoint assuming it would provide a similar updatable connection. Apparently, this only works with a SharePoint list.

    Which Wizard would that be?

    Sorry, I'm not sure exactly what you're dealing with without being able to see what you got.

    One quick question, a web app can only be accessed using a browser. Is your web app being accessed using a browser?

    Sunday, June 7, 2015 5:59 PM
  • Sorry for the confusion - here are screen shots:

    1:Created the APP in SharePoint

    2: Import data from Access 2013 desktop database.

    3: Link to SharePoint List (Read-only but data is at least refreshed from SharePoint List)

    Link to SharePoint List

    4: Link to SQL/ODBC (Data is neither read-only nor refreshed from SQL changes - why even have a connection?)

    SQL ODBC Connection


    John Fletcher

    Monday, June 8, 2015 5:04 PM
  • Hi SpaceData,

    As you found, Access includes import wizards to help you bring the data into your new web app. You can import data into a web app that’s in text files, spreadsheets, other Access desktop databases, SharePoint lists, and any SQL database that supports the Open Database Connectivity (ODBC) software standard. You can also create read-only links to SharePoint lists inside the same SharePoint. As well known, Import and link are different. You can find that SharePoint list provide two options (Import and Link), but in the ODBC Database, there is only Import Option. Since this, if you import a table form an ODBC SQL database, it would not keep refreshing when your SQL table changed.

    If this is a feature which you want to include in the further version of Access 2013 web app, I suggest you submit a feedback.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, June 9, 2015 9:06 AM
  • Hi John,

    I agree with Edward. As I said earlier, when you create tables for your web app, you can start with an empty table or import existing data from multiple sources. However, the table that Access created is actually a copy of that table source. It is not linked to each other at all because the new table is created in SQL Azure. Of course, this excludes the linked SharePoint List from the same site.

    Hope that makes sense...

    Wednesday, June 10, 2015 3:22 AM
  • Even though the connection is to another Web App on the same SharePoint site and via ODBC which required the SQL credentials to even see the tables. I find it odd that I can connect Excel, Visio, and other programs but not Access Web.

    John Fletcher

    Wednesday, June 10, 2015 4:40 AM
  • Hi John,

    >>Even though the connection is to another Web App on the same SharePoint site and via ODBC which required the SQL credentials to even see the tables.

    As you know, you could connect to another web app via ODB which required the SQL credentials. It is because that the Access web app is different from Access client. The data of Access client is stored in Access, but the data of Access web app is stored in the SQL Azure database. If you want to connect to Access web app, the really database you connect is SQL Azure database.

    >> I find it odd that I can connect Excel, Visio, and other programs but not Access Web.

    Based on my understanding, JET provide the data service in Access client database, and it could connect to excel. But Access web app is different from Access client, there is no JET in the Access web app, you could not connect to the excel file directly with Access web app.
    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 11, 2015 9:57 AM