Remote Queries In Microsoft Access

Remote Queries In Microsoft Access

What Is A Remote Query ?

It always amazes me how I can keep stumbling across features that I never ever noticed or read about in the 8 years that I have been programming Access databases. This article discusses one of those hidden gems that I discovered when investigating Access Automation. Whilst looking into that technology, I stumbled across a SQL help topic in Access that described an extension that allows you to point your current query to a table in another database. This in itself didn't seem all that interesting because I had been doing this for years using Linked tables. Then I noticed that you could also point your local query to a query that was in another Access database. No links, no local query definitions, this was starting to get interesting. Unfortunately the technology doesn't really come under any great heading so for the purposes of this article, I have come up with my own term for these queries and that is remote queries. Hopefully the discussions will help you to use these queries in the correct place once you understand what it is you have to do.

The "In Database" Clause Extension To SQL

As you will probably be very familiar with switching from query design mode to SQL queries, a remote query can be best described with the following example of SQL

SELECT [Orders Qry].* FROM [Orders Qry]

IN 'C:\msoffice97\Office\Samples\Northwind.mdb';

This example opens the orders query from my local Northwind database. No links, no tables.

INSERT INTO Orders ( CustomerID, EmployeeID, OrderDate )

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate

FROM Orders IN 'C:\msoffice97\Office\Samples\Northwind.mdb';

This example would transfer the some of the fields in from a remote location of Orders table in the Northwind database into a local version of the same table.

How Do You Set Up A Remote Query Manually ?

Remote queries are really a no-brainer to setup manually once you have seen an example. First open a query and do not select any tables. Choose Menu View and display Properties. Now replace (Current) in the Source Database property with the full path to the remote database that you want to display. An example of this is show in figure 1 where I have setup to return all the rows from the Orders query in the Northwind database. Now that you have added your database, you can choose the Query menu and choose Show Table. You will find this same button on your query design toolbar. Now all the tables and queries will be visible from the remote database as shown in figure 2.

A word of warning. This manual design of remote queries is terrific for initial development but you must be very careful if you are transferring the application to a new location as it is very likely that the path the remote database takes will change and this time you will not have any Add-In like the Linked Table manager to manage these changed paths. To help with this, I will demonstrate a simple wizard to change the paths later on in the article.

Click the picture to view it

Figure 1 - The Source Database property shows the path to remote orders query in Northwind.

Figure 2 - Once you have added the path to the source database, all tables and queries from that database will display in the table list for this query.

What Can A Remote Query Be Used For ?

The most important thing about this extension to Access SQL is that you understand it and apply it to the appropriate situation. In most normal applications, you will not bother with this approach but here are some scenarios where remote queries can be very handy.

"Software that looks at many databases". If you have ever written a piece of software that looks at more than one database, there are two approaches that you might have taken. The first would be to write software that modifies the linked tables in the database to keep pointing at the different database. Another approach would be to have different links for every different table that your database links to and then change the table that your application would point to. And yet another more complicated approach would be to write an Access Add-In to manage this multiple database arrangement for you. So as Remote Queries allow you to change the database path in an SQL statement (i.e. one spot), you will find that life could be a little easier.

"Software where the application is split into 2 or more databases and you want to maintain your queries in the one place." If you to have maintain multiple front end applications for one Access backend database, you will occasionally find that you are storing the same query in both applications. Having two copies of anything in computing always leads to problems as one version goes out of synchronization with the other one. Queries are no exception. If you are using a backend database such as SQL server, you will have the luxury of using queries stored in the database. Unfortunately in Access, you cannot link to a query in a backend database. But you can run a remote query by saying Select * from qryOrders in "c:\office\samples\northwind.mdb" Now you can store all the special logic for qryOrders in the backend query.

"You do not want to disturb the method that is used for re-linking to your backend database." The place where I am using 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 backend database. If you were to add link to a second backend database, you have to be very careful which tables your end users choose when they run standard Access Linked Table Add-In. If there are tables selected that appear in more than one database, the linked table manager will refresh the links one at a time and ask the location of those links one at a time. This is very confusing for end users (and a certain bearded software developer).

"The Insert and Select SQL statements can be used for a local temporary database." Here you can use an Insert Statement with a In Database clause to push the data into the local database and then manage the data in that remote database without using any links at all. When you are finished with the remote database, you can delete the local database."

"If you want to build a more secure MDE database" Wouldn't it be great if the user could not see the tables or the queries. If you were to hide the remote queries behind a form or module and turned the database into a MDE file, your front end database will distribute with no queries or linked tables. Now that will trick someone trying to sort out the logic behind the database."

What Smart Access Newsletter Has Had To Say On This Topic This Year.

An anonymous reader sent this one in: Edition 2.10

"I have a situation where I need to split my database into app and data. Since the data would be found in a number of places, I really wanted to avoid all the hassles of relinking tables, making sure what links are there, etc. In a moment of inspiration, I found that by using the IN clause in a select statement, I was able to get to my data file without having to create links!

