Queries On Steroids … Part II

The key words for today’s query tip are simple and dynamic.

There are two ways to read data from a remote Access database:
    Create a link to the table and open it.
    Just open it.

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.

» See All Articles by Columnist Danny J. Lesandrini