XE: ODBC connection to a remote XE server

How do you set up an ODBC connection to a XE instance on a remote server?

The basic steps are:
* Download and install the XE client on your Windows machine
* Create an ODBC DSN entry
* Create a new MS Access MDB and link two tables from XE (using the sample user HR)

First, you download the XE Client software from the Oracle Technet: http://www.oracle.com/technology/software/products/database/xe/index.html

Double-click on the downloaded file to install it:

During the installation choose to install the client software into a directory of your choice and finish the installation.



After that you can configure the ODBC entry. Goto Start > Control Panel > Administration > ODBC DatasourcesOn the tab System-DSN add another data source:


Choose Oracle in XEClient:


Enter the details for the connection to the remote machine, in my case the remote machine is daust3.opal-consulting.de

Data Source Name: choose an arbitrary name
Description: choose an arbitrary description
TNS Service Name: host:port/SID
User ID: Oracle user to connect to

The service name is critical here, the hostname is the name of the remote machine but it can also be an ip-address. The port is the port on which the Oracle listener is operating, usually 1521. The SID is fixed, it is called XE.

Then test the connection by entering the password for the user HR. The result has to be "Connection successful".


After that start MS Access (in my case I used MS Access 2000) and create a link to the tables stored in XE. You can do this either by clicking on New > Link table


or by right clicking in the table pane and selecting Link Tables ...


then choose ODBC Databases from the bottom of the list:


Choose the computer data source XE, which we just configured:


then enter the password for the user HR. You can also just overwrite the User Name with a different user you want to connect to:


then select the relevant tables by holding down the control-key and click on the table names. Then hit OK.


Voila! The tables are linked and you can start using them:


Just be aware of firewalls!!! If you cannot connect to the remote machine the reason can be that port 1521 is blocked by your firewall!

Update: 18.01.2007

Storing the password in the DSN entry

You can also store the password together with the username in the DSN entry. This way the login prompt won't show when you link tables in MS Access. This might be useful in certain situations.
Just use the username/password in the User ID field when configuring the DSN entry.
For example you want to connect as the oracle user HR with password hrpwd. Then enter HR/hrpwd as the User ID instead of HR.

Thanks to Jer for this tip ( post in the XE forum ) .


Update: 18.01.2007

Using tnsnames.ora with XE client

In the DSN entry you can specify the connection information to the XE server instance using the easy connect syntax, just as shown above.

But you can also use the traditional resolution via the tnsnames.ora file. This adds another layer of abstraction so that you can access the tnsnames entry in you VBA code and not hardcode the server, port and SID information into your code.

To accomplish this, the following steps are necessary:

First, you have to add the variable TNS_ADMIN to your Oracle configuration in the registry.
Start regedit and go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XEClient.



Then add a new string called TNS_ADMIN. Set the value to the directory where you want to store the tnsnames.ora file (you can also reference a directory of another Oracle install where a valid file is located).




Then create (or copy) the file tnsnames.ora and enter the following content:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DAUST3.opal-consulting.de)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

Then you can reconfigure your DSN entry and replace daust3.opal-consulting.de:1521/XE with XE.


Regards,
~Dietmar.