Newsgroup: it.comp.lang.visual-basic
Titolo: Oracle to Access97 via ODBC: any ideas?

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)
    )
  )

Hi Gianluca,
Maybe I have 2 ideas on what might possibly help solve some ODBC problems:
1. I noticed that #Error occurs when the datavalues in a field exceed the
fields capacity. To solve this try building a query on the table, gradually
add more fields until the #error code results, then you can identify the
rogue field.
Also I notice that Microsofts ODBC drivers are More compatible with Oracle
than Oracles ODBC drivers are compatible with Access, so try Microsofts to
see if this gives the same problem. The only place I have found these
drivers is in the Visual Basic 5 installation CD (Enterprise Edition).
2. The problem you have with Access refreshing the links to tables, I have
seen only when trying to run pass through queries, so maybe you can get
around this by unchecking the pass through option ?
Just a couple of ideas, I hope some help.
Have Fun - Richard.

In article <01bd0a2d$c861f170$98675092@ye12203>,
One of the problems we found was an Oracle column name 
being a reserved word in Access gave #Error in Access.
I agree that the MS drivers are better. The Oracle driver 
dosen't seem to work with MSQuery in Excel but the MS 
driver is OK. They can be downloaded from the MS 
Universal Data Access web site.
David Hopkins
