ACC: How to Query an ORACLE Table Without Attaching It

ID: Q109824

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes how to use the SQL IN clause to query an ORACLE database without linking (attaching) it.

In Microsoft Access, this can be accomplished more easily by using a SQL pass-through query. See the "References" section of this article for a reference to this method.

MORE INFORMATION

You can use the IN clause to query only one external database at a time. The IN clause requires two arguments: database name and connect string. When you attach ORACLE tables, specify an empty string for the database name.

The connect string consists of the following items:

Data Source Name (DSN): The name of the ODBC data source.

Database Name (DATABASE): The ORACLE connect string to the server. The ORACLE connect string consists of a prefix and a server name. (For TCP/IP users, there may be a prefix, a server name, and an SID.) The prefix is based on the network protocol. The following are the prefixes for networks that support SQL*Net for Windows:
```
      t:      for TCP/IP
      x:      for IPX/SPX
      p:      for Named Pipes 
```

Workstation ID (WSID): The name of the workstation from which the connection is being made (optional).

User ID (UID): The login ID (optional). If not present, the user will be prompted for the login ID the first time the macro is run.

Password (PWD): The login password (optional). If not present, the user will be prompted for the login password the first time the macro is run.

The following is a sample connect string to ORACLE. Note that it starts with "ODBC," indicating to the system that it should use the ODBC driver (as opposed to using an installable ISAM driver):

NOTE: In the following sample connect string, an underscore (_) is used as a line-continuation character. Remove the underscore when re-creating this connect string.


   ODBC;DSN=DataSourceName;Database=x:SrvrName;WSID=Wrk1;USID=JaneDoe;_
   PWD=ABC123

The following example demonstrates how to use the SQL IN clause in a query to query an ORACLE database without attaching it. Note that this example assumes that the ODBC Driver Manager and ORACLE ODBC driver is installed, that a data source called Corp was set up using the ODBC Driver Manager, and that the table called Employees on the server called Payroll can be successfully attached using the Attach command on the File menu:

Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

Create a new, unbound query.

From the View menu, choose SQL.

Type the following query in the SQL window, and then choose OK:


      Select *
      FROM Employees
      IN "" "ODBC;dsn=Corp;database=p:Payroll;uid=JSmith;pwd=KL3d";

Save and then close the query. Note that if you return to the SQL window after saving the query, Microsoft Access may place additional characters in the IN clause that you will have to remove in order to avoid a syntax error.

REFERENCES

For more information on the SQL IN clause, search for "In Clause" using the Microsoft Access for Windows 95 Help Index.

Microsoft Access "User's Guide," version 2.0, pages 273 and 276

Microsoft Access "Language Reference," version 1.1, Appendix B, "Microsoft Access SQL," page 513

Additional query words: attach remote data source

Keywords : kbusage OdbcOthr
Version : 1.0 1.1 2.0 7.0
Platform : WINDOWS
Issue type : kbhowto