Everyone knows that we can create linked table to external databases, but did you know you can actually directly query another database without first setting up a linked table?

It is actually possible to pass along the necessary information directly in the SQL Statement to establish a connection, even to a secured database file.

So, let’s explore how this is done.

The Basics

The basic concept is that we must configure the proper FROM clause to get Access to connect to another db.  The basic format being:

Unsecured Databases

...
FROM [;DATABASE=C:\YourFullPath\YourDatabase.accdb].YourTableName
...;

Secured Databases

...
FROM [;DATABASE=C:\YourFullPath\YourDatabase.accdb;PWD=YourPassword].YourTableName
...;

Practical Usage Examples

Below are a few examples of how this can be put into practice.

For these examples I’m pretending to be working with a contacts style database with table named ‘Contacts’ which has fields: Id, Company, LastName & FirstName.

Select Statement

Unsecured Databases

SELECT *
FROM [;DATABASE=C:\YourFullPath\YourDatabase.accdb].Contacts;

Secured Databases

SELECT *
FROM [;DATABASE=C:\YourFullPath\YourDatabase.accdb;PWD=YourPassword].Contacts;

Append Statement

Unsecured Databases

INSERT INTO [;DATABASE=C:\YourFullPath\YourDatabase.accdb].Contacts( Company, LastName, FirstName )
VALUES ('McDonalds', 'Kempczinski', 'Chris');

Secured Databases

INSERT INTO [;DATABASE=C:\YourFullPath\YourDatabase.accdb;PWD=YourPassword].Contacts( Company, LastName, FirstName )
VALUES ('McDonalds', 'Kempczinski', 'Chris');

Update Statement

Unsecured Databases

UPDATE [;DATABASE=C:\YourFullPath\YourDatabase.accdb].Contacts SET FirstName = 'Christopher'
WHERE Company = 'McDonalds';

Secured Databases

UPDATE [;DATABASE=C:\YourFullPath\YourDatabase.accdb;PWD=YourPassword].Contacts SET FirstName = 'Christopher'
WHERE Company = 'McDonalds';

Delete Statement

Unsecured Databases

DELETE 
FROM [;DATABASE=C:\YourFullPath\YourDatabase.accdb].Contacts
WHERE Company = 'McDonalds';

Secured Databases

DELETE 
FROM [;DATABASE=C:\YourFullPath\YourDatabase.accdb;PWD=YourPassword].Contacts
WHERE Company = 'McDonalds';

Make Table Statement

Pay attention here!  It is critical that you specify the fields in the Make Table SELECT clause otherwise you will receive the ambiguous error: “The search key was not found in any record”.

Unsecured Databases

SELECT T1.Field1, T1.Field2, T1.Field3, ... INTO LocalContacts
FROM (SELECT * FROM [;DATABASE=C:\YourFullPath\YourDatabase.accdb].Contacts) AS T1;

Secured Databases

SELECT T1.Field1, T1.Field2, T1.Field3, ... INTO LocalContacts
FROM (SELECT * FROM [;DATABASE=C:\YourFullPath\YourDatabase.accdb;PWD=YourPassword].Contacts) AS T1;

A Few Resources on the Subject

Access SQL: basic concepts, vocabulary, and syntax

Learn how to retrieve data from an Access database by using Structured Query Language, or SQL

SELECT statement (Microsoft Access SQL)

Instructs the Microsoft Access database engine to return information from the database as a set of records.

SQL SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

IN clause (Microsoft Access SQL)

Identifies tables in any external database to which the Microsoft Access database engine can connect, such as a dBASE or Paradox database or an external Microsoft Access database engine database.