Oracle to Access97 via ODBC: any ideas?

From: Gianluca *jlm* Meardi <gianluca.meardi_at_iol.it>
Date: 1997/12/12
Message-ID: <34916e23.30673742@news.vol.it>#1/1

Hi everyone,

        I'm trying to establish a connection with an Oracle db from Access97.
Now I use a local Personal Oracle 7.3.2 for developing purposes, in some time it'll be an Oracle UNIX server to do the job. I've installed latest version of Oracle ODBC32 drivers found on their Web site.
I use Oracle Instance Manager (from Enterprise Manager) to monitor the active connections to my Oracle db.
I linked all the tables I need from Oracle in Access: just after having linked them by Access menu, I can see and modify Oracle records: for now it's all ok. (yes, I checked the 'save password' option in Access link table panel)

If I now close and restart Access, when I try to view Oracle table records (either by VBA or by double clicking on a linked table) I get such an error:
"ODBC: connessione non riuscita"
 (='connection didn't succeed' in english). If I re-link the table, I can see values until I exit from Access again.
So I tried to open an Oracle connection by VBA code, using a very simple routine (self-invented, so sure buggy...) that *seemed* to work... Here is my code:

• - - - - - - - - - - - - - - -
  *** GLOBAL MODULE ***
  Public oraDB As Database, strConn As String
  • MAIN FORM *** Sub btnConnect() On Error GoTo errTrap ... strConn = "ODBC;dsn=My_DSN;uid=My_UID;pwd=my_PWD;" Set oraDB = OpenDatabase("", False, False, strConn) oraDB.Close MsgBox "ODBC Connection OK", vbInformation, "Oracle 7.3" Exit Sub errTrap: Select Case Err.Number Case 3146 MsgBox "Connection Problems..." Exit Sub Case Else ... End Select End Sub
• - - - - - - - - - - - - - - -

As I wrote, this 'strange' code seemed to work: after running it, I could well view my records, both by VBA and 'manually' viewing tables. But after some minutes not using the linked tables... boom! On next access to them I got Access frozen! Session Timeout, I thought: so I modified the registry value of the Jet/ODBC engine to increase the timeout value, but that didn't work.
Another time I kept an Oracle table opened (=on screen) in Access and, after some minutes, probably Access tried to refresh table values and printed '#Error#' in almost every table cell (almost freezing my PC, because every cell looked several times to Oracle before writing ERROR).
A strange behaviour was that not every cell was set to ERROR, but only the topmost records of the table, leaving old correct values in some records.

Might anybody direct me to the right solution? Thanx a LOT in advance,
 Gianluca

• Following are my system/software settings: ***

Toshiba 660CDT Pentium150 48MB RAM
Microsoft Windows 95 OSR2
Microsoft Access 97 (italian)
Oracle ODBC drivers 2.5.3.1.0
Personal Oracle 7.3.2.2.0
Enterprise Manager 1.2.2
SQL*Net Client 2.3.2.1.6A

TNSNAMES.ORA (created by SQL*Easy):
Toshiba.world =
  (DESCRIPTION =
    (ADDRESS_LIST =

        (ADDRESS = 
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = localhost)
          (Port = 1521)
        )
        (ADDRESS = 
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = localhost)
          (Port = 1526)
        )

    )
    (CONNECT_DATA = (SID = ORCL)
    )
  ) Received on Fri Dec 12 1997 - 00:00:00 CST