none
Access 2013 Web App Published, but I cannot find database on SQL Server 2012

    General discussion

  • I have a web application created in Access 2013 - everything works fine on client side, and also when I publish it to our SharePoint 2010 site.

    Wanting to leverage the power of SQL Server, and the possibility of writing SQL queries in SQL (rather than the very limited GUI-based query option in Access), we decided to move to SharePoint 2013 and SQL Server 2012 (both on same machine).

    Now, when I publish the web app from Access 2013, it publishes fine, but I cannot find a corresponding database on the SQL 2012 Server for this application. My understanding was that in SharePoint 2013, the Access web database gets stored as a SQL Server database (one database for each app), rather than keeping tables as lists, etc. as in SharePoint 2010. Right now we have SharePoint 2013, SQL 2012, Access 2013 web services, but upon publishing, the database is nowhere to be found on SQL Server 2012, implies we have the same limitations as in SharePoint 2010.

    Any idea what may be wrong - much thanks

    Jaydee


    jm


    • Edited by jaydee2007 Thursday, July 18, 2013 8:18 PM
    Thursday, July 18, 2013 7:12 PM

All replies

  • Something is amiss because you cannot publish an Access 2013 Web App to SharePoint 2010. It requires SharePoint 2013. Are you sure you did not publish it to Office 365 or did you just publish Access tables as SharePoint Lists?

    Sadly, and we hope to have this changed one day, we cannot at this time change anything at all regarding the SQL Server Database or Azure SQL Database schema, only the data. So that means the only way to change the schema in SQL Server is to change your Access Web App. The changes will then be made automatically in the SQL database.

     


    Patrick Wood,
    Founder, Gaining Access Technologies http://gainingaccess.net/

    Thursday, July 18, 2013 11:36 PM
  • You can try this. In Access open your Web App. Click on File tab and then you can click on the Manage button for Connections at the bottom. There you have a lot of options to enable you to access your data and your SQL Database.

    Patrick Wood,
    Founder, Gaining Access Technologies http://gainingaccess.net/

    Thursday, July 18, 2013 11:48 PM
  • I cannot see any Manage button under FILE in my Access 2013 setup...

    Thanks

    JD


    jm

    Friday, July 19, 2013 8:12 PM
  • Thanks Patrick. I had created the app using Access 2013, and it did publish successfully on both SharePoint 2010 and SharePoint 2013 servers (they are on different machines). The problem is the apps both look identical - what my understanding was that publishing on SharePoint 2013 saves a corresponding database in SQL Server 2102 (configured to contain these Access Services apps), instaed of saving Access data in SharePoint lists.

    Please see this link (http://blogs.msdn.com/b/kaevans/archive/2013/07/14/access-services-2013-setup-for-an-on-premises-installation.aspx) to basically see what I have done - the author here succeeds in getting a database on SQL Server 2012. For me, the advantages of such a setup, if it works, are substantial, as you can independently write reports, and create complex SQL queries outside of Access 2013 web application setup, which is very limited. In this sense, I think the schema can be changed in this new setup, not just the data.

    Also, looking at the Windows Event Viewer logs, it says that an Access 2010 app was created successfully on SharePoint 2013 server - I want it to create an Access 2013 app, not Access 2010 app - this may explain why on both SharePoint servers, it creates similar SharePoint lists for data. Some setting in SharePoint needs to be changed (I don't know which) to tell it to publish Access 2013 Services web apps as Access 2013 apps, with corresponding databases (one database for each new app).

    JD


    jm


    • Edited by jaydee2007 Friday, July 19, 2013 8:26 PM Clarifications
    Friday, July 19, 2013 8:19 PM
  • If you published something on SharePoint 2010 you DO NOT have an Access 2013 Web App and you DO NOT have a SQL Server Database or SQL Azure Database so it is no wonder you cannot find the SQL Server database and that is why you do not see the Manage button. I do not know how to make it any more clear for you.

    You probably have published Access tables on SharePoint as SharePoint Lists.


    Patrick Wood,
    Founder, Gaining Access Technologies http://gainingaccess.net/

    Saturday, July 20, 2013 3:39 AM
  • I read the article from the link you provided and a number of others by that author. Unfortunately he has a bad habit of using the wrong term.  Access Services is a 2010 application that is supported by SharePoint 2010 and nothing whatsoever is saved in SQL Server. Access 2013 Web Apps are supported by SharePoint 2013 only. If it is supported by SharePoint 2010 it is not an Access 2013 Web App. They are two totally different things even though some of the writers have mixed up the terms.

    I was on the Beta Testing team for Access 2013 and we begged them for the ability to change the Schema of SQL Server or SQL Azure database but our requests were not granted. So again, you absolutely cannot change the Schema, tables, queries, stored procedures, table triggers, or functions at all whatsoever. If you can change any of this then you do not have an Access 2013 Web App SQL Database.


    Patrick Wood,
    Founder, Gaining Access Technologies http://gainingaccess.net/

    Saturday, July 20, 2013 4:10 AM
  • If you cannot see that button you do not have an Access 2013 Web App. That button is there in all my Access Web Apps and it is in articles about Access 2013 Web Apps because it is a crucial feature of the Web Apps.

    Patrick Wood,
    Founder, Gaining Access Technologies http://gainingaccess.net/

    Saturday, July 20, 2013 4:13 AM
  • Thanks much Patrick - I truly appreciate your answers and insights. It is likely true that it is not an Access 2013 Web App, because it also says in Windows logs that an Access "2010" app was published successfully (and this is while I am publishing from Access 2013, to a SharePoint 2013 server - I have re-checked these things multiple times now). My question is - is there some setting in Access 2013 that identifies an application in it as an Access 2010 app - I am using Access 2013, so I was assuming that any web app in it is a 2013 web app, which doesn't seem to be true.

    Again, if a database is in SQL Server, my theory was that it will be possible to build views and reports separately from Access, which seems limited. Hence my comment about ability to change schema - not from Access 2013, but in SQL Server itself.

    I may have to re-build the application in Access 2013, from scratch (it is not  a big one, so should not take long). 

    One other issue is that I can't see my sharepoint server in Available Locations when I try to publish a new test app, but the old application publishes without a hitch.

    Would you be able to recommend any sites or books that explain Access 2013 web apps on SharePoint 2013?

    Again I appreciate your thorough replies and thank you so much

    JD


    jm

    Saturday, July 20, 2013 3:50 PM
  • The unfortunate use of the term "Access Services" in "Access Services in SharePoint 2013" is confusing but an Access 2010 Access Services web application is where all the data and the schema is stored in SharePoint 2010. This was not the best configuration so it was changed with Access 2013 and Access 2013 now has Web Apps which are very different from Access 2010 Access Services.

    I recommend you read this book when it comes out next month:
    Professional Access 2013 Programming

    I also recommend you read all the articles about Access 2013 Web Apps on the Access Team Blog: http://blogs.office.com/b/microsoft-access/ starting with this article Introducing Access 2013

    Also I recommend reading the following articles:
    What's new for Access 2013 developers 

    How to: Create and customize a web app in Access 2013

    Access 2013 and SQL Server


    Patrick Wood,
    Founder, Gaining Access Technologies http://gainingaccess.net/

    Saturday, July 20, 2013 7:08 PM
  • You can create a Reporting Database automatically with ODBC linked tables to your SQL Server Database. Although intended for creating and viewing Reports you can manage your data in the linked tables.

    The only way to change the schema in SQL Server is to change the design of your Access 2013 Web App using Access. The changes will automatically be made for you in SQL Server.


    Patrick Wood,
    Founder, Gaining Access Technologies http://gainingaccess.net/

    Saturday, July 20, 2013 7:12 PM
  • In the article linked to below the differences between Access Services (Access Web Databases) and Access 2013 Web Apps are explained. Note that what is commonly known as Access Services is called Access Web Databases in this article. Note that you cannot create an Access Services or Access Web Database using Access 2013 but you can open it using Access 2013.

    Overview of Access Services in SharePoint Server 2013

    SharePoint 2013 - Access Services


    Patrick Wood,
    Founder, Gaining Access Technologies http://gainingaccess.net/


    • Edited by PatrickWood Saturday, July 20, 2013 7:45 PM Added Link
    Saturday, July 20, 2013 7:40 PM
  • Thanks Patrick for the detailed replies and references. I will check them out

    Best regards

    JD


    jm

    Monday, July 22, 2013 2:31 PM
  • Hi Patrick,

    I see from reading your thread that you're very knowledgable about the Access 2013/Sharepoint 2013 integration.  I'm struggling with a very basic problem that I think you may have a simple answer for (I hope).

    For the purposes of playing around with Access 2013's Web App functionality, I setup a trial Office365 environment, so I could have a "properly" setup sharepoint site to play with.

    I then promptly tried to follow the access blog tutorial here: http://blogs.office.com/b/microsoft-access/archive/2013/01/22/visualize-access-data-in-excel.aspx ,  However, when I try to connect to the SQL server via Excel or SQL Server Management Studio, I simply get error messages indicating an inability to connect.  I checked my firewall settings and that I can talk to the server on the right port (1433)... but to no avail. 

    Since i'm following the tutorial nearly verbatim, including using Office365, as they do, i'm baffled as to why I can't access the underlying SQL Server database with non-access tools.  Do you have any idea what might be wrong?

    Wednesday, October 09, 2013 1:56 AM
  • Hi alesser.

    I was reading about your doubts and I'd likke to know if you achieved to connect Excel with Access. I've been trying but I get the same error tan you.

    Thx.

    Sunday, January 05, 2014 12:09 AM
  • I think it would be best to start a new thread so these questions can be answered accurately and fully because we may need to ask you questions and then you answer back and it could make for a discussion that is too complicated and long.


    Patrick Wood,
    Founder, Gaining Access Technologies http://gainingaccess.net/

    Sunday, January 05, 2014 12:42 AM