Queries On Steroids ... Part II
December 15, 2006
The key words for today's query tip are simple and dynamic.
There are two ways to read data from a remote Access database:
No, I'm not pulling your leg. Today we'll see how you can write a query in Access that points to and opens data in a remote Microsoft Access database without linking to it first. I've created a demo database that pulls data, simply and dynamically, from a PubsData database and you can play with it yourself in this month's download.
However, this month we are not going to make you wait to see the code. It's so simple, you can virtually understand every nuisance by considering the following screen shot from the Query Design Window (in SQL view).
To pull data from the sales table in the PubsData.mdb database, you simply point to its network location. Since this file is in a folder named C:\Articles\QueryTricks\, we simply add that to the query, so the sql becomes ...
SELECT * FROM [C:\Development\Articles\PubsData.mdb].[sales] ORDER BY ord_date DESC
That's all there is to it. You now know 90% of what is in the demo. The only thing left to discuss is how you might leverage this nifty little trick in your applications.
What about Performance?
The first question you should be asking yourself is "How's the performance of this approach?" Frankly, I don't know. I have never actually seen a performance issue when using these queries, but then again, I have never built an entire application with them. (Something I wouldn't recommend, though I can't give you any evidence why.) Here's a link to a Microsoft KB article that will tell you much more than I aspire to in this article, and below is the only mention it makes of performance.
Attaching tables has powerful performance and administrative advantages -- especially if you are executing queries repeatedly. However, sometimes you might want to take the slower route described in this article when you need to use ad hoc queries that encompass two or more databases. This article explains how to construct these slower cross-database queries.
So, there you have it. Attached tables have a performance advantage, but this approach is great for ad-hoc, cross-database queries. The article includes additional syntax for creating ad-hoc odbc queries and explains additional syntax for Access databases with security implemented.
Where to use Simple-Dynamic Queries?
The demo code for this article (see screen shot below) suggests two ways you can implement this idea:
1. Saved queries that point to remote database tables
2. Dynamic rowsource SQL for combo and listbox controls
The top listbox shows a bunch of saved queries that all begin with the text Remote_Table_. Upon opening, my code loops through all QueryDefs of the database and when it finds one of these, it rewrites the SQL and points the query to the PubsData.mdb file in the same folder as the current db. Thus, the app contains six queries that are "linked" to the PubsData database and may be used as you would any tables: adding, editing or deleting records as you see fit.
The bottom half of the form displays two listboxes whose row source properties are set dynamically, based upon the file path in the text box just above them. It defaults to the PubsData.mdb, assuming that you will unpack the full contents of the zip file into a temp folder. However, you can enter any valid path to an Access database and view the catalog of local tables. Select a table and the listbox to the right is loaded with the TOP 10 records and left 4 columns of data. Double click on a table in the catalog, and a saved query is modified to point to that table and the new query is opened.
As an aside, the catalog of tables is easy to find because every Access database includes a table named MSysObjects, which includes all of the tables, forms, etc. in the database. By filtering for Type=1 and excluding tables that start with MSys, we get a list of your special, local tables.
Give it a Try
This is one of those "hip pocket" tools that you should just keep in mind when thinking about pulling data from remote data sources. Sure, there's a performance hit and I wouldn't build an application with this paradigm, but I'm sure glad Microsoft included this functionality.
Part II of Queries on Steroids was short and sweet, so if your appetite for Query Tricks isn't quite satiated, stop by next month for Part III: Fancy Filtering Techniques. If you have questions or would like to suggest a query topic, email me at Danny Lesandrini with your comments.