The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Reaching Out and Grabbing Data –
Attached Tables

In 2007, a terrific movie came out called “There Will Be Blood.” It told the story about an oil tycoon, Daniel Plainview, played by Daniel Day Lewis. The story tracks Plainview's rise from poverty to his ultimate success as a ruthless businessman sitting on top of an empire. Along the way, he ran into a young preacher who became key to the drilling rights for a sector of land that Daniel wanted to drill. The young preacher convinces him to join his church. In a public ceremony, the preacher has Daniel list his shortcomings and renounce his sins.
 
After a number of years, the preacher fell on hard times and approached Daniel to buy some property to help him get out of debt, explaining that the land had oil on it and therefore was quite valuable.
 
Daniel Plainview scoffed at the preacher and told him that his land had no oil left. Although there had been an abundance of oil in prior years, Daniel had been able to acquire adjacent property and use drilling techniques to suck the oil out from the preacher's property, as if from a giant straw. Slurp!
 
This ability to pull in resources from other places has great usefulness. Access does this as well. It can link into other data sources and slurp in data. It does this through a mechanism called attached tables.
 

The Beauty Behind Attached Tables

If you have an attached table, the data is living someplace other than in your own database. The attached table can be as simple as another Access table in the same directory on your hard drive, or it could come from a variety of sources as far-reaching as non-Access databases such as SQL Server, Oracle, File Maker, or even Microsoft Excel (provided the data is appropriately arranged)
 
The great thing about attached tables is that you get to consume data without necessarily having produced it.

The Mechanics

The key to using data that exists outside of your database is creating a connection. These connections fall into two broad categories: native Access databases and foreign sources, a SQL Server database, for example. The first is a fairly seamless approach using clicking and pointing techniques. The second, more complicated approach, allows a longer reach, but requires something called an ODBC connection, which we'll discuss in a future Wizard article.
 
Attaching a table is an easy process. If you're using Access 2007 or 2010, click on the External Data tab on the ribbon and you will see a section called Import & Link as shown below.
 
 
 
The easiest type of data to attach is in an Access database, but the steps are similar for Excel, text, and XML files. For our example, we'll use Access. When you click on the Access icon, you get the following dialog box.
 

 

From here, make sure that you have clicked on the link to the data source button, and then click browse. You will be taken to a window that will allow you to navigate to the file that contains your data. Once you find it, click okay and it will bring up a list of tables available for linking. Choose whichever tables you want and click OK again. For versions prior to Access 2007, you will find the same functionality under the File menu | Get External Data.
 
Once you have selected your files and clicked OK, Access will take a moment and then your table will show up in your tables list with a little blue arrow symbol next to it.
 

 

The image above shows three attached tables. The first table, tblEvents, is an Access database; the second, txtTemp, is a text file; and the third, xlsSheet1, is an Excel file.
 
Along the way, Access helps you to identify the files by having different icons for different types of attached tables.
 
Once your data shows up in the table view, you can click on it and read the data just as you would from any native Access table. You can open the table to see the data or consume it through a form, query, or report. The data is available for your use  just as with any other table in your database.
 

Warnings and Caveats

When you're working with an attached table, in some instances you will be able to add, edit, and delete data and; in other instances, you won't. If you do have the ability to add and edit data, be very careful as you use the table. Unless you are intimately familiar with the data, don’t make changes. Because it is a linked table, any updates you make to the data are live.
 
A competent database administrator will make sure that the rights you have by linking are appropriate. However, not all database administrators take the necessary precautions on this front, so when traveling this path, walk with care.

Conclusion

Using linked tables can vastly expand your horizon. You are not limited to local files on your hard drive, or even on your local area network; you can go all the way to the Internet to attach selected databases, which we'll talk more about that in a future Wizard. However, know that with these techniques, the amount of data at your fingertips is truly stunning.
 

 

 Trap of the Month: Importing vs. Attaching Tables

One of the most common mistakes that beginners make when attaching tables is that they import tables (the default action) rather than attach tables. If you import a table, you will get a copy of the data, rather than a connection to the data. If you end with this scenario, any changes to the data that take place in the live file will not be reflected in the data you see in your database. So, if you want a live feed of the data, make sure that you click on "link" to attach the table rather than import it.
 
On the other hand, if your desire is to just to get a snapshot of the data and then manipulate, massage, or change it, then certainly importing rather than linking is a reasonable approach.

 

Tip of the Month: 
Attachment Information

After you have linked a database and time goes by, you may well forget where the data is coming from. You will always know that the table is attached because it has the little blue arrow symbol next to it, but you may wonder where the data is coming from. If you hover your mouse over the table name, you will see a pop-up that gives information about the source file.

If you are using Access 2003 or earlier, you can get the information in a slightly more complicated fashion. Check out the Tip of the Month in the February 2006 Wizard.


 
Quick Links:

Custom Software Home

Access Wizard Archives

Our Services


© Copyright Custom Software
All Rights Reserved | Westford, MA 01886