How can I use multiple Databases from a CS app using ODBC

  • We are moving from AS/400 DB2 to SqlServer and in that process I've run into a problem.

    The setup is like this:

    Our applications is client server Windows programs using ODBC to maintain data.

    we have a database containing common data and a number of production data databases

    The problem:

    When a user connects to the SQLServer through ODBC the user should have access to one of the production data databases and the common data database, so that all tables from the 2 databases is accessible.

    is this possible

    regards

    Henrik Holm, JL-Data

     

  • The login has to be granted to access both databases in SQL Server with proper permissions.

  • Using your description the user would need multiple ODBC setups. 

    Common Data

    1 for each Prod DB.

    OR

    If you are using VB, have the app open a direct connection to each. Avoid ODBC if you can.

    OR

    If you don't want 2 connections, you should be able to open a connection to the prod DB, and issue a "Select CommonDB..table ......' to get data from the common table.  You would for either of these need to grant the user access to each db.

    OR

    Create views in each prod db referencing the tables in the common DB.

    Still need an ODBC setup at each client for each prod db in either of the last 2.


    KlK

  • Your last possibility sounds usable. I wasn't aware that I could crease a view in one DataBase referring a table in another DataBase. In fact since my programs does all data access using views this sound really neat.

    Thanks

    Henrik

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply