Solutions By Design: A Data Access Solution

Last month, in "Accessing SQL Server Data," I described the first three steps of a six-step process to distribute data views to the user's desktop, using Microsoft Excel as a low-cost front end to your SQL Server. Using the Pubs database as an example, I described how to set up the ODBC connection between Excel and SQL Server so that the views created on the SQL Server would be accessible from Excel. Now let's look at the final three steps: creating a query file, automatically distributing that query file to every user's desktop computer, and introducing the user to this new way of accessing data on the SQL Server.

Create the Query Files

Last month, I showed you how to use Excel and Microsoft Query to access SQL Server data and retrieve it into an Excel spreadsheet. You can save this query as a .dqy file that you can distribute to the users' desktops. A .dqy query file is created when you create a new query in Excel. Each .dqy file contains information about how to connect to the database server and after you're connected, which view or query to execute. A .dqy file also contains column heading labels for the returned data.

I also showed you how to put an ODBC data source called PubsDatabase in place. Now, open Excel and make sure you have a blank spreadsheet open. From the top menu, select Data, Get External Data, Create New Query. (If you're using Excel 2000, the menu selections are Data, Get External Data, Run Saved Query.) When the Choose Data Source dialog box opens, select PubsDatabase and click OK. The Query Wizard's Choose Columns dialog box opens with a pick list of views and tables on the left, as Screen 1 shows. Highlight the view _PublisherStaff, which I created in last month's column, click the right-pointing arrow to move all columns into the window on the right (Columns in Your Query), and click Next. The resulting dialog box gives you an opportunity to filter the data. For this exercise, don't filter. Click Next.

The next dialog box lets you choose the sort order for displaying the data. Sort first by publishing company, then job title, both in ascending order, as Screen 2 shows. Click Next.

The dialog box in Screen 3, page 62, lets you Return Data to Microsoft Excel, View data or edit query in Microsoft Query, or Save Query. Choose Save Query. When the Save As dialog box opens, accept the default location, name the query PublisherStaff .dqy, and save. Click Finish.

In Excel 97, the Wizard places the PublisherStaff .dqy query file in your Office/Query folder. The full pathname is usually \Program Files\Microsoft Office\Queries. To let users access this query, you first need to place it on a generally accessible file server, and second, modify the query so it runs for everyone. If you can, create a shared directory on a centrally accessible file server, call it DBQueries, and copy the query Publisher-Staff.dqy into it. If you can't do that, create a DBQueries directory on your machine and copy the query to that directory.

Now you need to modify the copy of the .dqy file in the DBQueries directory. Use Notepad (Run notepad.exe) without word wrap to edit the file. Be careful when you edit .dqy files because modifying a position or inserting a return where one didn't exist before can cause the application using the .dqy file to malfunction.

Listing 1 shows the original .dqy file. Depending on your environment and permissions scheme, some variables might differ from this file's specifications. Listing 2 shows the finished .dqy file. Note the changes I made from the original file. The Driver keyword is set to \{SQL Server\}; the Server keyword points to the name of the server where the database resides. You can set the keywords UID and PWD (user ID and password—if present in your .dqy file) to NULL, along with WSID (workstation security identifier). Set the APP keyword to Microsoft® Query. The DATABASE keyword must point to the database where the data is stored. Make sure you don't have any returns in this keyword line or in the query line that follows (the one that starts with SELECT). The SELECT line is the SQL query; don't modify anything on this line. The last line of the file is the tab-delimited list of column headers for the returned data. Two blank lines separate it from the query line. The positioning and arrangement of the column headers is closely related to the columns specified in the query line. Don't modify the last line.

Now, test and preview what your users will see. Make sure Excel is still running and that you have a blank spreadsheet open. From the top menu, select Data, Get External Data, Run Database Query (Run Saved Query, in Excel 2000). When the Run Query dialog box opens, select PublisherStaff from the list and click Get Data, as in Screen 4. The Returning External Data to Microsoft Excel dialog box, which Screen 5 shows, then prompts you for where to place the data in the open spreadsheet. The default is =$A$1, which is the absolute value of the upper left cell on the spreadsheet. Accept the default; click OK. The data set that returns is all the data in the table, sorted first by publishing company, then by job title. Remember that this view of the data is static. It isn't dynamically linked to the database, so changes made to data in the spreadsheet won't propagate to the database.

Distribute the Query Files

You'll need to set up a scheme to distribute the .dqy files to users logging on to the network, so they can run the queries. The scheme I suggest is a simple one, but it works seamlessly. Listings 3, 4, and 5 are batch files that instruct a client computer to copy .dqy files from a central file server into a user's Office Queries directory (usually \Program Files\Microsoft Office\Queries) each time the user logs on to the network. With luck, all the user computers are set up with the same system software directory structures. If each person has a different Office software installation, this scheme will need extensive modification to work.

I've created a version of this batch file, Query Copy nn.bat, for each of the Microsoft client operating systems. The batch files use the MS-DOS xcopy program to copy the query files. The switch options vary from one operating system to another. By creating separate batch files for each operating system, you're prepared for changes in the option sets of future xcopy releases. Also, management of the process is simplified: To set up a Windows 95 machine, use the file Query Copy 95.bat. Store these .bat files in a directory other than DBQueries.

Unless you can take charge of your remote clients through systems management software, you'll have to set up the automated copy procedure from each client computer. At each client computer, copy the appropriate batch file into the root directory. Click the file name (Query Copy nn.bat), and select Create Shortcut from the shortcut menu. Drag the shortcut into the client's Startup folder. On a Windows 9x computer, this folder is \Windows\StartMenu\Programs\ Startup. For a Windows NT computer, you can put the shortcut in the All Users Startup folder, or, if it's a shared system, you can copy the shortcut into specific profiles.

Highlight the shortcut to Query Copy nn.bat, right-click, and modify the properties, as Screen 6 shows. Set the .bat file to run minimized and to close on exit. Test the file by double-clicking it. You'll see XCOPY on the bottom task bar for just a moment, as the query files are being copied from the file server to the client desktop you're testing from. At every future startup, this batch file will execute. Now you can add, delete, and modify queries at the file server, and every time your users log on to the network, these queries will be copied to their computers.

The last step in this process is to set up a training program to teach the users how to use Excel with the queries. During the training, emphasize the following sequence of keystrokes: After you open Excel, from the top menu choose Data, Get External Data, Run Database Query (Run Saved Query if you're training on Excel 2000). As in Screen 4, users can see the queries you created. Choose a query by highlighting it; click Get Data. Position the data on the open spreadsheet, click OK, and you'll have a copy of the data in the spreadsheet, ready for use.

Leveraging existing user skills and already-installed software packages on your company's computers is a cost-effective way to create database access. Using Excel and Microsoft Query to bring views of the data to your desktops can be the fastest way to develop user interaction with the data in your database.