ACC: How to Query an ORACLE Table Without Attaching It

• 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.

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

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

2. Create a new, unbound query.

3. From the View menu, choose SQL.

4. 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";
   

5. 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