Using External Data | Beginning Access 2002 VBA (Programmer to Programmer)

So far in this chapter we've looked at how to get data into and out of Access, but we haven't addressed the point that imported data isn't live. In other words, when you import a set of data you'll be unaware of any subsequent changes that are made. In order to get access to live data, you have to link objects into Access as tables. They look like tables, they act like tables heck, they even smell like tables. But in fact, they are really just a pointer back to the original data.

This technique allows you to link into Access a variety of other objects. This is pretty important, because it allows you to use data that might not be yours to control. Other departments, for example, might not want you to control the data, even if they do let you access it.

The Database Splitter

One quite useful advantage of this linking is the ability to split your database into two without damaging it. There's actually an add-in supplied called the Database Splitter . We're not going to run through this here, but it is worth mentioning why it's a good idea.

Let's take a typical business scenario. You've just developed a database for use in your department. You've spent months developing it, and have finally rolled it out. It's stored on your central server, so there's only one copy, and everyone has been using it - the data has started to accumulate quite rapidly . It's been a few weeks, and then your boss comes to you and says 'I want you to add this feature. By tomorrow.' Great. Now you've got to stop everyone from using the database, because you have to make the changes on the master, since it's the one with all of the data. They all complain, and you're now under pressure. Not a good way to work.

But, what if you've got two databases? One with all of the tables, and one with everything else - forms, queries, reports , and so on, with the actual data tables just linked from the other database. You can quite happily make changes to the forms database without affecting the data.

In fact, when redeveloping your front end, you can have three databases:

The Data database. This is the one whose structure almost never changes.
The Live forms database. This is the one the users use.
A Test database, which is a copy of the Live database. You can make your changes to forms, etc., without affecting the users. You can test against the live data if necessary, just by linking in the tables.

In this situation, once you've made the changes to the test database, you can just copy it onto the server, overwriting the Live forms database. The tables are linked, so no data is lost. The users will only have a few moments delay while you perform the copy, and then they have the new changes.

This sort of scenario is used quite often. Many companies start using Access as their main development tool, and then grow beyond its capabilities. So they move their data into a bigger database, such as SQL Server, but leave the front end, the forms, in Access, and link the tables from SQL Server. This way the only cost, both in terms of time and resources, is the movement of the data, not the redevelopment of the whole application. This is one of the reasons why Access now has the ability to work with MSDE and SQL Server in a much more integrated way.

Linked Tables

Since we've talked about linked tables, it's about time to give them a try. Let's use the Sales.xls spreadsheet we created earlier. If you've deleted it, you can simply recreate it from the import and export form.

Try It Out-Linking a Spreadsheet

Open frmImportExport in design view and add another button. Call it cmdLink and give it a caption of Link Spreadsheet .

In the Click event, add the following code:

   DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _     "SalesFigures", "C:\BegAccessVBA2002\Sales.XLS", True     MsgBox "Spreadsheet linked"

Go back to the form and, in Form mode, click the New button.
Then, have a look at the database window:

Notice the new table, the icon to indicate its source, and the arrow, to indicate it's a linked table. There are a few things you can't do (such as deleting data), but otherwise this behaves exactly like a normal table. If you change data, you are actually changing the spreadsheet. There's only one copy, and you've just got a link to it.

You can delete the link anytime you want, and it doesn't delete the original spreadsheet. All it does is delete the link. It's a bit like shortcuts in Windows - if you delete the shortcut, you don't delete the file it points to.

This technique is quite useful as it allows you to have a copy of the data on a central server, but it allows users to have a copy of the front-end database on their local machines. This would mean that it's quicker to open the database because it's stored locally. In fact, one often-used technique is to store some tables locally as well. These would be tables that change very rarely, or not at all. So the only data stored centrally is the data that changes frequently.

Differences between Linked and Local Tables

Linked tables have several advantages over local ones:

You can store the data in its most appropriate location. For example, if the data is supplied by the finance department, and they are happier working in Excel than in Access, you can let them work in Excel. Linking the spreadsheet into Access lets you use the data as if it was an Access table.
Linking tables allows you to access data you don't own, but need to use. This is especially true as companies start using data more for decision making.
They allow you to separate your data from your user interface, allowing easier maintenance.

There are, however, certain drawbacks of linked tables:

Linked tables aren't part of your database, so the records have to be retrieved from another file. This could lead to speed problems if the source of the data is on another machine on the network.
Linked tables must be opened as dynaset or snapshot-type recordsets, and they therefore don't support the Seek method.
You must be careful when joining tables from different places, such as one local table and one remote table, as the field type may not be completely compatible. JET 4 has reduced the possibility of incompatibilities, but you should just be aware that this could be a problem.
You should be careful when joining large remote tables to small local tables. If both tables are local Access can optimize the join, but if the large table is linked, all of the data must be brought across the link before the join can take place. This can lead to speed problems.

Don't let the above put you off linking tables, or even trying the database splitter with its back end/front end approach. This is the first step towards client/server systems, and can bring some big benefits.