Accessing external data using the IN clause

Someone sent us a question the other day about one of my favorite dark corners in Access and we thought it might be interesting to dive into this area a little.

Microsoft Access SQL supports two uses of the IN keyword. The most commonly used case is as part of the WHERE clause of a SQL statement to provide a list of values used as criteria. Using the Customers table in the Northwind 2007 sample database as an example, this might look something like:

SELECT [First Name], [Last Name], [State/Province]
FROM [Customers]
WHERE [State/Province] IN ('WA', 'CA', 'NY')

This query returns the names of customers that live in WA, CA, or NY. This keyword provides a nice alternative to the OR clause which would require repeating the field name.

The other use of the IN keyword is as part of the SELECT statement or FROM clause, and is called the IN clause. The IN clause is used to access external data, and can be used in place of linked tables. For example, let's say that you had a SQL Server database that is used as part of an application, but you don't want to maintain a DSN to connect. You could use the IN clause in a query that uses a DSN-less connection to quickly read data from the external table. For example:

SELECT * FROM ExternalTable
IN '' [ODBC;Driver={SQL Server}; Server=ServerName; Database=DatabaseName; Trusted_Connection=Yes]

It is possible to create a linked table that uses a DSN-less connection, but creating the table requires code. Also, unlike a SQL pass-through query, this query is executed by the Access database engine which means that the resultset may also be updateable. Furthermore, there is a certain simplicity or elegance about this technique that I think is cool. Incidentally, there is another syntax for the IN clause, but I prefer this one.

So far so good, but since the IN clause is part of the FROM clause of a query, it can also be used other places where the FROM clause can be used. For example, in a make table query to create a local copy of data in an external table:

SELECT * INTO LocalTable
FROM SourceTable
IN '' [ODBC;Driver={SQL Server}; Server=ServerName; Database=DatabaseName; Trusted_Connection=Yes]

Or, in an append query using the INSERT...INTO statement to move data from the local database into another database. Note that the connection string used in this example is to another Access database.

INSERT INTO DestinationTable (DestinationField)
IN '' [;DATABASE= C:\Users\Rob\Documents\Northwind 2007.accdb]
SELECT SourceField FROM SourceTable

You could even use an append query with multiple IN clauses to concatenate two text files:

INSERT INTO [File2.txt] (Field1)
IN '' [TEXT; FMT=Delimited; HDR=YES; DATABASE= C:\Users\Rob\Documents;TABLE=File2.txt]
SELECT Field1
FROM [File1.txt]
IN '' [TEXT; FMT=Delimited; HDR=YES; DATABASE= c:\Users\Rob\Documents;TABLE=File1.txt];

These are just a few examples of how you could use the IN clause to quickly work with external data. By using different connection strings for the external data sources, you could do some pretty cool stuff.