Linking Access to an External Data Source

There are many reasons an Access Developer needs or chooses to link an Access database to an external Data Source.  One common reason is to increase efficiency when a team of users need simultaneous access to a database across a Local Area Network. A common practice is to store an Access file containing the database tables in a shared folder.  This is referred to the Server or Back End.  Individual Users then have a local Access database stored on their own PC's which is linked to this Server.  Each one of these local database files contains all the Access Forms, Queries, and Reports, and are referred to as Client's or Front End's.  The logic behind this set up is that once data is downloaded from the Server, any processing that is required can then be done locally, thereby freeing the Server to deliver information to other Client's on the network.

In this exercise we are going to have a go at linking an Access Database to an External Data Source.   You don't need to be on a network to try this.  The general principle of Linking works exactly the same when the Client and Server database files are stored on the same machine, and even in the same folder.

Before you begin, you will need to create a new database containing a table. Call the database LinkTestServer.accdb. This will be the data source that we will be linking to.  A simple table of made up names will do fine for this.  Alternatively, you can download this example Link Test Server Database to use for the purpose.  Once you have done this we can begin by creating the Client database, and then link it to the Server file.

1. Open Access and Create a New Database.  You can save it in the same folder as the Server file.
2. Select the EXTERNAL DATA tab on the Access Ribbon.
3. In the Ribbon's IMPORT group, click the IMPORT ACCESS DATABASE icon.
   
   
   
   
4. This opens the GET EXTERNAL DATA dialogue box.  Here we need to browse and select the name of the Access Database that we are going to use as our Server or Data Source. You will also need to click the lower option box where it says LINK TO THE DATA SOURCE BY CREATING A LINKED TABLE.
   
   
   
   
   
5. Click OK.
6. The LINK TABLES dialogue box now opens. This lists all the tables in the Server Database.  In our example there is just one: tblCustomer.  Click the name so it is highlighted in blue.
7. Click OK to complete the linking process.

You should now see the linked table represented in the NAVIGATION PANE of your Client database.

As you can see from the screen shot above, it looks similar to an ordinary 'native' table, except there is a blue arrow to its left, indicating it is a linked table.  You can now open it from within the Client database and add additional names to it as if it was a native table.  You may also base Forms on it, Query it and Create Reports from it too.  It is just the same as working with a native table except you cannot modify the table design (eg add or change fields ... etc).  The table is still located externally, but any additions, deletions or edits you make to the table's data from the Client database is reflected in the data stored in the Server database. You may like to experiment with this and see for yourself.