MS Access Queries: How To Add An Ad-Hoc Linked Table To Your Query
How well do you know MS Access queries? Here’s another way you can communicate with a table that does not reside within the same database without the need to officially link a table using either the Linked table manager tool or via the Linked table method.
Some have said this is Microsoft Access queries on steroids! I think MS Access database is generally on steroids in any event especially when users want to know the difference between this powerful application and the more commonly used Microsoft Excel.
It is very easy to create and below are the simple steps…
MS Access Queries: How To Add An Ad-Hoc Linked Table To Your Query – The Steps
Make sure you have your external database ready by knowing the full path and database name. For example, I have a database called ‘Employees.accdb’ located in the path ‘C:\Users\Ben\Documents\’ and this will be the reference for my SQL view query which is the required design view to create this type of query.
The table in this external database is also called ‘Employees’ and therefore the full path to connect to a table via this query will look like:
Notice the square brackets wrapped around the path and filename to encapsulate this as one referenced object. In some instances, you will also see square brackets wrapped around the table name too should it contain any spaces like [Employee Salaries].
In a blank new query, switch to the SQL view mode and start typing the SQL language required. In my simple example, I want to show all fields and all records from this table and sort the recordset by the [Last Name] field (not square brackets again here):
ORDER BY [Last name];
Name spaces are ignored and can be written all on one line should that be your choice. Also, the last character ends with a semi-colon and can also be omitted.
If you typed the path correctly to your database sample, it will show the records in the normal view.
There you have it – simple!
The performance MS Access queries may drop if this has to first connect over a LAN (local area network) of create and cache the object from opening the external database first time around but with most modern connections and hardware, you shouldn’t suffer too much.
The added thing of course is to return only the actual records required which means adding criteria too and that in turn will help with the performance.
Become a master of Access queries with my eBooks – all come with a money back guarantee and 30 day email support.