"My method, which seems to be working right now, is to make the form unbound. On the load event of the form set Me.RecordSource = "SELECT * FROM tblMY IN 'a:\datafile.mdb';" and requery the form (Me.Requery). All the 'normal' form abilities are working on the datafile of my choice!"

Mike Gunderloy Replied

Yep, that works -- and it's not very well known. The IN clause of a SELECT statement in Access allows you to retrieve data from another Jet database on the local file system. In addition to using this syntax in a RecordSource, you can also use it in a regular SELECT query to retrieve records from a database that you don't have open.

Geri Reshef writes in Edition 2.14:

"One big advantage of 'linking without linking' using an IN clause is that it allows us to make a link from mdb file A to a query in mdb file B which is connected to a table in mdb file C (or to a query in file C which is connected to…). We cannot currently make a link from one mdb file to a linked table in another mdb file!"

Signup for this great free newsletter at www.pinpub.com

The Demonstration Database

The demonstration database has a two Remote Query wizard like forms that you can get the use of in your database plus some software that I used for testing the performance of the queries. You can see the options in the database in figure 3.

Figure 3 - The demonstration database has a number of options to experiment with.

To setup the demonstration database to work on your copy of Northwind, you will need to select either RemoteQueries97.mdb or RemoteQueries2000.mdb . Then open the database and run the Access Table Linker Add-In to make sure that all the linked tables point to your copy of the Northwind database. After creating your first query, you will need to run the Change Remote path for multiple queries option as shown in figure 3 that I explain later on in the article. This will point all the remote queries in this database to your copy of Northwind. Also in the demonstration database is a database called secureTables.mdb. You will use this to test remote queries with secure tables.

Creating A Remote Query With A Wizard

One of the problems with the query property interface described in section above is that you have to add the path to the remote database manually. There is no build button to help you find it. The other is that you need to work out how to display the Query property to add the Source Database property path to your remote database. To make this all a little easier, I have written a simple little wizard form that you can add to your databases as show in Figure 4

Figure 4 - Select a remote database and then choose a table or query in that database.

There are three software components to this interface. The first is the find file button that allows you to select your Access database. The code under this button is a slightly modified version of the file finder that came with Solutions database in Office 97 developer.

The second special function will be found in the onEnter event of the select table combo box. This will retrieve all the tables and queries in the Access database you selected in figure 4. To fill the combo box, the function returns one long text string with Table/Query Name and then either Table or Query. In Access 2000 and 97, the combo boxes will only handle 2048 characters. Access XP now doesn't have this silly text limit. This routine is called sysTabnames_FX and you will find many uses for it in applications where you want to retrieve Table or Query Name; Table or Query Type in a semi colon delimited string suited to combo and list boxes.

tableReq.RowSource = _
left(sysTabnames_FX(, Me!myDatabase), maxComboChar)

The third import coding lies under the Make Query button for which I have used one of my oldest functions, a Query Builder. The following code describes some of the hurdles you will face in generating remote queries from any Access query.

If Me!myDatabase <> "(Current)" And Len(Me!myDatabase) > 0 Then

If left(Me!tableReq, 1) = "[" Then
defQryNameStr = "rqry" & Mid(Me!tableReq, 2, Len(Me!tableReq) - 2)
Else

defQryNameStr = "rqry" & Me!tableReq

End If

QryNameStr = InputBox("Choose the name you want " & _
"to give to your remote query", "Input The Name Of The Query", defQryNameStr)

The first issue that crops up is the fact that Access allows a very loose naming convention for queries with blank spaces and many other odd characters being allowed. In this case I wish to simply add an extension of "Rqry" to denote the fact that this is a remote query. Now if the query name had no spaces, this would be an easy new name to give a query. But even Microsoft's examples all use names with spaces which we must handle by surrounding the name in square brackets as follows

If Len(QryNameStr) > 0 Then
sqlStr = "select * from " & Me!tableReq

If sqlStr <> "(Current)" Then

sqlStr = sqlStr & " in '" & Me!myDatabase & "'"
End If
Call replaceQuery_FX(QryNameStr, sqlStr, True, acViewDesign)

End If

Once the query is established, we need to make a very simply remote SQL string that will show all columns from the remote table or query. This is all the functionality that this wizard provides you as no amount of software development would make for a better query development environment as the one that comes with Access.

The Replace Query Function

The sample code above included a query builder function that you can use in any Access application called ReplaceQuery_FX. This function allows you to pass a new query name and an SQL string and it will open or save the query in the Access database container. The following simple example will save and open a query called aNewQuery

e.g. isOK = replaceQuery_FX("aNewQuery", "Select actuals, budgets from sales")

The arguments for this routine are

QueryName is a text name for the query that you want to make/replace

Query is the valid SQL string that makes up the query

Conf is an optional True or False flag to confirm if you want to replace an existing query with the same name (default is true)

openQue is an optional flag to open the query immediately in design view rather than normal view

If you never use a remote query, have a think about including this routine in your more versatile applications as it will allow you to easily turn any SQL in a viewable query.

