• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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

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?
3 Solutions
Jim P.Commented:
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.
Nico BontenbalCommented:
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.
+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.
DanielcmorrisAuthor Commented:
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!

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now