Microsoft Office 97 brought into being a comprehensive solution for developing applications based in the productivity tools that make up the suite. Office 97 now has Visual Basic throughout the suite, from Word to Excel to Access. In each of these environments, you have the ability to develop complete applications based on the language.
Throughout this book, you've seen how to set up and work with SQL Server, but in this chapter, the focus is more on interacting with SQL Server, rather than administering the server itself. Much of the access to SQL Server can be standardized into a discussion of the Data Access Objects, and there are special considerations when working from Access. By and large, the ability to work with data from a SQL Server can be a good addition to your applications. In addition, by using some client/server techniques to work with information, you can leverage the processing power of the SQL Server system in your applications. This chapter focuses on two distinct areas. First, you'll see how to move your databases from Access to SQL Server and how to work with databases and tables that are based in SQL Server from within Access.
The second area of focus is that of developing an application in VB that can work with database tables. Each of these will be highlighted here. n
As of this writing, an upsizing wizard for Access 97 is not yet available. You can check back with the Microsoft Access site, located on the Web at www.microsoft.com/access, and download the utility after it becomes available. The Upsizing Wizard is currently available for Access 95 and Access 2.
The upsizing tools help you migrate a database schema to SQL Server by moving your tables and relationships to SQL Server-based objects. The utilities will move your tables to the server by exporting them and creating the necessary indexes and other supporting objects. In addition, triggers can be created that will enforce any relational integrity rules you've implemented in Access.
What results is a system in Access that contains linked tables that reference the tables in SQL Server. See Figure 26.1 for an example.
FIG. 26.1
When you link to tables in SQL Server, they are shown with the link icon but can
be accessed with your standard Basic instructions.
The wizard will not create stored procedures for you to take the place of data manipulation you may be performing in your Access Basic code. In these cases, you'll want to work through the information in this book on stored procedures and compare it to the types of things you're doing in your Basic code. Wherever possible, consider moving database manipulation statements and processes to stored procedures and out of your code.
In the next sections, you'll see how you can manually migrate your tables to SQL Server. You'll also see how to create the links you'll need to work with the tables as seamlessly as possible.
Microsoft Access gives you an excellent development environment with many of the capabilities of SQL Server available for testing and implementation planning. Because Access is a smaller engine and not truly client/server, its use as a server system in a larger implementation is somewhat limited, but it still provides an unmatched development environment for database work.
TIP: This chapter is by no means a complete tutorial on the use of Microsoft Access. For detailed operation information, how-to assistance, and the like, be sure to get a copy of any of several books from Que, including:
- Access 97 Expert Solutions, by Stan Leszynski, 1997
- Special Edition, Using Microsoft Access, by Roger Jennings
This chapter is meant to provide a quick overview of what you'll need to do to work with database and table information shared between Access and SQL Server.
Access gives you a good user interface in which to develop the tables and relationships. There are some differences in how Access works with databases, including items at both the physical storage aspect and slightly different terminology when discussing some items. Some examples are:
There are others, but these are the key differences that will likely help in your use of SQL Server with Access. If you have an application in Access, it's represented by up to six different elements. See Table 26.1 for a quick explanation of the different components and whether it's something you'll likely be upsizing or converting to SQL Server.
Element | Description and Upsizability |
Tables | Same as SQL Server. This is probably the single most important item that you'll be upsizing. Tables represent the rows of information in Access that you want to convert to SQL Server. |
Queries | Queries will likely need to be converted, but it will be a largely manual task to do so. This means you'll have to review the SQL behind the query and create the appropriate view on SQL Server. Remember, too, that you can LINK to a view on SQL Server and use it as you would a table, assuming you declare an "index" on the view. |
Forms | Forms will not be converted to SQL Server. Remember, in the client/server model, the client system is responsible for the management of the user interface; the server has responsibility for the management and manipulation of the data. |
Reports | As with forms, this item will not likely be converted. Of special note, however, is any report based on a query. In Access, you can code the query into the report. To get the best results, you'll want to review the property sheets for the report, convert the hard-coded query, and make it a view in SQL Server. Then, in the query, reference the new view. Performance will be enhanced, the report will run the same as it did with the hard-coded query, but the result is a client/server implementation of the report. |
Macros | Macros are strictly an Access item. Macros may be calling on Queries, and they may be issuing some higher level commands that are worth reviewing. As with Reports, you don't want to have a macro that relies on a query that you can place into SQL Server for better performance. |
Modules | Modules are rarely an automatic decision to upsize. What this means is that you need to consider each and every procedure and subroutine to determine what's best upsized. With the modules, you have several options. You may be converting a subroutine to a stored procedure, a view, or a dynamic query. It will depend entirely on the processing you're doing in the database. Remember to make changes in short batches and continue testing. It's important as you start working with the core functionality of your system that you test it thoroughly before moving on to the next conversion goal. |
Upsizing an application usually takes on several different steps, outlined in the next sections.
When you convert pieces of your application to use SQL Server, it can be helpful to do so in stages that let you test each step of the way. In these examples, you'll see how to convert portions of the Northwind Traders demonstration database. The sample database is provided with Access as a learning tool and can be installed when you set up Access on your system.
CAUTION: Moving your information to SQL Server should be done at a time when other users are not on your system. During the process, if information is added to the Access tables, it will not be reflected in the SQL Server tables. In addition, there is no easy way to re-synch the tables to gather any information that had been missed in the transition. Be sure you have exclusive use of the database if dynamic changes are typically made to the system.
Moving the Tables to SQL Server The first step is typically moving your tables and their associated data to the SQL Server system. This is done pretty simply in Access, by using the Export functionality built in to the system. To start, open the Northwind application and select the Tables tab. See Figure 26.2.
FIG. 26.2
The Northwind system lets you work with a largely functional ordering system and
includes several tables to work with.
The first thing you'll do is select a table and then right-click it. Select Save As/Export from the resulting menu. Select To an External File or Database from the dialog box that results. This enables you to send the information to an alternate destination, rather than copying it within the current database. See Figure 26.3.
FIG. 26.3
You can use the Export option to make copies of data in an existing Access database.
For sending information to SQL Server, however, you'll need to send it to an external
database.
When you make your selection and click to continue, you'll be presented with a dialog box that prompts you with the type of export you want to perform. As you can see in Figure 26.4, you need to select the type of export you want to perform.
FIG. 26.4
The option to export to SQL Server is supported as an ODBC setting. ODBC is the last
selection on the Save as type list box.
When you select ODBC, you'll be asked to specify the name of the table you'll export to. The default will be a table with the same name as the table you're exporting. For most systems, it's a good idea to accept the default name. This will save confusion when you're linking to the table later. Finally, you'll be prompted to select the ODBC data source you'll use to connect with the database on SQL Server. In Figure 26.5, you can see that you are able to select from all installed data sources, or you can create a new data source for the conversion batch.
FIG. 26.5
Select the appropriate DSN you'll use to gain access to the database and tables you
need.
TIP: If you'll be using this in a production environment, you'll save trouble later if you create a new data source for the linked tables. Write down the name and use it only for this task. That way, if a user name or password changes, you can update it in one place, and you'll update all of your links using that DSN to get to SQL Server.
After you select the ODBC DSN to use, the export process will begin. You'll be asked to log in to the server first, allowing you one final chance to select the database you want to use. To use the option, select the Options >> button on the dialog box. In the example shown in Figure 26.6, the Northwind database is used for the export.
FIG. 26.6
When you log in, you'll have the opportunity to select which database you want
to export into.
During the export process, you'll see an indicator in the lower-left corner of the Access workspace that will tell you the status of the process. After the process is completed, the table will be on SQL Server, with the structure and data intact. As you can see in Figure 26.7, the table now shows up in the Enterprise Manager. You should always confirm that the table was converted without a problem. Perhaps the best way to do this is to run a simple select * from table and compare the results from Access with those from SQL Server. Repeat this export process for each table, resulting in a total of eight tables exported to the server.
FIG. 26.7
You should verify that each table has exported to the database you planned before
you continue with the upsizing of your application.
At this point, there is no difference in your Access application. You've exported only the structures and data to the SQL Server system. The next step is to make the SQL Server tables those that are referenced when your application is run. Doing so requires that you first rename your existing tables, then create the links to the SQL Server-based information.
From Access, rename each table by right-clicking it and choosing the Rename option from the menu. Simply add "OLD" before the existing name and press Enter. You can use anything you'd like here. The key is to keep the old table (so don't delete it) and to rename it so you can create the active links that point to the SQL Server. When you're finished, you'll have a list of tables that resembles those shown in Figure 26.8.
FIG. 26.8
By renaming each table, you leave yourself the option of falling back to the original
tables without a problem.
The last step to begin using your now-SQL Server-based tables is to link to them from Access. Right-click the Tables form and select Link Tables... from the menu. Once again, you'll be prompted for the table and type of table to link to. Select ODBC Databases () from the Files of type list box (see Figure 26.9).
FIG. 26.9
As with the export process, you'll be using an ODBC data source for the linked
tables from SQL Server.
Select your ODBC data source, and you'll be prompted to log in to SQL Server. Remember, if you're using a different database, select the Options >> button to select it prior to completing the login. As soon as you log in, Access will display the list of tables from the database. You'll notice, as shown in Figure 26.10, that each table has a DBO prefix, representing the database owner.
FIG. 26.10
Access will automatically query SQL Server to determine what tables are available.
You'll also notice that any file name characters that were allowed in Access, but not in SQL Server, will be replaced to be correct for SQL Server. In the example, the Access table "ORDER DETAILS" was updated to be named "ORDER_DETAILS." Click each table you want, or in this case, click Select All. Next, click the OK button to begin the linking process. As each table is linked, you will be prompted to indicate the unique identifier for it. The list of fields, or columns, that you see will be taken from the table, and you'll need to highlight the index field or fields (see Figure 26.11).
FIG. 26.11
If you can, select the same fields for your key on the linked fields as you have
for the original tables.
Be sure you select a field or combination of fields that will result in a unique key to the table. This is required in order to have an updateable table from Access. In many cases, as is the case with the Northwind sample database, several different elements may be required to create a unique key on the table. Be sure you have your database schema handy when you link to the tables.
NOTE: The information you provide for the index is used to create a pseudo-index, based in Access. This index is used to work with SQL Server to provide the information to uniquely identify a row for updates. The index is not created in SQL Server, so you won't see it on the table if you review the objects associated with the table in SQL Server. n
If you make a mistake, or later find that the indexes don't provide the functionality you need, you can always unlink the table and re-link it. After you've linked the tables and provided the information to identify the index, Access's tables will be updated to show the new links. You'll notice a globe next to each of the SQL Server tables, indicating that it's accessed externally. Figure 26.12 shows the results of linking the tables to the Access database.
FIG. 26.12
Once added, the linked tables are shown with a different icon next to them.
Of course, you've probably noticed that the tables came in with names that aren't quite right. You need to rename the tables, removing the "dbo_" prefix that's been added to them. As before, you can rename them by right-clicking them, selecting Rename, and removing the prefix. The result is a series of tables with the original names but linked to SQL Server.
At this point, you should run your application and make sure it still performs as you expect. If you have any problems, check the file names you've provided and make sure the SQL Server information you've provided is accurate relative to login names, passwords, and so on. If you have any trouble at all, do not continue in the upsizing until you've corrected the problem.
You may notice that you already have better performance on the application. It's interesting to see that even by moving the data to SQL Server, you can obtain some benefits, even without optimizing Select statements, queries, and other functions.
After you're satisfied that you've successfully converted your tables to SQL Server, you can review the queries to see which ones are likely candidates for translation into a view or stored procedure in SQL Server.
Reviewing the Queries--Making Views Converting your queries to SQL Server is not quite as straightforward as exporting and linking to tables, but it can certainly be faster than writing the views from scratch. The basic premise of moving the queries to SQL Server is that you'll create a view in SQL Server, then link to it just as you do a table. Because Access sees queries and tables as functional equivalents, this is another place you can make the changes to the data, make no changes to the application, but still get the benefits of a client/server implementation. In this example, you'll convert one of the queries from the Northwind database, Order Subto to a view. Then you'll see how to reference the view and use it in your applications. Of course, the goal of this is as mentioned earlier: no code change--only a change in the source of information.
To begin, select the Queries tab in Access. Select the Order Subtotal query and click Design. You'll be presented with the query designer, probably in Design mode, looking like that shown in Figure 26.13.
FIG. 26.13
In the default design mode, you can visually design the query by dragging and
dropping fields to the query. This, however, isn't the best way to work with the
query for creating the associated view.
The first thing you'll want to do is change to SQL mode for the query. You can do this in one of two ways. First, you can select View, S_QL View from the menus. This will change the display to the associated SQL statements that represent it. You'll get the same result from the second approach, selecting the View drop-down list and selecting S_QL View. In either case, the result is that Access will give you the SQL statements, like those shown next:
SELECT DISTINCTROW [Order Details].OrderID AS Expr1, Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS Subtotal FROM [Order Details] GROUP BY [Order Details].OrderID;
There are a couple of things you'll notice right away from this statement. First, it has references to the tables enclosed in square brackets. Because Access allows you to have spaces in your table names, brackets are used to delineate the names. These aren't allowed in SQL Server, and neither are the spaces in the object names.
You may recall from the work in linking the tables to SQL Server that the names that contained a space were changed to include an underscore character in place of the space. In this example, the table that was changed during the linking demonstration is referenced and will have to be updated to include the underscore.
NOTE: Even though you updated the name of the object reference in Access, what you really updated was the shortcut to the table in SQL Server. The table in SQL Server still contains the underscore, even though the reference in Access has the space in the object name.
You'll need to change DISTINCTROW, inserted automatically by Access, to DISTINCT. SQL Server won't recognize the DISTINCTROW function provided by Access.
Another thing you'll notice is the use of the CCUR function, one that converts numeric values to currency representation. This will have to be either replaced or coded around to format the information as you'll need it.
TIP: In this case, the value will be left unconverted as it's used in reports. The Access report writer will format fields for you, saving you the need to format it before using it.
The final thing you'll need to change is the trailing semicolon on the statement. SQL Server doesn't need this and will point it out as a syntax error on the statement.
The first step you can take in testing statements like this is to cut and paste it in to ISQL/W and run it. See what the results are and step through the changes you need to make to get the right results set. In this example, the first thing that's pointed out is a syntax error, as outlined here:
Msg 170, Level 15, State 1 Line 1: Incorrect syntax near `[`.
This error is referencing the brackets around the object names. Remove the brackets and re-run the query to find the next item you need to consider. Of course, when you remove the brackets, change the object name as well so it includes the underscore character. If you don't, SQL Server won't see the table. The result is shown next:
SELECT DISTINCT Order_Details.OrderID AS Expr1, Sum(CCur(UnitPrice*Quantity*(1-Discount)/100)*100) AS Subtotal FROM Order_Details GROUP BY Order_Details.OrderID;
At this point, you have two final things to update. You need to remove the reference to CCur, and you need to remove the final semicolon. When you do, the results set will start off like that shown in Listing 26.1.
Expr1 Subtotal ----------- ----------------------- 10248 440.0 10249 1863.39987182617 10250 1552.60003662109 10251 654.060005187988 10252 3597.89990234375 10253 1444.79998779297 10254 556.62003326416 10255 2490.49996948242 10256 517.800003051758 10257 1119.89994049072 10258 1614.88000488281 10259 100.799999237061 10260 1504.64999389648 10261 448.0 10262 583.999996185303 10263 1873.79995727539 10264 695.625 10265 1175.99995422363 10266 346.559997558594 10267 3536.60000610352 10268 1101.19993591309 10269 642.200012207031 10270 1376.0 10271 48.0
By comparison, the same query run against the linked SQL Server table from within Access produces the results shown in Figure 26.14.
FIG. 26.14
The results are the same in the newly modified SQL query that will be used for
the View. The only exception is the formatting of the information.
You're ready now to use this query as the basis for a view. In SQL Enterprise Manager, right-click the Views in the Northwind database. Select New View from the menu to get the dialog box that lets you provide the SQL statements that will comprise the instructions for the view. Cut and paste your SQL statement in to the box after the AS clause. Change the <View Name> to be the name by which you want to know the view.
TIP: Even if you have to place an underscore in the name of the view where a space exists in Access, you should name the view as close to its original name in Access as possible. This will make debugging and tracking down table/view links easier when you're debugging your application.
In Figure 26.15, you can see an example of the view with the name updated and the code pasted in.
Press the "play" button to run and store your new view in the database. When it runs, it will be tested for syntax validity, compiled, and stored. You won't get any results back from the query until you open it explicitly. If all goes well, the query dialog box will be cleared, and you'll be able to create another view if needed.
FIG. 26.15
Use the tested SQL statement as the basis for your new view.
At this time, you've got the view in the SQL Server system, and you're able to use it in a query. You can test the view in ISQL/W by simply issuing a select * from order_subtotals statement. The results should match those shown earlier in the test run of the query.
Now the fun begins. What remains is linking the application to the new view and testing the application. Again, the goal is that you don't have to change any code in the application but only the objects referenced in the Access user interface.
Perhaps surprisingly, Access will allow you to link to the view just as you do a table in SQL Server. When you walk through the link process, you'll notice that the view is listed, along with the other tables that are available, automatically (see Figure 26.16).
FIG. 26.16
In the link tables options, views are listed right alongside available tables.
When you combine this with the fact that Access treats queries and tables the same logically in your application, you can quickly see that you'll be able to link to the view and run your application. Link the view to your Access application as you would a table.
Before you can rename the view to the name of the original query, you'll need to rename the query. As with the tables that were linked in earlier, rename the query with an OLD prefix, keeping the query around should the need arise later. After the query has been renamed, rename the newly linked view to the name of the query. In this case, it's dbo_Order_Subtotals that should be renamed to Order Subtotals.
Now is the time to test. Keep in mind that queries have the potential to be a more difficult testing proposition. It's a bit less obvious in your application where they're referenced. They can be called from reports, modules, and macros. In addition, you can have forms that reference the query. In short, be sure you fully test your application from every conceivable angle before you roll out a converted application to a production environment.
Microsoft Access is not a client/server system in and of itself. Applications you develop with it are not inherently developed with both client and server components of the solution. You can, however, use Access as a development tool, providing the user interface for applications. The tools it offers give you good leverage creating your applications, such as the report writer, the query tools, and so on. These are all helpful development tools.
When you create the application, consider first doing so in Access against a database that you can test against, break, and repair. By doing so, you can use Access for what it's best for and still use SQL Server to manage the database aspects of your work.
You've seen throughout this chapter how you can convert those items that should be server based to SQL Server. Tables, queries, and views are objects best managed by SQL Server, and they're easily converted to SQL Server after you've designed them.
Access basically becomes a development environment, giving you tools to manage the interface that is presented to the user and letting you work with SQL Server on the back-end.
When you create queries in Access and then submit them to SQL Server, they are first parsed for the ODBC connectivity layer and then submitted to SQL Server as a sort of temporary stored procedure to be run. The results are returned to your application, and you can work with the data from there.
What happens if you want or need more control? What if you want to call a stored procedure and use the output from it directly? That's where pass-through queries come in. In Access, you can create a pass-through query that bypasses the translation and submits exactly what you indicate to SQL Server. To set up a query for pass-through operation, first open the query by highlighting it and clicking Design. Next, select _Query, S_QL-Specific, and finally Pass-through from the menus. When you do, you're presented with the SQL statement equivalent of the query, as shown in Figure 26.17, for the Invoices query in the Northwind database.
Pass-through queries let you call stored procedures, reference views, and use other features of SQL Server that might have been unavailable using the more typical query from Access. To run a stored procedure, for example, you can just use the EXECute command and call out the stored procedure.
FIG. 26.17
When you choose to make a query a pass-through query, Access will switch to SQL mode
so you can edit the statement if needed.
For example, to call the stored procedure AccessProc from a pass-through query, you can use the following statement:
exec accessproc
When you do, the stored procedure will be executed, and the results will be presented from the query. It's a simple thing to incorporate this type of logic in your application by creating the queries you need or by establishing your queries in Access Basic to be pass-through queries.
Access is a great tool. It provides the capability for corporate developers to create solid, database-centric applications that can be put into production. The applications can include security and can be quick and easy to use. It's a natural way to introduce and "test market" an application with users in the business environment.
Once completed, upsizing the application to SQL Server can make a good application even better. Performance can be improved, security can be added, and the open availability of the application's information can be expanded, providing corporate-wide access to the application if needed.
In practical applications, using Access as the development and test platform works well. We've used this approach at several sites with good success. The issues that don't tend to get ironed out appropriately in test situations that rely on Access include the following:
After your first application is converted, you'll come up with additional items that pertain to your development style that need to be reviewed. The best thing you can do is make a list of these items and make sure you check them on each and every system you upsize.
In this chapter, you've seen how to move an Access-based application to SQL Server while still maintaining compatibility with the code you have based in Access. You'll do best by your application if you add the special things possible in SQL Server to your database. These include those items in the following closely related chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.