| |
|
| |
|
|
Columns & Rows
Access to SQL Client/Server Development with Access and SQL Server
Access 2000 has new options to offer the client/server application developer: a new data storage format, a new Upsizing Wizard, and an improved Jet database engine capable of supporting merge replication with SQL Server 7.0. This article will discuss some of these options and attempt to guide you through the sometimes bewildering array of choices.
The Upsizing Wizard The Access 2000 Upsizing Wizard is built into Access 2000, so you don't have to go to the Web site to download it. The Upsizing Wizard will take care of upsizing your Access 2000 application to SQL Server 7.0, and allow you to choose how you want your existing application converted to SQL Server. Here are some of your choices:
To load the Upsizing Wizard, select Tools | Database Utilities | Upsizing Wizard from the menu and follow the steps. You will need to specify options for your upsized database. FIGURE 1 shows upsizing all table attributes using DRI (declarative referential integrity) instead of triggers.
Although the Upsizing Wizard simplifies the steps of upsizing your Access 2000 application to SQL Server 7.0, your work has only begun. You now need to modify your Access application to take advantage of SQL Server features.
Design Goals If you're moving from a pure Access/Jet environment, this can mean completely re-writing your application. Access forms work well based on queries that return all of the rows and columns from an entire table. It often comes as a surprise to a developer who has upsized her Access application to find that it actually performs more slowly when the data is stored in SQL Server.
To divide the processing of tasks more efficiently between Access and SQL Server, you need to let SQL Server handle the data processing. This means reducing the amount of data exchanged between the server and the Access front end by putting a more restrictive WHERE clause on every query sent to the server, or by calling stored procedures located on the server to return result sets. This reduces both network traffic and the load on the server (which other users will appreciate), while giving a considerable performance boost to your application. Stored procedures are compiled and optimized in the database, enabling them to execute much faster than an equivalent dynamic SQL statement.
You can also make your application more scalable by releasing resources early, or by not using them at all. One technique is using local (Jet) tables for static lookup tables to fill list boxes and combo boxes. Another technique is refraining from holding locks on the server by involving users in transactions where records can be potentially locked for long periods. Collect all user input before the transaction and execute it quickly.
Linking SQL Server Tables in an Access Database If you want to stay with the familiar Access database (.mdb), then linking tables is certainly the easiest option, requiring the least amount of effort on your part. You can work with SQL Server tables via ODBC in the same way you would work with native Access tables (or linked tables in any other database format). You can also link to a view on the server and work with it, the same way you would with a table.
At first, linked tables appear to be the ideal solution: simply link up and go without having to rewrite. However, linked tables don't meet the design goals we discussed earlier. If you moved to SQL Server because your tables were getting too large or you were trying to support too many concurrent users, then it may actually make things worse.
In addition, linked tables depend on ODBC to make the connection to SQL Server. Although ODBC remains a well-supported technology in the Microsoft world, it's clearly not the technology of the future. Microsoft has consistently said that ActiveX Data Objects (ADO) is the preferred data-access technology for new applications. In Access 2000, we're already seeing some features that are only supported in ADO. If you're planning to keep an application around for the long haul, you should consider the benefits of making the ADO migration now.
Unbound Forms One alternative to using linked tables is to use unbound forms and ADO code. Access 2000 uses ADO as its default data access library. The sample database included with the article, Connect2K.mdb, is an Access 2000 database with no linked tables or native Access tables (this is available for download; see end of article for details). The sample form, frmSelectRecordsource, uses ADO to retrieve data from a SQL Server 7.0 database. To use the sample, open Connect2K.mdb and click the Unbound Form button. This loads frmSelectRecordsource. Here, you can enter the name of your server, a database name, a table name, a valid User ID, and a password with rights on the server. (Note: Form frmSelectRecordsource references Microsoft ADO Ext. 2.5 for DDL and Security. You may need to reset this reference to version 2.1, via the References dialog box.)
FIGURE 2 illustrates the form with a server named MABEL, the Northwind sample database in SQL Server 7.0, and a SELECT statement on the Customers table:
SELECT * FROM Customers WHERE Country = 'France'
You could specify only the Customers table, but that's not something you would want to do in practice unless the user needs to see every record in the table.
When you click OK, a dynamic data entry form will be built based on the SELECT statement. You can navigate through the records, edit them, and save changes back to the database. FIGURE 3 shows the form built by the sample. If you close the form and select a different database and recordsource, a new form will be built based on the database and recordsource you select.
The way this works is to have the application take advantage of ADO's ability to maintain a client-side recordset. Using OLE DB's Cursor Service, a client-side recordset retrieves data once from the server and holds it along with any changes in the memory of the client computer. You can then use ADO's batch-updating capability to write back any and all changes to the server as a single operation. Working in this manner lowers network traffic and server memory requirements at the cost of some concurrency, i.e. you never know if some other client has cached changes to a record you're working with.
When you select a data source, the sample creates a global recordset using the GetRecordset procedure. Note that the recordset is defined as being of type ADODB.Recordset. Because ADO and DAO objects have the same names, you need to fully qualify ADO object names if you're working with the ADO and DAO libraries in the same database (see FIGURE 4).
' Opens a client-side recordset based on the ' options selected on frmSelectRecordsource. Sub GetRecordset(strServer As String, _ strDatabase As String, strSource As String, _ strUser As String, strPassword As String)
Set grst = New ADODB.Recordset With grst .CursorType = adOpenKeyset .CursorLocation = adUseClient .LockType = adLockBatchOptimistic .Open _ strSource, "Provider=SQLOLEDB;User Id=" & _ strUser & ";Password=" & strPassword & _ ";Initial Catalog=" & strDatabase & _ ";Data Source=" & strServer End With End Sub FIGURE 4: Selecting a datasource.
The combination of CursorType, CursorLocation, and LockType in FIGURE 4 results in a recordset on the client in batch-editing mode.
After opening the recordset, the GetFields procedure (located in basADO) returns a semicolon-delimited list of all the fields in the recordset. The CreateControl function then builds textboxes for each field in the recordset (see FIGURE 5).
' Open the ADO Recordset and create a ' data editing form based on it. Private Sub cmdOK_Click() Dim strFields As String Dim strField As String Dim intSemi As Integer Dim lngTop As Long Dim ctl As Control Dim intI As Integer
GetRecordset txtServer, txtDatabase, txtRecordsource, _ txtUserID, txtPassword & "" DoCmd.OpenForm "frmData", acDesign, , , , acHidden lngTop = 660
strFields = GetFields() intI = 1 Do Until Len(strFields) = 0 intSemi = InStr(1, strFields, ";") strField = Left(strFields, intSemi - 1) strFields = Mid(strFields, intSemi + 1) Set ctl = CreateControl("frmData", acLabel, acDetail, _ , , 60, lngTop, 2820, 240) ctl.Caption = strField & ":" Set ctl = CreateControl("frmData", acTextBox, _ acDetail, , , 2940, lngTop, _ 2820, 240) ctl.Name = "Text" & CStr(intI) ctl.BeforeUpdate = " [Event Procedure]" intI = intI + 1 lngTop = lngTop + 300 Loop DoEvents DoCmd.OpenForm "frmData", acNormal End Sub FIGURE 5: Working on a recordset using GetFields and CreateControl.
Note that the BeforeUpdate properties of the created textboxes are set to " [Event Procedure]" to hook them up to pre-existing event procedures in frmData. You could use the InsertText method to build event procedures on the fly here, but doing so will reset the VBA project and disconnect you from the data source.
The frmData form uses straightforward code to move through the recordset when you push the buttons and to save changes when you modify a field. The code from the navigation buttons uses the appropriate Move method on the global Recordset object (MoveFirst, etc.), and then calls the GetCurrentRecord procedure, which walks the controls collection and runs the GetFieldData procedure on the appropriate textboxes. If the recordset position is on a data row (and not the beginning or end of the recordset), then the AbsolutePosition property of the recordset is used to display the position of the record in the txtCurrentRecord textbox. You can think of the AbsolutePosition property as a surrogate record number (see FIGURE 6).
Private Sub GetCurrentRecord() Dim ctl As Control
For Each ctl In Me.Controls If ((ctl.ControlType = acTextBox) And _ (ctl.Name <> "txtCurrentRecord")) Then ctl = GetFieldData(CInt(Mid(ctl.Name, 5))) End If Next ctl Select Case grst.AbsolutePosition Case -2 Me!txtCurrentRecord = "BOF" Case -3 Me!txtCurrentRecord = "EOF" Case Else Me!txtCurrentRecord = grst.AbsolutePosition End Select End Sub
Private Sub Text1_BeforeUpdate(Cancel As Integer) grst.Fields(0).Value = CStr(Me!Text1) End Sub FIGURE 6: Displaying the current recordset position.
The GetFieldData function simply checks to see if we're at the beginning or end of the recordset with the .BOF and .EOF properties, and then returns the value of the field if we're on a record:
Function GetFieldData(intFieldNumber As Integer) As String If ((grst.BOF) Or (grst.EOF)) Then GetFieldData = "" Else GetFieldData = _ grst.Fields(intFieldNumber - 1).Value & "" End If End Function
Finally, when you click the Save All Changes button, the form invokes the UpdateBatch method of the ADO recordset. This method writes all of the cached changes back to the server:
Private Sub cmdUpdateBatch_Click() grst.UpdateBatch End Sub
Of course, you can extend this sample in many ways. For starters, in a production environment, you'd want to make sure the batch update succeeded by placing an error trap in it and checking the Status property of individual records if there was any problem. You might also want to experiment with different record-locking and cursor type properties, or build dedicated forms rather than creating forms on the fly.
Creating a Stored Procedure-driven Application Replacing all data access with stored procedures on your SQL Server is an excellent way to speed up data operations and reduce network traffic. It also has the advantage of separating the user-interface code in the Access database from the business logic of the application, which can be implemented in the stored procedures. Stored procedures are written in Transact-SQL and saved on the server. SQL Server 7.0 has an efficient mechanism for caching the execution plan of a stored procedure, so that subsequent calls to the same stored procedure execute more quickly. Stored procedures are also an efficient security mechanism, effectively acting as a barrier between data and the application to prevent users from accidentally or maliciously modifying large amounts of data, as well as preventing that million-row-returning ad hoc query. Stored procedures in SQL Server 7.0 have a single permission, Execute, which supercedes any permissions granted to users on the underlying tables.
Using stored procedures requires more effort on your part because you need to know enough Transact-SQL to write effective queries, which you can then save as stored procedures in SQL Server. The following stored procedure executes a simple SELECT statement against the Customers table in the Northwind database:
CREATE PROCEDURE procSelectCustomer AS SET NOCOUNT ON
SELECT CustomerID, CompanyName, ContactName FROM Customers ORDER BY CompanyName, ContactName GO
You can easily execute a stored procedure through the mechanism of creating an Access pass-through query. To create a pass-through query, create a new query and choose Query | SQL Specific | Pass-Through from the menu. Type the following in the SQL window:
EXEC procSelectCustomer
Then, select Query | Properties and set the pass-through query's options in the Query Properties dialog box (see FIGURE 7).
There are two important options you need to set:
ODBC;DSN=MyDSN;Description=MyDSN to SQL Server;DATABASE=MyDB;Trusted_Connection=Yes
When you execute the stored procedure, you can view the results in datasheet view (see FIGURE 8).
Now, click the Pass-through Query button on frmMain to open the example. You can base forms, reports, the Rowsource for list boxes, and combo boxes on pass-through queries, as well as use pass-through queries to execute stored procedures that don't return any records.
Executing Stored Procedures Using ADO Creating a pass-through query every time you wanted to execute a stored procedure isn't always the most efficient way of going about things. Executing stored procedures using either an ADO Connection or Command object will further improve performance and cut down on the clutter of saved query objects in your database.
The following stored procedure, procInflatePrice, takes a single parameter, @percent, and updates the UnitPrice column in the Products table by that amount. To run the example in the Connect2K.mdb sample database, you will need to create it in the Northwind sample database:
CREATE PROCEDURE procInflatePrice ( @percent MONEY = NULL ) AS SET NOCOUNT ON
IF @percent = NULL RETURN
UPDATE Products SET UnitPrice = UnitPrice * @percent WHERE Discontinued = 0 GO
Once you've created the stored procedure in the Northwind database, click on the Execute with Parameter button on frmMain. This runs the ExecuteWithParameter procedure, which uses an ADO Command object to execute the stored procedure. The user is prompted for the amount to increase the prices in the Products table in the Northwind database. Then the CommandText and CommandType properties are set to the name and type of the stored procedure. The @percent parameter is a member of the Command object's Parameters collection, and the value is passed to it by the strAmount variable. The Execute method of the Command object then executes the stored procedure (see FIGURE 9).
Public Sub ExecuteWithParameter() Dim cmd As ADODB.Command Dim strAmount As String
On Error Resume Next strAmount = InputBox( _ "Enter the percentage to increase prices", _ "Raising Prices", 1.1) If Len(strAmount & "") > 0 And IsNumeric(strAmount) Then Set cmd = New ADODB.Command cmd.ActiveConnection = "Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=Northwind;" & "uid=sa;pwd=" cmd.CommandText = "procInflatePrice" cmd.CommandType = adCmdStoredProc cmd.Parameters("@percent") = CCur(strAmount) cmd.Execute If Err = 0 Then MsgBox "Prices raised by " & strAmount Else MsgBox Err.Number & ": " & Err.Description End If Set cmd = Nothing End If End Sub FIGURE 9: Executing the stored procedure.
This gives you a general idea of how you can get started working with stored procedures using the ADO Command object. You could also modify the unbound form example shown earlier to use stored procedures instead of directly querying tables. Of course, you'd have to write the stored procedures first!
Access Projects Access projects (ADPs) are new in Access 2000. Although an ADP can be opened in Access and show objects in a database container, it's not a database itself. That's because Access databases use the Jet engine for their data storage, and Access projects use an external database. An Access project allows you to connect to the following databases:
The biggest difference between MDBs and ADPs that will have an impact on you as a client/server developer is that there is no local storage for queries. Data objects in an Access project are located on the server and application objects are located in the ADP (see FIGURE 10).
FIGURE 10: Data and application objects in Access.
In other words, when you use an Access project, you're connecting directly to SQL Server and all of the data-containing objects live on the server. Any changes you make to the design of tables, views, relationships, or stored procedures take place at the server, not in the ADP. Only user interface and code objects are stored locally in the ADP file.
Working with Data in ADPs Access uses the Visual Data Tools designers for tables, views, stored procedures, and database diagrams in Access projects. These tools will be familiar to you if you've worked with SQL Server data from within the SQL Server Enterprise Manager, or from the DataView window in Visual Basic. The most advanced of these tools is the View Designer, which offers up to three synchronized panes showing your view (see FIGURE 11). For some reason, the Access designers chose not to include the live data pane that other Visual Data Tools clients can display.
When you open a datasheet in an Access project, Access retrieves the data from the server as an updateable snapshot. This data is cached locally as soon as it's retrieved, so that operations, such as moving to a different record within the recordset, do not require communicating with the server. If you change a record, Access sends just that change back to the server. The data retrieval is done asynchronously, so Access will start populating rows of the datasheet before all of the data has been retrieved.
Forms, data access pages, and reports in Access projects are nearly identical to the familiar versions you know from Access databases. However, there are a number of new form properties that you should know about. The following are designed to help you optimize forms for a client/server environment:
If you need to work with data in an ADP programmatically, ADO is the only way to go. The ADO library is loaded by default in an Access project. In fact, the DAO library isn't even available, which makes sense, because the Jet engine isn't loaded. Most often you'll want to work with data in the same project SQL Server database that's being displayed in the user interface. The CurrentProject object refers to the current project and contains the following collections:
CurrentProject's BaseConnectionString property returns a valid ADO Connection object to use with the data, which opens a recordset on the Category table in the sample Northwind Access project that ships with Access 2000:
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.BaseConnectionString rst.Open "Select CategoryName from Categories" Do Until rst.EOF Debug.Print rst!CategoryName rst.MoveNext Loop
Conclusion So how do you choose a method for Access 2000/SQL Server 7.0 applications? There are a few points to consider. Linked tables are the easiest way to do quick-and-dirty prototyping for an application. However, because linked tables don't offer any way to limit the number of records returned, they're not well suited for most production applications. Access projects offer a nice integration of the Access user interface directly on the SQL Server engine. For new applications, this is an attractive proposition. Be warned, though, that this is version 1 technology, and there are some rough edges. If you're converting an existing Access application to client/server, you'll probably find that unbound forms with programmatic data access provide the best balance of speed of development and features.
The file accompanying this article is available for download.
Mary Chipman, MCSD, MCT, is a regular contributor to Smart Access, a senior consultant for MCW Technologies, and a Microsoft Solution Provider. She is a co-author of Access and SQL Server Developer's Handbook (SYBEX, 1996) and SQL Server 7 In Record Time (SYBEX, 1998). You can reach Mary at mailto:mchip@mcwtech.com.
Mike Gunderloy, MCSE, MCSD is a Senior Consultant with MCW Technologies, a Microsoft Solution Provider, and the author of numerous books and articles about Access, Office, Visual Basic and VBA. His most recent book is Visual Basic Developer's Guide to ADO (SYBEX, 1999), and he's currently at work on Mastering SQL Server 7.5 (SYBEX). You can reach Mike at mailto:MikeG1@mcwtech.com.
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
| ||||||
.gif){kind=small}
