locked
Create t-sql store procedure in a access web app RRS feed

  • Question

  • Hi, 

    I was creating a access web app and got to connect to the sql server database using management studio. But I'm noticed that I don't have the right to create t-sql procedure. Is there a way that I can do that ? I want to use access as my front end but sql server as my back end

    Saturday, January 28, 2017 5:35 PM

Answers

  • Hi Joe,

    If your main goal is for the user to use their internet browser to use the database, then I am not sure you can create stored procedures if you go the Access Web App (AWA) route. Otherwise, you can't avoid using .Net and learning HTML and CSS because you now want to work in the web platform, which requires HTML and CSS.

    If you can drop Access out of the picture, there may be other development platforms suitable for web applications, which may be light on learning requirements for HTML and CSS. For example, LightSwitch or PowerApps. However, I am not sure if those platforms can consume T-SQL stored procedures.

    So, bottom line, if you want to continue using T-SQL stored procedures, you may have to stick with desktop Access front end. You can still use desktop Access front end over the Internet, you can't just use an internet "browser" as a user interface.

    Just my 2 cents...

    • Proposed as answer by Chenchen Li Tuesday, January 31, 2017 9:05 AM
    • Marked as answer by JoeMsAccess Saturday, February 4, 2017 2:08 PM
    Saturday, January 28, 2017 6:16 PM
  • ... and MS didn't want the developer to accidentally "mess" something up, which could render the AWA unusable.

    As side step, why then did MS make Access?

    Imb.

    • Marked as answer by JoeMsAccess Saturday, February 4, 2017 2:08 PM
    Saturday, January 28, 2017 8:40 PM

All replies

  • Hi,

    Unfortunately, no. Access as a front end is not the same as "internet browser" (i.e. web app) as a front end.

    If you use the desktop version of Access as a front end to SQL Server backend, then yes, you can create and use stored procedures.

    But if you want to use an Access Web Application as a front end to SQL Server or Azure backent, then you will be limited to what you can create from the Access user interface. (Although, it's not entirely true because you can use .Net web services as well).

    Just my 2 cents...

    • Proposed as answer by Chenchen Li Tuesday, January 31, 2017 9:04 AM
    Saturday, January 28, 2017 5:40 PM
  • Thank you. I do use the desktop version to do that and it work very well. But looking for a quick way to migrate my existing app desktop in the web - my app are mostly all t-sql and some minor form a little vba.

    When you say .net to you mean visual studio ? I tried that but leaning curve for .asp and HTML CSS it too much effort. I can manage c# but don't want to learn HTML and CSS

    Saturday, January 28, 2017 5:50 PM
  • Hi Joe,

    If your main goal is for the user to use their internet browser to use the database, then I am not sure you can create stored procedures if you go the Access Web App (AWA) route. Otherwise, you can't avoid using .Net and learning HTML and CSS because you now want to work in the web platform, which requires HTML and CSS.

    If you can drop Access out of the picture, there may be other development platforms suitable for web applications, which may be light on learning requirements for HTML and CSS. For example, LightSwitch or PowerApps. However, I am not sure if those platforms can consume T-SQL stored procedures.

    So, bottom line, if you want to continue using T-SQL stored procedures, you may have to stick with desktop Access front end. You can still use desktop Access front end over the Internet, you can't just use an internet "browser" as a user interface.

    Just my 2 cents...

    • Proposed as answer by Chenchen Li Tuesday, January 31, 2017 9:05 AM
    • Marked as answer by JoeMsAccess Saturday, February 4, 2017 2:08 PM
    Saturday, January 28, 2017 6:16 PM
  • PowerApps was the next thing i was going to look at.

    That said,  I'm surprised AWA doesn't let you do that. You can create procedure through the UI but not directly in the SQL database. (why should I learn " Access data macro" when I can do them directly in SQL)

    Also, I wouldn't mind keeping the heaving stuff on desktop but give the user access to view and update some data using AWA. I can't do that because i can't copy my procedure in the AWA SQL database or can't make AWA use my current SQL database.

    Thank for you reply.

    Joe

    Saturday, January 28, 2017 6:37 PM
  • Hi Joe,

    My guess why you can't create stored procedures or really why you don't have a whole lot of permissions to the SQL Server instance of the backend is probably because AWA does a lot of things behind the scenes and MS didn't want the developer to accidentally "mess" something up, which could render the AWA unusable.

    For example, if I remember correctly, AWA basically converts your macros into stored procedures, and so you don't have access to create SPs yourself - you only have access to create macros, when then becomes SPs.

    Just my 2 cents...

    Saturday, January 28, 2017 8:13 PM
  • ... and MS didn't want the developer to accidentally "mess" something up, which could render the AWA unusable.

    As side step, why then did MS make Access?

    Imb.

    • Marked as answer by JoeMsAccess Saturday, February 4, 2017 2:08 PM
    Saturday, January 28, 2017 8:40 PM
  • ... and MS didn't want the developer to accidentally "mess" something up, which could render the AWA unusable.

    As side step, why then did MS make Access?

    Imb.

    Hi Imb,

    Sorry but I don't understand the question. Are you asking when they created AWA?

    Saturday, January 28, 2017 8:50 PM
  • Sorry but I don't understand the question. Are you asking when they created AWA?

    Hi theDBguy,

    It was meant a little bit cynical. If MS would be so protective against developpers with respect to AWA, they could also have done a better job with respect to Access. In Access you can mess up anything you want.

    One should be protective with respect to users. Or are developpers "users" in the AWA-world?

    Imb.

    Saturday, January 28, 2017 9:25 PM
  • Hi Imb,

    I think I understand what you mean. The difference between desktop Access and AWA is the reliance of AWA to the SharePoint platform. If you create a desktop Access database, MS does not care what you do with it. But if you create an AWA, then they have to make sure it plays nice with SharePoint. Allowing developers to tinker in the area behind the scenes probably just offers too many risks they are not willing to take.

    Just my humble opinion...

    • Proposed as answer by Chenchen Li Tuesday, January 31, 2017 9:04 AM
    Saturday, January 28, 2017 9:42 PM
  • Still think MS should have done a better job on providing a easy a quick application (tool) to let power user quickly create web app like they did when they built Access for desktop app.  

    It just so easy to create a nice and efficient app quickly (not expensive) in Access - even better when you use SQL Server as a backend.  Would have love to have this for web app.

    Joe

    Sunday, January 29, 2017 2:46 AM
  • Still think MS should have done a better job on providing a easy a quick application (tool) to let power user quickly create web app like they did when they built Access for desktop app.  

    It just so easy to create a nice and efficient app quickly (not expensive) in Access - even better when you use SQL Server as a backend.  Would have love to have this for web app.

    Joe

    Hi Joe,

    I think MS is trying to provide a quick application tool for web apps except they call it Power Apps rather than Access.

    Just kidding...

    Sunday, January 29, 2017 3:32 AM
  • Hi Joe,

    I would suggest you submit your feedback on Access User Voice site: https://access.uservoice.com/

    If you don’t have any other issue about your original question, I suggest you mark helpful post as answer.

    Thanks for your understanding.

    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.

    Tuesday, January 31, 2017 9:06 AM