locked
Access Web App - Data Connect RRS feed

  • Question

  • I am running a trial of Office 365 Small Business Premium.

    I am testing a web app to see if this is something my company would want to track inventory /w. So i am just using the web app the preform simple tasks. However I would like to be able to access these lists from desktop access to preform more operations.

    I followed the blog post on how to set up connectivity.  

    1. Set to be accessed from everywhere

    2. Set to read and write

    3. I locate the read/write connections and try and imput that information in access desktop or excel, and receive the same error "SQL ERROR:40615"

    It continues to say to enable access use the sql azure portal or run sp_set_firewall_rule on the master database. I found another post on how to fix this (by logging on to the azure http://azure-howto.blogspot.com/2011/05/how-to-set-firewall-rule-to-connect-to.html) but after entering in my office 365 info it tells me i don't have azure services.

    My Question:

    1. http://blogs.office.com/b/microsoft-access/archive/2012/08/08/access-2013-and-sql-server.aspx Post (at the bottom) indicated that possibly if i didn't have the trial version it would work? is this the case?

    2. Or no matter what do i need another "azure" service to pull my access web app data off that server?

    Wednesday, May 8, 2013 10:44 PM

Answers

  • http://blogs.office.com/b/microsoft-access/archive/2012/08/08/access-2013-and-sql-server.aspx
    
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/0caa8980-1599-4a86-a7c3-c52fe3393db9

    Tuesday, May 14, 2013 5:22 PM

All replies

  • This is a quick note to let you know that we are doing research on this issue. We will let you know if there is any progress.

    Have a nice day.

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 9, 2013 5:27 AM
  • Compare connection string with my pre-last post from this thread http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/0caa8980-1599-4a86-a7c3-c52fe3393db9

    This works fine for me in Access and in SSMS, I didn't try Excel, but think it should work as well.


    Andrey V Artemyev | Saint-Petersburg, Russia

    • Proposed as answer by Sagar 1987 Tuesday, May 14, 2013 5:15 PM
    Thursday, May 9, 2013 6:31 PM
  • Sorry I am a new...

    So how exactly do you use access /w SSMS because all I have tried is odbc database with excel and access, and followed what the previous tutorials had setup.

    Thursday, May 9, 2013 7:56 PM
  • Access provides credentials, server name and database name fro you. That's all you need to connect via SSMS. Use server name, login and password at Login SSMS tab, database name - at Connection Properties tab. Fill in the field for database name manually (or just paste it from somewhere) but don't use a dropdown feature.

    Andrey V Artemyev | Saint-Petersburg, Russia

    Thursday, May 9, 2013 8:10 PM
  • So was this ever resolved? I have read this entire post and the link to the other post provided above and cannot see a clear resolution

    I get the same  "SQL ERROR:40615" error when I try to use the "Report on my Data" button on a 2013 Web App.

    Then there is a popup for the SQL Server Login

    I then enter the password provided when I click the "Connections" button.

    I still get the same  "SQL ERROR:40615" error.

    I have also previously installed the SQL Server 2012 Native Client Drivers .

    SSMS is mentioned in post previously. Do I need to install that also? And then connect using the info provided from the MS Access "Connections" button? Then run a "firewall rule"?

    TIA for any help

    Saturday, May 11, 2013 2:50 PM
  • So was this ever resolved?

    The problem is that this has never been faced.

    Report on my Data

    Could you show a screenshot? I cannot see this button in a web app.

    SSMS is mentioned in post previously. Do I need to install that also? And then connect using the info provided from the MS Access "Connections" button? Then run a "firewall rule"?
    If you want to work with SQL Server tables via SSMS, you can install it as well, but that's not obligatory. As for firewall rule, I know nothing about it, unfortunately.

    Andrey V Artemyev | Saint-Petersburg, Russia

    Saturday, May 11, 2013 3:23 PM
  • The "Report on my Data" button is the same one you posted in the thread you mention above.
    Saturday, May 11, 2013 3:59 PM
  • Oh :D

    I really didn't notice it. So what can I say? It works fine for me w/o any error messages.


    Andrey V Artemyev | Saint-Petersburg, Russia

    Saturday, May 11, 2013 4:43 PM
  • HMMM. Doesn't work for me whether I try a Read Only or a Read Write Connection.

    I get the error message:

    Connection failed:

    SQLState:'37000'

    SQL Server Error: 40615

    [Microsoft][SQL Server Native Client11.0][SQL Server] Cannot open server '{my server name}' requested by the login. Client IP address '{my IP Address}' is not allowed to access the server. To enable access, use the SQL Azure Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

    Saturday, May 11, 2013 9:05 PM
  • I was able to create a new web  app and it works for me also.  I was able to link and read the data from SQL Server. However I then tried to create a read/write connection and it was also read only. I can not update or perform an  insert.

    Although I see the process works (at least for a read only connection) I still would like to know how to "fix" the issue that NickWDunn originally reported.

    Sunday, May 12, 2013 12:16 AM
  • http://blogs.office.com/b/microsoft-access/archive/2012/08/08/access-2013-and-sql-server.aspx
    
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/0caa8980-1599-4a86-a7c3-c52fe3393db9

    Tuesday, May 14, 2013 5:22 PM
  • I don't think either of the links actually answers the question

    1st link is a blog that says you should be able to connect read/write as I attempted to do. Although it is a very good article and confirmed that I should be able to connect from a Access desktop application to the Azure Sql database, it does not give much detail on how to do that and nothing about the error I received.

    2nd link contains useful info and the author states he was able to connect using SSMS (which I would love to understand how to do that) . I don't think the connection code he provided will  work unless you are able to create a firewall to allow access from the desktop IP address.


    Theo Fountain

    Monday, June 17, 2013 3:10 AM
  • Ok, the solution is to handly create the linked tables with the odbc string.

    The wizard and other connections doesnt works, I think because the wizard and as well sql server try to access differents objects in the database, while the hand direct connection works fine.

    Bye

    Davide


    Davide

    Thursday, December 19, 2013 11:01 PM
  • The best method I have is, enable the read/write connection within access and then create a DSN file in notepad using the following text:
     
    [ODBC]
    DRIVER=SQL Server Native Client 11.0
    UID=(username)
    Pwd=(password)
    Encrypt=yes
    DATABASE=(database name)
    SERVER=(servername)
     
    Save the file with the extension .dsn
     
    You can then load this as a file data source in access or wherever to create a linked table.
     
    As with any linked table you will not be able to edit the table design.
    Thursday, July 7, 2016 6:58 AM