Post reply

setting up ODBC connections to get to linked server

  • July 14, 2005 at 9:09 am

    #93519

    HI. We have a microsoft access application that needs to get to oracle but is doing thru a linked server on sql server. The application does not need any access to any sql server database, only wants to get some data from the oracle database. The developer is not sure how to set up the ODBC connection for this application so that it can see the sql server linked server.  Can anyone direct me to some documentation or give suggestions on how to do this?

     Thanks so much,

    Juanita 

  • July 14, 2005 at 1:02 pm

    #574542

    Hello Juanita,

    Is there a reason why MS Acces has to go through SQL Server?

    If no, then use File ->Get External Data ->Link Table in mS Access and use ODBC source or if you have a driver for Oracle.

    If yes, you do have to go through SQL Server then you may try the following. It worked with my SQL Server linked server, may work with Oracle.

    1. Created linked server to whatever you need

    2. Set security - map local SQL Server login to whatever security you have on the remote server

    3. Login to SQL Server as SQL Server login that you mapped to remote server. Create a view in the database that is default to this SQL Server login. THis view will use 4 part name to get data from remote server like:

    CREATE VIEW dbo.LinkedServerView1

    AS

    SELECT     TOP 20 *

    FROM         remoteserver.databasename.ownername.mytable

    4. On the computer that runs MS Access create ODBC source that connects to the database from step 3 using credentials of SQL Server user in steps 2 and 3.

    4. In MS Access do the following:

    File ->Get External Data ->Link Tables Select ODBC from the dropdown.

    select your ODBC source, provide SQL login name and password. You will be presented with all tables and views from the default database of logged in user. Select the view you just created and follow the wizard to select fields.

    So to summarize steps:

    you created a linked server

    you created a view that goes towards this linked server to get data

    you created ODBC source on MS ACCESS computer to get to this view

    you use Linked Tables in Access to connect to this view that in turn selects from the remote server

    Yelena

    Regards,Yelena Varsha

  • July 14, 2005 at 3:12 pm

    #574603

    HI ! Thank you sooo much!!! It all worked making the 'dummy' view. We had to go this route because of not being able to set up an oracle client. Everything works perfectly!!

    Juanita  

     

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

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