ACC: How to Query an ORACLE Table Without Attaching It (109824)

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:

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.