When you start using Access as a front end for a server database, the rules change. This article introduces the fundamental techniques for delivering effective client/server applications with Access.
There are many reasons why you might want to upsize your application to use a remote database server like SQL Server 7.0 or Oracle. Reasons that you might want to move your back-end database to a remote server database include, among others, the ability to support an increased number of users, to support transactional consistency, to handle larger and more complex databases, and to improve security, to name just a few. However, simply upsizing won’t guarantee that your application will automatically run faster, support more users, or be more secure.
When you use a server database, you have a variety of options on how to use the data with your Access application. Some of these options require very little work, such as linking the tables on the server to your front-end database, whereas other options require more code or even a redesign of your application. In this article, we’ll survey some of the techniques that you can use to continue working with your data from Access, even after the data itself is stored in SQL Server or some other remote database. If you’re starting an Access client/server application from scratch, we’ll show you what to do to get the best performance from your server database. We’ll only discuss Access 97 techniques, but you should be aware that Access 2000 will offer significant new functionality in this area with the new Access Project file type, which natively marries the Access user interface with SQL Server data.
If you’re upsizing to Microsoft SQL Server, you’ll be happy to know that Microsoft has released a new version of its popular Upsizing Wizard that will take care of a lot of the gory details for you. It’s available on Microsoft’s Web site at http://www.microsoft.com/AccessDev/ProdInfo/AUT97dat.htm.This is a maintenance release that adds the ability to cleanly upsize an Access 97 database to SQL Server 7.0. Most Access applications are split into a back-end database containing tables and a front-end database containing everything else (queries, forms, reports, macros, and modules). The Upsizing Wizard will take care of the table, or back-end database, since your front-end database will have no relevance to SQL Server. After you run the wizard, though, you still have work to do in order to get the best out of your new client/server application.
You need to take steps to optimize your application to take advantage of your server’s features and move away from the way you did things when you had an all-Access database application. The following goals should be taken into consideration as you move to client/server development:
- Divide the processing of tasks efficiently between Access and your back-end server. This means letting the server do all of the data processing, not fetching the data and then having Jet process it in the application.
- Reduce the amount of data exchanged between the server and your Access application. You’ll want to limit query results to return the bare minimum required. This reduces network traffic and the load on the server (which other users will appreciate), while giving a performance boost to your application.
- Make your application scalable by releasing resources early or not using them at all. Don’t needlessly hold locks or involve users in transactions where records can be potentially locked for long periods of time.
In the following sections, we’ll discuss some of the techniques you can use meet these goals.
Linked tables and views
The easiest way to work with your server’s tables is to link them. You can work with SQL Server tables via ODBC in the same way you would with native Access tables or linked tables in other database formats. You can browse the tables in datasheet view, run queries against them, and create forms and reports based on them. If you don’t want to deal with an entire table, you can create a view in your server database that’s based on the table and link that view to your Access database. The rules for updating views are similar to the rules for updating data through queries in Access (for instance, you can’t update data if the view contains any aggregate functions).
As an example, to link to SQL Server tables or views, you’d follow these steps:
- Select the File menu, point to Get External Data, and click Link Tables. You’ll be prompted by the Link dialog box to select a database.
- Select ODBC Databases() from the Files of Type combo box. You’ll be prompted by the Select Data Source dialog box to select a data source. Click the Machine Data Source tab.
- Select the name of an existing data source, or create a new data source, and click OK.
- With SQL Server, you’ll then need to choose the security context of the connection. If you use NT authentication, no further action will be required. If you use SQL Server authentication, you’ll need to provide a user ID and password.
- Once you’ve been validated by the server, you’ll be able to select a default database (the database to which you want to link), and then you’ll be able to select the tables or views you want to link.
At first glance, linked tables appear to be an ideal solution, since you don’t have to rewrite your application–just link up and go. As long as your server tables have unique indexes (or primary keys), you’ll be able to work normally with them. However, what worked well in Access doesn’t necessarily carry forward into the client/server world, particularly if you’re trying to meet the design goals listed previously.
For example, most forms in an all-Access application are bound to entire tables or queries that return all of the rows from joined tables to give you a fully editable data set. If you further filter the data after the form has been opened, it means that the selection of records is taking place in the front end, not the server. Furthermore, returning all rows makes no sense if the purpose of the form is to edit data. After all, a user can only work with one row at a time. One way to get around the problem is to use unbound forms, as described in the next section.
Unless you’ve been living in a cave for the past year or so, you’ve already run across ActiveX Data Objects, usually known as ADO. ADO is the successor to DAO (and other data access libraries such as RDO) and benefits greatly in speed and simplicity from Microsoft’s experience with data access in the past. For example, ADO lets you create a Recordset object directly, without needing to clutter up your code with any other objects. If you haven’t already installed any software using ADO (for example, Internet Explorer 5.0, Visual Basic 6.0, or SQL Server 7.0), you can download the latest version from the Microsoft Universal Data Access Web site at http://www.microsoft.com/data/download.htm. Even if you’ve already installed ADO, you’ll want to check the Web site for updates. As of this writing, the only way to get the latest ADO was directly from the Web site.
Access 2000, in fact, uses ADO as its default data access library. But there’s no reason you can’t use ADO from Access 97 as well. The only problem is that you can’t bind things like Forms and Reports to ADO data sources, so you have to do more work in code. In a client/server environment, this is the appropriate choice, anyway, so moving to ADO makes a lot of sense when you upsize your application.
To demonstrate this technique, we’ve provided a sample Access 97 database called Connect.mdb in the accompanying Download file that uses ADO to retrieve data from the version of the Northwind Traders sample database that ships with SQL Server 7.0. To use the sample, open frmSelectRecordsource and enter the name of your SQL 7.0 server, a table name, and a valid user ID and password with rights on the server. Then click OK. The sample will build a data entry form based on the selected table and let you navigate through and edit the records. Figure 1 shows a form built by the sample.
While you can use ODBCDirect (or even DAO) for the code behind unbound forms, the sample’s code takes 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–you never know if some other client has cached changes to a record you’re working with.
Once you’ve selected a data source in the sample’s opening dialog box, the code creates a global Recordset using this GetRecordset procedure:
Sub GetRecordset(strServer 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=Northwind;Data Source=" & _ strServer End With End Sub
The combination of CursorType, CursorLocation, and LockType chosen here results in a Recordset on the client in batch-editing mode. Note that the Recordset is defined as being of type “ADODB.Recordset” rather than just “Recordset”–since ADO and DAO objects have the same names, you need to fully qualify ADO object names when you’re working in an Access 97. Without the ADODB prefix, your code might create a DAO Recordset, which won’t work with this code.
We’ve made this sample form as flexible as possible so that it can work with any table you aim it at. After opening the Recordset, frmSelectRecordsource uses the CreateControl function to build the form on the fly by adding text boxes for each field in the Recordset:
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, txtRecordsource, _ txtUserID, txtPassword & "" DoCmd.OpenForm "frmData", acDesign 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 DoCmd.OpenForm "frmData", acNormal End Sub
The GetFields procedure can be found module basADO in the sample database. The module simply returns a semicolon-delimited list of all of the fields in the Recordset. Note that the .BeforeUpdate properties of the created text boxes 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 Form frmData itself uses straightforward code to move through the Recordset when you push the previous and next buttons, and to save changes when you modify a field. For example, here’s the code to support the Move First button:
Private Sub cmdFirst_Click() grst.MoveFirst GetCurrentRecord End Sub 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
This is all of the code required to move data from the first text box to the fields in the Recordset:
Private Sub Text1_BeforeUpdate(Cancel As Integer) grst.Fields(0).Value = CStr(Me!Text1) End Sub
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 (Russell Sinclair’s article in this month’s issue–”Everything You Need to Know About ADO Recordsets”–provides more information on the Recordset object):
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 create forms on the fly. But this example does show how easy it is to get started with client/server development in ADO in Access 97.
Using stored procedures for server-side updates
Another way to take advantage of your server is to use pass-through queries to bypass the Jet query engine, speeding up data operations and reducing network traffic. For SQL Server, you can enter Transact-SQL statements that are passed directly to SQL Server for processing. Pass-through queries bypass any syntax checking, interpretation, or translation of the SQL by the Jet database engine.
One of the most important functions of a pass-through query is that you can execute stored procedures on the server. A stored procedure is a group of statements compiled on the server into a single execution plan. Stored procedures called with pass-through queries allow you to work directly with the server, eliminating the middle layer of translation and interpretation provided by Jet.
Stored procedures can be used to carry out actions, return information, or manipulate data. SQL Server comes with built-in system stored procedures that you can use to manipulate the server and perform administrative tasks. With most of the major database servers, you can also define your own stored procedures to process or return data using their proprietary language (for SQL Server, that’s the Transact-SQL language).
For instance, in our previous example, we could have used a stored procedure to return the data and called it from the GetRecordset procedure instead of opening the Recordset on the table directly. Using stored procedures in this way provides an extra layer of abstraction between your application and the tables. Changes to your database structure might only affect your stored procedures, eliminating the need to re-code your applications. By calling a stored procedure, you can remove all permissions from your SQL Server tables to prevent users from browsing them directly. You could then write stored procedures to retrieve data, and grant the appropriate Execute permissions on the stored procedures. The stored procedure will run based on those Execute permissions, superseding the permissions set on the tables.
Creating stored procedures
Since the process for creating stored procedure varies from server to server, we’ll restrict this discussion to SQL Server. SQL Server 7.0 boasts a wizard that will help you create stored procedures to insert, update, or delete rows from a table. Select Tools | Wizards | Database | Create Stored Procedure Wizard from the MMC main console window, and the wizard will step you through the process of creating a stored procedure to modify data. You can also create stored procedures by hand. The following stored procedure takes a single parameter, @Category, and increases the UnitPrice of products in that category by 10 percent:
CREATE PROCEDURE procPriceIncreaseByCategory @Category int AS Update Products Set UnitPrice = UnitPrice*1.1 WHERE Discontinued = 0 AND CategoryID = @Category
You could create an equivalent query in Access and run it against a linked table, but the query would be processed locally and would be much slower. By using the stored procedure, all of the processing takes place on the server.
To run the procPriceIncreaseByCategory stored procedure (or any other stored procedure), you’d begin by creating a new pass-through query. To do this, create an empty query, select SQL Specific from the Query menu, and click Pass-Through. You then need to set the query’s properties, beginning with connection information in the ODBC Connect Str property. Since our sample procedure doesn’t return records, a query that called it would also have its Returns Records property set to No. In the SQL window, you’d then type the name of the stored procedure, followed, for our sample query, by the CategoryID of the category of products to which you wish to apply the price increase. The resulting pass-through query would look like this:
Figure 2 shows the pass-through query syntax along with the query properties.
When you execute the query, the prices will be updated on the server. You can modify your pass-through queries at runtime by using VBA to modify the .SQL property of the QueryDef object. In our example, that would let you supply different CategoryID values as you re-ran the query.
Executing stored procedures using ADO
Having to create a pass-through query every time you want to execute a stored procedure could get cumbersome. You can further improve the performance of your client/server applications by executing stored procedures directly using an ADO Connection object. The following procedure uses the Connection object’s Execute method to run the stored procedure, passing it the product number to update:
Public Function ExecuteStoredProc( _ intProd As Integer) Dim cnn As ADODB.Connection Set cnn = New ADODB.Connection cnn.Open "Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=Northwind;" & _ "uid=sa;pwd=" Call cnn.Execute("ProcPriceIncreaseByCategory " _ & intProd) End Function
You can also use a Command object to execute stored procedures with multiple parameters, taking advantage of the Command object’s Parameters collection. Parameters let you create stored procedures that can be tailored or modified by the data that you pass to them. Consider the following stored procedure, procShipperAdd, which inserts a new row in the Shippers table in the Northwind database. You need to supply two parameters: CompanyName and Phone. The ShipperID is an Identity column, which will automatically increment. The stored procedure, after inserting the new Shippers record, then returns the new ShipperID as an output parameter (again, this procedure is in SQL Server’s Transact-SQL language):
CREATE Procedure procShipperAdd @CompanyName nvarchar(40), @Phone nvarchar(24), @ShipperID int = NULL OUTPUT As SET NOCOUNT ON INSERT INTO Shippers (CompanyName, Phone) VALUES (@CompanyName, @Phone) SELECT @ShipperID = @@IDENTITY
The following function, ExecuteShipperAdd, returns the ShipperID added by the stored procedure. It creates a Command object and sets the CommandText to the name of the stored procedure and the CommandType to adCmdStoredProc to indicate that it’s a stored procedure (and not a SQL statement) that’s being executed. The code then uses the Command object’s Parameters collection to pass the two parameters. Once the stored procedure has been executed, you also access the ShipperID from the Parameters collection:
Public Function ExecuteShipperAdd( _ strShipper As String, _ strPhone As String) As Long Dim cmd As ADODB.Command Set cmd = New ADODB.Command cmd.ActiveConnection = _ "Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=Northwind;" & _ "uid=sa;pwd=" cmd.CommandText = "procShipperAdd" cmd.CommandType = adCmdStoredProc cmd.Parameters("@CompanyName") = strShipper cmd.Parameters("@Phone") = strPhone cmd.Execute ExecuteShipperAdd = cmd.Parameters("@ShipperID") End Function
As server databases become more prevalent, Access’s ability to work in client/server environments will become more important to you. In this article, we’ve just scratched the surface when working with server-side data in Access. In doing that, we’ve provided you not only with some good advice on accessing server-side data but also included the ADO code that you’ll need to implement that advice. Armed with these tools, you can start to explore the world of server-based applications.
Other Pages On This Site You Might Like To Read