The Big Problem With Remote Queries

As soon as you demonstrate your software, you are going to run into this problem. Your client will want to install the software on the "P" drive so that they can share it amongst all the users. You, of course have setup the remote queries on your C drive and guess what, the software falls over. If these were linked tables, you could run the linked table manager and things will all be fixed. But for remote queries, you will require something similar to a linked table manager that works on the database path of all remote queries in your database. And if you really get into the technology, you are also going to be in a situation where some of the remote queries refer to different remote databases. Now your path changing software will have to be selective when making alterations.

The approach that I have taken for in my sample application is to allow the user to firstly identify a query where the remote database path is incorrect, Then I allow the user to select the new path to this database that holds this query or table. Once that is done, I look through the SQL of all the queries in the current database and modify the remote database path to the new location of the database. This is handled using the wizard form that is illustrated in figure 5. The important part of this routine is the DAO software that searches the SQL of all the queries

Figure 5 - The replace remote path wizard allows you to change paths in multiple queries

With MyDB

For i = 0 To .QueryDefs.count - 1
If left(.QueryDefs(i).NAME, 1) <> "~" Then
    For j = 1 To 2
'     Some queries have the remote database path in
'     them twice so we need to repeat the
'     exercise for the second one.

If InStr(.QueryDefs(i).SQL, oldRemoteDB) Then
DoCmd.Echo True, .QueryDefs(i).NAME

        If conf And j = 1 Then
          testIt = MsgBox("Replace the remote path" & _
                   " in query --> " & .QueryDefs(i).NAME, _
            vbYesNoCancel, "Replace an existing query")

        Else
          testIt = vbYes
        End If

If testIt = vbCancel Then

GoTo Exit_replaceRemoteDBPath

ElseIf testIt = vbYes Then

' Check to see if the remote query is going
' to be set into a local query

          If newRemoteDB = "(Current)" Then
            sqlStr = .QueryDefs(i).SQL
            startPos = InStr(sqlStr, "in '")

            If startPos > 0 Then
              endPos = InStr(Mid(sqlStr, startPos + 5), "'")
              sqlStr = left(sqlStr, startPos - 1) & _
               Mid(.QueryDefs(i).SQL, startPos + 5 + endPos)

            Else
              sqlStr = .QueryDefs(i).SQL
            End If

Else

' replace one external database path with another

            sqlStr = repStr_FX(.QueryDefs(i).SQL, oldRemoteDB, newRemoteDB)
          End If
          .QueryDefs(i).SQL = sqlStr
          End If
    End If
   Next j
End If

Next i

End With

Listing 6 Subroutine that assists in modifying all remote queries with an incorrect remote path

Things to look for in this source code is the way that we search the SQL of the query using InStr(.QueryDefs(i).SQL, oldRemoteDB). Old remoteDB path is the path to the old database that has been identified in the query that was selected as the one to provide the old database path. There is a small function that replaces the old path with the new one once the path to the old database has been plucked from the old query. This is handled through the repStr_FX function. I have setup the routine to prompt the user to confirm each of these changes but in a live situation, you would probable do all the queries after one prompt. Note that in some queries like Make Table queries, the remote query path (in Database clause) might actually occur twice in the query as follows

SELECT Orders.* INTO NewOrders IN 'C:\msoffice97\Office\Samples\Solutions.mdb'

FROM Orders IN 'C:\msoffice97\Office\Samples\Northwind.mdb';

This is why I have to compare the database path against the SQL statements twice.

Relative Path

Another way to approach remote databases is to store the database in the same relative path as the software database. i.e. a sub directory Just before you are about to run the query, modify the remote path to suit the relative directory. This method works extremely well for spreadsheets and temporary databases.

Secure Tables

The remote queries work as expected on secure tables. The download zip file includes a database with two tables in it called tblSecure and tblUnsecure. You can try this out in the Create Your First remote query option to test the security of the queries. A possible enhancement of the sysTableNames function that is included with this database would be to hide secure tables or at least let the user know they were secure.

Performance Testing

The next 3 items on the main menu in the sample relate to adding additional rows to the Northwind table for testing performance. When I am required to test queries to try and improve performance, I will usually use the CopyFromRecordset method of Excel and transfer the full query results into excel. That way I can also output the time before and after the transfer to empty cells on the spreadsheet. A simpler method for testing performance of queries involves using message boxes and a watch. You will find software to double the number of records in the Northwind orders table using both linked tables and remote queries under buttons 3, 4 and 5 shown in figure 1.

For the testing, I used my slowest old laptop as a server and a 10 megabit network path. The results shown are for Access 97. As you can see, the time difference between linked and remote queries is negligible except where I have built a single insert statement for every line of data. Here there is a significant time difference but clearly the lesson here is not to use the single Insert statements rather than to worry about whether you are running remote queries or not.

This article first appeared in the May 2001 Edition of Smart Access. Reprinted with permission from Pinnacle Publishing (http://www.pinpub.com/).

and was written by Garry Robinson from GR-FX Pty Limited