ODBC Page describes problems using Access as frontend, ODBC Configuration, year 2000

---

Useful Information

• Use Data Sources (ODBC) from the Administrative Tools section of the Control Panel if you can not find the ODBC administrator
• Oracle Database Name resolution by LDAP- an Introduction
• Connecting to the Datahub (gives ORA files)
• New training document: acc-t11, Accessing the DataHub using ODBC (39 pages) PDF  Word 
• How to create an ODBC data source to access Oracle data
• Importing Data from Excel into Access. It is not possible to export directly into Oracle from Excel since ODBC is not listed as a possible file type. Export or link into an Access database, and then export into Oracle. It is however possible to get Oracle data directly into a spreadsheet using Data/Get External data/new database query
• Comparing Oracle and Access SQL (Jun 98)
• ODBC Year 2000 Information (Dec 98)
• Converting from Access to Oracle (mod Jan 2004) contains useful information about names, data types, speeding up applications, problems etc. Also contains a useful Oracle script to rename column_names so that they are useable in Oracle.
• To convert Access yes/no fields to Oracle use:
  decode(ENGLISH_TEST_REQUIRED,0,'N','Y') in your Oracle script
• Access section with tutorials and solutions- includes how to create an autocounter (cannot use an autocounter as a default in Oracle)
• ADO Connection String Samples
• Oracle ODBC FAQ
• A conference paper I presented (Oracle User Group Sep 95) about interfacing Access 2 with Oracle 6. Gives timings and hints and tips on speeding up queries and forms (relevent to any version of ODBC and Oracle), error mesages, comparisons of Access and Oracle datatypes, MSysConf etc.
• Interfacing Access with Oracle . Powerpoint summary of ODBC points and some more timings
• Passing data between Microsoft Office products and ORACLE Word
• To restrict the list of linked tables to avoid seeing the whole of the system catalogue, create an ALL_OBJECTS view and ALL_SYNONYMS view in the user's schema.
  --NOTE that this technique has the limitation that only objects actually owned by the view user will be viewable. For example, if the user has a synonym that points to an object in another user's schema, it will not be visible
• Query times out with 1013 user requested cancel of current operation.
  • set connectiontimeout and query_timeout to 0 using regedit
  • check configuration parameters of data source with ODBC Administrator- disable enable query timeout, enable LOBs, enable Result sets, enable Disable MTS support
  • look at Tools/options/Advanced- you may want to decrease the ODBC refresh interval
  • Look at the properties of the query and set ODBC timeout to 0. By default it is set to 60 seconds
• Object invalid or no longer set someone has changed the definition of the linked table- it may not always be cleared by the linked table manager especially if the name has been changed- in this case, reattach the table and redefine the links.
  Another reason is that the data types of linked tables do not match local tables exactly eg Oracle data type number(12) does not consistently match Access data type decimal 12 Access data type decimal 12 and may cause this error (Access long integer only maps to number(10,0). Joins seem to work on Windows 2000 but not Windows NT (same database, same version of Access, same version of ODBC!)- one either gets the object error inconsistently when one tries to save the design or one gets no results at all (again inconsistently). To get round, this you should always define the oracle data type as number(10) to link to Access long integer on a local table.
  This problem can also result in no current record error
• The data values show as #deleted in a query or a form (but not in table view or on a report!). Try refreshing the link to the tables. The other problem may be that you may have specified the wrong primary key (can happen if the table is actually an Oracle view). If you have specified the correct primary key and the table is read only, try reattaching the table making sure not to specify a primary key when asked.
• SQL Plus Worksheet seems to have been introduced on the Oracle 8i disc. It is found under the database administration set of programs. It is very confusing to use. It is far better to use SQL Plus from the Application Development set of programs

Installation and Configuration

Oracle 11 client

• Installing Net10 and ODBC on Windows2000 or XP
• Oracle Client 10.2.0.1.0 can use LDAP directory rather than Oracle Names. Edit LDAP.ORA and SQLNET.ORA. as described in http://www.bristol.ac.uk/is/computing/applications/infosystems/datahub/v2technicaldocs/connectingv2.html
• to install the Oracle 10 client on an XP machine that already had Oracle 9 client on it will break all the existing ODBC connections - they can not be edited, only deleted (reinstalling Oracle 9 will enable existing connections to work again) and recreated it using the Oracle 10 ODBC Driver.
• When Testing Connection if you got an error "Resolving ORA-12705: Cannot access NLS data files or invalid environment specified", you need to verify that NLS_LANG is "unset" at the system-level:
  * Windows - The NLS_LANG must be unset in the Windows registry (re-named is best). Look for the NLS_LANG subkey in the registry at \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, and rename it." Rename all such keys as there may be several, and Voila! it works.

Oracle 9 client

• The Oracle 9i CD installs happily on XP- it works even if the database is still version 7 and you are not using Oracle Internet directory.
• You should have Oracle 9 ODBC installed with the security option
• Installing Net9 and ODBC on Windows2000 or XP
• How to remove data source names from the registry. This is useful if you have mangled your ODBC installation and could not deinstall previous versions
• Installing Net8 and ODBC on Windows2000 - includes setting up a data source.
• If you are using Windows 2000, make sure you download the latest version of ODBC from http://technet.oracle.com at least 8.1.5 (CD with 8.1.5 for NT just hangs when you test even though tnsping OK)
• https://www.bris.ac.uk/is/internal/projects/oracleprocs/oraclenames/files" University sqlnet.ora files to allow use of central Oracle Names servers
• While establishing connection for the first time to the database, you get error Error cannot load resource file sqresus.dll
  • Use the DOS prompt to test the connection using tnsping connection_name
  • If this is successful, look at the user path by typing path. This should show \oracle\ora90\bin first
  • Look at the system path by looking at the environment variables of My Computer. This should be the same as the user path
  • If there is still a problem, copy sqresus.dll, and oranmsp.dll from \oracle\ora90\bin to the \winnt folder

Oracle 8 client

• There is a bug in the installation of the Oracle8 client (8.1.7). The bug affects Pentium 4 systems, causing the software to refuse to install. To get it to work copy the installation to disk (or server) and rename all occurrences (of symcjit.dll to e.g. symcjit.old. The software will install and work. I have it running on W2k and XP.
• If you are installing Oracle Client 8i (8.1.5) on Windows 2000 make sure that there is no space in the directory filename. By default, it may try to install in the Program Files folder. If you accept this you get the message:
• Net8 Configuration Assistant "failed(see details..." (details: Class not found:Files\Oracle\ora8i\network\jlib). Net8 Easy Config "succeeded". The ODBC driver will still work if you can copy listener.ora, sqlnet.ora and tnsnames.ora from another machine into network\admin. tnsping, sqlplus and odbc administrator will work, but odbctest and net8 configuation assistant will not work. This error arises because Java Installer doesn't manage space in installation path.