ACC: How to Query SQL Server Tables Without Linking/Attaching

 Last reviewed: May 20, 1997
Article ID: Q101375
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 SQL Server table without linking (attaching) it.

MORE INFORMATION

You can use IN to query only one external database at a time.

The IN clause requires two arguments: database name and connect string. When you link SQL Server tables, you must specify an empty string for the database name. The connect string consists of the following items: 

   Data Source Name (DSN)        The name of the SQL Server computer

   Database Name (DATABASE)      The name of the SQL Server database

   Workstation ID (WSID)         The name of the workstation that is
                                 making the connection (optional)

   User ID (UID)                 The login ID*

   Password (PWD)                The user password*

    * This item is optional within the connect string. If it is not
      present, the system prompts the user with a dialog box to enter this
      information the first time the macro is executed each session.
The following is a sample connect string to SQL Server. Note that it starts with "ODBC," which tells the system that it uses the ODBC driver (as opposed to an installable ISAM driver). 

   ODBC;DSN=ServerName;Database=Pubs;WSID=Wrk1;USID=JaneDoe;PWD=ABC123
The following example assumes that the ODBC driver manager and SQL Server driver are installed, that a data source called "Corp" was set up using the ODBC driver manager, and that the table "authors" can be successfully linked from the database Pubs, using the Get External Data/Link Tables commands on the File menu (or Attach Table command on the File menu in versions 1.x and 2.0).

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

  2. Create a new query. Do not select a table.

  3. On the View menu, click SQL.

  4. Enter the following SQL statement into the SQL window: 

          Select *
      FROM authors
      IN "" "ODBC;dsn=Corp;database=pubs;uid=<login name>;pwd=<password>";

  5. Close the SQL dialog box and close and save the query.

NOTE: If you return to the View SQL window after saving the query, Microsoft Access may place additional characters in the IN clause that you must remove to avoid a syntax error.

REFERENCES

For more information using Microsoft Access as a front end, search for "SQL," and then " SQL Databases" using the Microsoft Access for Windows 95 Help Index.

For more information about the IN clause, search for "IN (SQL)," and then "IN Clause (SQL)" using the Microsoft Access for Windows 95 Help Index.

Additional query words: attach remote data source
Keywords : kbusage OdbcOthr
Version : 1.0 1.1 2.0 7.0
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.