Creating an ODBC data source

Make sure you have Oracle ODBC software installed on your PC before you start, and a suitable tnsnames.ora file in the oracle/network/admin folder to identify local database hosts. You can pick up the tnsnames.ora file (if you find you need it- for example if you get unrecognised service name) from a previous installation or from Maggie Shapland and paste it into the newly created oracle/ora90/network/admin/ folder making sure the old tnsnames.ora that was created by default if you did not exit, is renamed first. For each connection, the entry looks like (where datasource alias, machine_address, and database_name must be specified): 

datasource alias=
  (DESCRIPTION =
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = machine_address)(PORT =1521))
    )
    (CONNECT_DATA =
      (sid = database_name)
    )
  )
eg 

hprod.bris.ac.uk = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
        (ADDRESS = 
          (COMMUNITY = tcp.bris.ac.uk)
          (PROTOCOL = TCP)
          (Host = sunshine.bris.ac.uk)
          (Port = 1521)
        )
    )
    (CONNECT_DATA = (SID = hprod)
    )
  )

Testing the connection

  • select the DOS command prompt from Accessories
  • Type tnsping hprod
    (one of the Bristol databases, otherwise type the name of one you want to test). It should identify the connection and confirm OK

    Creating a Data Source

    You need to create a data source to be able to use Access as a front end to Oracle

    Accessing Oracle Data

    To access Oracle data, create an external link.
    Attaching data means that you are using an external link to the data at its source. Creating external links to Oracle tables keeps the Access view up-to-date, avoids duplication of data, and does not require extra storage space within the Access database. Attached tables are slower to access than locally stored tables, and their design can not be changed.

    Identifying an existing connection definition

    If there is already a Microsoft Access database with links to an Oracle database, but the data source has not been defined, up, there will be a suitable warning message telling you that the ODBC connection has failed when you try to look at the data.
    Look at the design of one of the linked tables (you must select the design icon and then look at the properties of the table, rather than looking at the properties of the table from the main database window). Linked tables are recognisable from the icon associated with them- Oracle tables have a globe icon. You will be warned that it is a linked table and that you can not change the structure.
    Example properties of linked tables This shows that ODBC was used to look at the data belonging to the law_username username using database alias hprod (the name before the description in the tnsnames file which accesses hprod database on the sunshine machine), the data source was called law, the name of the table/view was person_units, and username law_user.
    This indicates that to be able to use the Access database to look at the data (and update it if the appropriate permissions have been given by Oracle), that one needs to set up a datasource called law, use the identified username and database, and that the database/service has been correctly defined in the tnsnames file.

    What to do if the data source no longer works

    Use tnsping hprod (or whatever the database connection is called), as described above and check the details match the tnsnames file. tnsping uses the Oracle named server to find the connection details, whereas Microsoft Access tends to use tnsnames.ora, so the details should be consistent. Database administrators may have a habit of moving databases to new machines so do check