none
Replacing linked tables with Web Service RRS feed

  • Question

  • We have a solid MS Application that has both local and linked SQL tables.  There is a desire to move away from using local MS Access tables and linked tables to a Web Service interface to SQL tables.

    So my question is....Is it possible to use an OData web service to push/pull data on SQL server?  If so, are there any links that could provide additional information.  I have found many links dealing with pulling data, but I am interested in pushing (saving) data back onto SQL server.

    Tuesday, February 28, 2017 3:27 PM

Answers

  • If you are using Access or any other VBA client it's really not worth the hassle of using a Web or WCF Service unless you have no other choice. Access does not have native support for either of these technologies so you have to rely on clunky interop mechanisms to accomplish the task.

    If you really want to use WCF, or for that matter Web API which appears to be what Microsoft is focusing on, then you should be developing your apps using Visual Studio, which has native support in the .NET Framework for these technologies.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Fidencio Pena Tuesday, March 7, 2017 9:25 PM
    Wednesday, March 1, 2017 2:20 PM

All replies

  • Hello,

    >>I am interested in pushing (saving) data back onto SQL server.

    I think your issue is related to SQL server, I suggest you post on SQL Server forum. Besides, please visit links below and hope it could be helpful.

    Tutorial: Using the OData Source

    https://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api

    Using the New OData Source in SQL Server Integration Services

    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.

    Wednesday, March 1, 2017 6:21 AM
    Moderator
  • If you are using Access or any other VBA client it's really not worth the hassle of using a Web or WCF Service unless you have no other choice. Access does not have native support for either of these technologies so you have to rely on clunky interop mechanisms to accomplish the task.

    If you really want to use WCF, or for that matter Web API which appears to be what Microsoft is focusing on, then you should be developing your apps using Visual Studio, which has native support in the .NET Framework for these technologies.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Fidencio Pena Tuesday, March 7, 2017 9:25 PM
    Wednesday, March 1, 2017 2:20 PM
  • Paul,

      That was my thought also, but need to do my due diligence to see if there was a possibility of using OData services from within MS Access.  I did find one product ( https://www.cdata.com/drivers/odata/odbc/ ) which claims to allow you to perform CRUD via an OData service, but it was the only thing that I found.  The scarcity of products/tools dealing with this type of need indicates to me that it is not worth it.

         Thank you for responding.

    Tuesday, March 7, 2017 9:29 PM