Share via

Would like to connect MS Access to Oracle DB- Need Help

Anonymous
2021-02-16T05:26:26+00:00

Hi Team,

Thanks for support always.

I am working on MS access integration with Oracle DB. Would like to have realtime data from Oracle DB to Access DB.

This we can achive by using ODBC or using Link tables as per the mas access functionality.

Am having  Windows server 2012 R2 (64 bit operating system.) where ms access is running.

But am unaware how to to setup to connect to oracle DB? What are the places we need to do changes?

Help on this topic will be appreciated.

Thanks in advance.

Regards,

Nitin Chaudhari

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-03-29T07:14:22+00:00

    Headsup..

    Hi Tom,

    Thanks for support.

    Have tried to add NLS_LANG in  environment variable.

    But if I add variable; am unable to connect the DB. 

    If NLS_LANG is not added am able to connect to DB but Japanese characters are not shown correctly.

    Now I have added NLS_LANG=JAPANESE_JAPAN.JA16SJISTILDE

    And it worked. Now Japanese characters are displayed correctly. 

    Thanks for help and support.

    Regards,

    Nitin Chaudhari

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2021-03-29T02:47:23+00:00

    Hi Tom,

    Thanks for support.

    As suggested have installed ODBC drivers and able to connect to oracle DB.

    As per oracle support site have installed below.

    https://www.oracle.com/jp/database/technologies/instant-client/microsoft-windows-32-downloads.html

    -Oracle Instant Client 19.1 Basic Package

    -ODBC package for 19.1 Version

    Made changes in System Environment Variables

    - Path: C:\oracle\instantclient_19_10\

    • TNS_Admin: C:\oracle\instantclient_19_10\network\admin

    Am using link table to access the oracle DB from Access and successfuly able to connect oracle 11g database.

    But the Japanese characters(from Oracle DB) are showing Junk in MS Access.

    I have googled it; but cant get any leads.

    What could be the issue? 

    Regards,

    Nitin Chaudhari

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2021-02-16T13:46:02+00:00

    You will need to talk to the Oracle database administrator to see what kind of access you will be allowed. They may not want to give you access to the tables. Maybe only to a few stored procedures.

    Then you need an ODBC driver.

    If stored procedures, you will typically use a Passthrough query.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2021-03-04T04:19:07+00:00

    Re 1: ODBC drivers are installed on the local machine.

    Re 2: A version that is compatible with your version of Oracle. For the most part there are no severe restrictions, so I would start with the latest version of the driver from oracle.com

    I believe MSFT also has an oracle driver, or at least used to in the past. Not my area of expertise, but maybe you have two drivers to try and see which works best for your scenarios.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2021-03-04T01:59:45+00:00

    Hi Tom,

    Thanks for support.

    My sincere opologies for delay in responce.

    Regarding access to Oracle DB and table; we can manage by ourself.

    Actually It would be read only access; dont want to update anything from MS Access.

    But am not sure how to connect to oracle DB through ODBC and ODBC driver.

    1. Where to install ODBC Driver?

    Is it where the oracle DB exist? or From where MS Access would be connecting Oracle DB?

    1. What version of ODBC Driver do I need to install?

    It would be great help; if you can suggest for above so that I can coonect to Oracle DB.

    Regards,

    Nitin Chaudhari

    Was this answer helpful?

    0 comments