How can I offer clients ODBC access to an online database - maybe webservices?

Posted on 2011-10-07
Last Modified: 2014-11-12
I currently have an inventory control system with about 70 companies using it.  Several have requested ODBC access to the data.  Since the products table, for example, has the products for all 70 companies, then I can't exactly just give them a connection string!  
To handle things like QuickBooks Integration, I have written web services and a VB application to run the interface, but that's really just a set of functions.  

Is there a way I can write a web service which they can connect to using something like MS Word for a mail merge?
Question by:Danielcmorris
    LVL 38

    Assisted Solution

    by:Jim P.
    There are several ways that you can go. None is really efficient.

    You can create a download system that dumps the data to text or xml files.

    You could create a Word or Excel file that connects to a webpage. You provide that to the customers to use as a source for their mail merge.
    LVL 22

    Accepted Solution

    You could create a separate user for each company. Then you create a view that uses the current user as part of the where clause, so only the rows for a specific company are returned by the view. Then you give these users only permissions on the view and not on any other objects in the database.
    I think you should be able to find a lot more info if you search for:
    "row level security" and "sql server"
    Not sure though if this is possible for Azure also.
    LVL 32

    Assisted Solution

    +1 for the views. And it is possible with Azure, too.
    For performance, create some indexes on the fields that will be in the "where" clause.
    LVL 4

    Author Closing Comment

    So here's the final solution:  I did both.

    Clients who were willing to seriously kick up some bucks got a connection string directly to some views in Azure, but we managed to get Apidas to manage them along with our own access modules that we lease from them.  They'll deal with them from now on in exchange for a bulk rate.  

    For the others, I gave them an XML "file" on the server, but it is actually function picked up by a handler that pulls a recordset from the DB based upon an API key in the url.  They can use that as a source for a mail-merge or read-only linked tables in MS Access.

    So, both of you had exactly what I needed.  Thanks a lot!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Data center, now-a-days, is referred as the home of all the advanced technologies. In-fact, most of the businesses are now establishing their entire organizational structure around the IT capabilities.
    This is part 1 of a tutorial series on how to set up a Virtual Private Cloud (VPC) in Amazon Web Services.  The series goes over a multi Availability Zone configuration, with public-facing subnets (direct access to the internet) and private-facing s…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now