Protecting Linked Tables Paths with Remote Queries

Protecting Linked Tables' Paths with Remote Queries

The best ways to protect and secure individual linked tables are by using the Hidden property (see Chapter 3) and by using workgroup security on tables in the back-end database. If you want to disguise your linked tables, consider trying the following remote query technique.

One of the more interesting ways to protect against people opening or importing directly from a linked table (see Chapter 4) in a front-end database is to replace your link with a remote query. These queries work by including a direct reference to the back-end database in the SQL of the query itself. To illustrate this process, have a look at the following SQL code of a query that will retrieve the Orders table from a remote Northwind database. The SQL extension that allows this query to work is the IN clause that follows the FROM Table clause in the query.

 SELECT Orders.* FROM Orders   IN 'C:\Program Files\Microsoft Office\Office 10\samples\Northwind.mdb'; 

To start experimenting with remote queries yourself, try the following:

1. Open any database, select the Database window, and choose the Queries object type.

2. Create a new query in design view and do not select any tables.

3. Choose View ˜ Properties.

4. Type the full path to the Northwind database in the Source Database field on the Query Properties dialog (as shown in Figure 11-4). The default term (current) in the property line refers to this database.

   
   Figure 11-4: Entering the full path to a database in the Source Database field.

5. Choose Query ˜ Show Table, and you will now have a full list of all the tables and queries that are in the source database (as shown in Figure 11-5). You should find this same button on your Query Design toolbar.

   
   Figure 11-5: A list of tables and queries in the source database.

6. Choose the Orders table and drag the appropriate fields down to the grid.

7. The query will now work as though you had actually opened the Northwind database itself.

Now that you have seen how you can write a remote query to remove a linked table from your database, how are remote queries actually a protection measure?

• You can use a remote query to remove a number (or all) of your important linked tables so that they do not appear in the Tables tab in the Database window or in the Tables list when they're imported from another database.

• Because developers and users rarely use remote queries, they offer a subtle way to hide the location of your back-end database(s).

• They let you secretly link to a second database.

• Because front-end queries can refer directly to queries in the back-end database, you can move queries to the back-end database. This action, which is something that linked tables cannot do, will stop the duplication of queries when more than one front-end database uses the same query.

• You can store the remote query in either VBA code or a form's RecordSource property, making it much harder for someone to locate the back-end database.

One interesting extension to remote queries is that you can use them on Microsoft Excel data, text files, SQL Server tables and any other data source to which Access can link. I have used the Excel extension in a number of my production databases with success.

Using Remote Queries in Normal Development

Where I use remote queries the most in ordinary applications is when I need to get or put some data into a database that is not the normal back-end database. If you were to add a link to a second back-end database, you would have to be very careful which tables you or your DBA users choose when you run the standard Access Linked Table Manager. If tables are selected that appear in more than one database, the Linked Table Manager will refresh the links one at a time and ask for the location of those links one at a time. This process can be very confusing for the DBA (and a certain Australian software developer). If you, too, want to experience this confusion, follow these steps:

1. Open Access and create a new blank database.

2. Link to two tables in two separate back-end databases by choosing File ˜ Get External Data ˜ Linked Tables, making a total of four links.

3. Start the Linked Table Manager in Access 2000 or later by choosing Tools ˜ Database Utilities ˜ Linked Table Manager. In Access 97, choose Tools ˜ Add-Ins ˜ Linked Table.

4. Select the Always Prompt for a New Location check box and select all the linked tables.

5. Click OK.

Now you will need to point individually to the correct back-end database for each and every table in the database. Naturally, in databases that have more than 10 different linked tables spread across two or more back-end databases, this process can get a little tedious . This particular trait of the linked table manager doesn't surface unless you link to more than one database.

How I generally approach external file issues like this is by storing the external database in the same relative folder path as the software database; that is, a subdirectory. Then, just before the query is run, I modify the remote path by using VBA code to suit the relative directory. This method works particularly well for spreadsheets and temporary databases.

Remote Queries May Require Additional VBA Coding

This manual design of remote queries is terrific when you are on-site, but you must be aware that there's no add-in like the Linked Table Manager to manage these database paths. If you want to read more on this topic, see the section "Further Reading" at the end of the chapter for information on how to reach a link to an article that I wrote on remote queries.