Back to the (Microsoft) Office...Today's cunning tip is that of linking queries between two Access databases.

It is easy to link tables in Access; that is to say that your Access application can use, edit, add and delete data that physically exists in a different Access application's tables. This allows you to have multiple independent front-ends for the same background data for instance, gain the ability to develop new forms and reports whilst other people are using existing ones, or have an application that uses data from two different databases. Do this via the "Link table" option you get, when you create a new table (Click the Insert menu, then Table). However at first sight it isn't possible to do this for queries.

You can import queries, sure (File menu, then Get External Data, then Import), but this creates a separate copy of the query in the second database in your database. This is a waste of space and more importantly risks confusion desynchronisation if one of the queries is changed and the supposed replica is not. It is the equivalent of an imported table, not a linked one.

When you know how, it is actually easy to create the equivalent of a directly linked query without knowing any advanced jibberish. There is a query property called "Source database" which allows you to get information from another database's query for your own query. So to make the equivalent of a linked query, you want to link to that database and command Access to give you all the data that query generates. Do it like this:

1. In the database you're currently working in, select the Insert menu, then Query. Click OK to go into design view. When it shows you the tables and queries in the current database, just click Close because you're only interested in queries in another database.
2. In the resulting blank query screen, if you cannot already see the Query Properties window, go into the View menu and select Query Properties. One of the properties is called "Source database" and is almost certain to say (Current) at present. Simply type the path to the database you wish to get a linked query from, as in the example pictured on the below.

The "Source database" property

3. Chose the Query menu, and select Show Table. The resulting screen will give a list of all the tables and queries in the remote database.
4. Select the query you want to link to, and click Add.
5. Now drag the * from the query now shown on the query design screen into the first Field row to ensure that all the data is displayed as below. You can test it by running the query via the Query menu by choosing Run.

Selecting to show all the fields

6. Save this query, probably using the same name as the query in the remote database to help you remember what it is.

Voila, a linked query that updates as soon as the query in the other database is altered.

SQLGeekness: This sort of query can also be written dynamically in VBA etc. via SQL. You can use the "IN" keyword to let the computer know what database to search for. As an example, the equivalent to the query pictured above can be written as:
SELECT Query1.* FROM Query1 IN 'C:\Documents and Settings\Adam\Desktop\db1.mdb';