Introduction
How do you get from one side of the river to the other side of the river? Well, if there is a bridge or a tunnel, it is pretty simple. If there is no bridge or tunnel, then you hope that there may be a ferry. If there is is no bridge, tunnel, or ferry, then life becomes much tougher. You can ford the river, but that is uncomfortable, time-consuming, and wet. Wouldn’t it be great if, every time you came to a river like this, you could create a temporary low-cost bridge that you use once and then it disappears – and do that for every river again and again and again?
In this month’s
Wizard, I will show you how to build a temporary data bridge. I’m not talking about a linked table – that is permanent; you probably already know how to do that. I am talking about something temporary that disappears once you are finished.
Why Create a Temporary Bridge?
When you have a linked table, any user that can get to the Navigation Pane can make their way to the table and change or delete the data. Although there are ways to hide linked tables (look for techniques to do this in a future
Wizard), a clever user may still be able to find them.
We might say that you can always remove a linked table on the way out of the database. That is true, but what happens if a user hits a bug that prevents your closing code from running? If you have a linked table that you intended to delete, then that table is exposed when the database reopens. The same logic holds if you plan to immediately drop a table one you have your linked data. If you link the table and then get a bug that terminates the code prior to dropping the table, your linked data is exposed. If you are dealing with highly sensitive data, you now have a hole in your security. The temporary bridge solves this problem.
Sometimes you might want to get data from the same table across multiple databases or Excel worksheets. This can be a very tedious process that is easier to solve in code than by manually linking tables, a process that takes longer than building a single temporary bridge.
And sometimes you may want to obscure certain sources for data, such as a classified or private database. By using a non-linked table you can create the bridge, grab the data, and the table never gets linked.
The Details
To create a temporary bridge to a table in another database, open a new query in design view. Right-click in the white space in the top of the query window and click on property sheet from the window that pops up. At this point, you should see a window similar to the one below.
If you have been working with Access you will know that the property sheet is where you can make selections to specify properties of the query that are not directly available in the query by design window. The item called source database will say “(current)” by default. This particular parameter is telling the query to look in the current database for any table or query that you might be referencing.
We can use this property to point to another database. In the example above, I am pointing to a database that I sometimes use to create examples for
Access Wizard articles.
Know that the database you want to get the data from is not limited to another Access database or even a database at all. You can use that same technique to go after Excel sheets, CSV files, or other sources of data.
Once you put the correct path to the database into the property sheet, go to the SQL view and type the SQL statement there. To get the SQL view, click on the view button in the upper left-hand corner as shown in the picture below.
At this point, you will be presented with a screen that says Select where you have to type your SQL statement. This can be a very persnickety process, so it is easiest to start out with select * from MYTable substituting the name of the table holds the data that you are interest in.
In my case, I typed: “
SELECT * From tblActivities” (without the quotes).
Once you type that in, click the Run button and, if everything works properly, you will get data back from your source database.
From there, to go back into the SQL view and you will find that the SQL engine has translated your statement from the parameter dialog box into something like the following:
SELECT tblActivities.* FROM tblActivities IN 'J:\Data\Access\Wizard\Wizard2010.accdb';
The big difference between this statement and the typical SQL statement is the use of the word
IN, which indicates that the SQL engine is just pointing to a different database.
Now this is a tough process to get the SQL statement exactly right, especially if you don’t typically write them out by hand. To help with this problem, take a look at this month’s
Tip of the Month for an alternate way to do this.
An Added Bonus
With the technique above, swapping out the name of the external database will pull data from another data source. The implications of this are rather significant because, if you have five or 10 or 100 of the same data to get to from different databases, you can create a series of SQL statements that you manipulate in VBA code that swaps out the name of the table to use for the query. From there it is simply is a question of grabbing the data that comes back from your query.
Conclusion
Using techniques I have shown you this month – grabbing data from an unlinked table – allows you greater security. If you provide your customer with an accde, rather than an accdb, then the users are not able to get your code. As a result, you have a fairly secure method that prevents users from determining where the source database lives.
