MS Access Queries: How To Add An Ad-Hoc Linked Table To Your Query

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.ms access queries

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:

[C:\Users\Ben\Documents\Employees.accdb].Employees

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):

SELECT *

FROM [C:\Users\Ben\Documents\Employees.accdb].Employees

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.

SIGN UP AND GET FREE EBOOK
SignUp Now and Get FREE EBOOK in your inbox and Every Friday, we send out amazing Tips and Tricks to help you master Microsoft Access.

4 Replies to “MS Access Queries: How To Add An Ad-Hoc Linked Table To Your Query”

  1. Aw, this was an incredibly good post. Taking a few minutes and actual effort to
    create a really good article… but what can I say… I hesitate a lot
    and never manage to get nearly anything done.

  2. You are so awesome! I don’t suppose I’ve read anything like this before.
    So wonderful to discover someone with unique thoughts on this subject.
    Really.. thank you for starting this up. This web site is something that
    is needed on the internet, someone with some originality!

  3. This is really interesting, You’re a very skilled blogger.
    I have joined your rss feed and look forward to seeking more of your excellent post.
    Also, I’ve shared your site in my social networks!

  4. Just wish to say your article is as astounding.
    The clearness on your publish is simply excellent and i could think you’re a professional on this subject.
    Fine with your permission let me to grab your feed to stay up to date with impending post.

    Thanks one million and please keep up the enjoyable work.

Leave a Reply

Your email address will not be published. Required fields are marked *