Forum Stats

  • 3,874,651 Users
  • 2,266,759 Discussions
  • 7,911,926 Comments

Discussions

MS Access 07 ODBC Oracle 10g Express

730054
730054 Member Posts: 9
edited Nov 6, 2009 1:20PM in ODBC
Hi guys, spend a lot of time searching forums on how to connect Oracle with ODBC from Access and I am having trouble connecting.
Oracle 10g Express installed locally with static IP 192.168.1.2 with system name AnabolicPC but oracle control database html links to 127.0.0.1:8080 which works fine (loopback address)

So here's what I do:
Open Access 07, btw Oracle 10g Express running and I can login and type commands in SQL Worksheet no probs.
After that I click on External Data in Access then more>ODBC Database>Link to Data Source by creating a linked table (have to click that, part of a tutorial I have to do at uni)> click OK and
Select Data Source appears>then I select Machine Data Source> New> System Data Source> then from the list I select Oracle in XE> Next> Finish> Now I checked tnsnames.ora and XE seems right:
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = AnabolicPC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
But I cannot connect to it, I consulted ODBC Oracle help and followed instructions but still doesn't work. Vista x64 btw. The error I get is ODBC call failed.
Any help would be appreciated. Can't make any sense of other posts. I'm a newbie so step by step solution would be great.

Thank you in advance
George
Tagged:

Answers

  • 732649
    732649 Member Posts: 1
    Hi George,

    I'm having a similar problem on a Windows Server 2003 64-bit. I didn't realize the Oracle 10g Express had a 64-bit version to go with your Vista x64. I would recommend using the Standard 64-bit Oracle client instead of the Express Edition client - which I believe is 32-bit.

    -Sean
  • 730054
    730054 Member Posts: 9
    I much prefer going for express edition. I found a workaround with 64-bit OSes:

    1) Use 64-bit ODBC drivers or

    2) Follow this link which gives you detailed steps on how to do it with InstantClient
    [http://simeonpilgrim.com/blog/2007/08/01/remote-connection-to-oracle-10g-express-via-odbc/comment-page-1/]

    Thank you anyways
This discussion has been closed.