Changes should be made to the application that make better use of SQL Server's features.
It's convenient, it's common, and we've all done it. But now it's time to stop. Don't open tables in datasheet view and look around for the data you want. That was fine when your database was used only by you, when the tables were small, and the data was in the .mdb file on your PC. But now you are in a multi-user environment sharing both the data and the network resources. When you open a table in datasheet view, you are viewing all of the records on that table. Instead of 500 or 5,000 records, you may be asking for 50,000 or 450,000 records.
Instead, ask your application users what record they really want to see, then query the server for just that record. Instead of presenting a list of all your customers to your users, request the first few characters of the customers' name.
Use letter buttons to reduce the number of records selected. The Customer Phone List form in the Microsoft Access Northwind database is an excellent example. This particular form uses a macro to filter the records. Do not use this method with attached tables. Instead, have each button fire off a query with the appropriately restrictive WHERE condition.
When you do need to browse, keep it short. If you only need to browse the polar bears living in Alaska, use a query that excludes those in the Yukon and Siberia. If you only need the name, don't browse the name and account number. Everything you browse travels over the network. One of the critical tests of a well-developed application is its speed when scaled up to 50, 100, 500 or more users. The bottleneck on many systems is the network capacity, so treat it carefully in your applications.
If you need to browse an entire table, keep a copy locally and update it monthly, weekly, daily, or when the application first connects. A local copy won't show updates since you last copied it, but often that isn't important. Don't browse the list of all employees when you can download the names of the employees in the accounting department once a week. Add a button to refresh the list. Use replication from SQL Server to Microsoft Access to keep a local list current.
For example, when your application was in Microsoft Access, users would browse a list of the 3,000 products from the products table and select the one they wanted. Then they would order by using the associated part number.
To migrate from Microsoft Access to SQL Server, upsize the products and orders table. Replicate the products table to the Microsoft Access .mdb file. This places a copy of the products table into your Microsoft Access .mdb. In your application, attach to the SQL Server orders table and the local copy of the Microsoft Access products table. Now browsing the products table locally will not incur network overhead. When the user selects a product, the application uses the local copy to derive the product ID and sends an insert query to the attached orders table on the server. For efficient and secure processing, create a stored procedure on the server that accepts variable portions of the insert query as arguments. Your next decision is to determine the optimal replication period. You could use Pull Replication to download the products table if the user clicks a button, Replicate on the first day of each month, or Replicate any time there are changes to the products table.
Recordsets of the type snapshot download all of the data immediately. Recordsets of the type dynaset download a small group of records and then download pointers to the rest of the data. Use a recordset of the snapshot type if:
Under these conditions, the recordset opens more quickly with snapshot than with dynaset. For combo boxes and list boxes, use a snapshot and keep the number of records as small as possible. SQL pass-through queries always return read-only snapshots.
One of the most critical elements of a multi-user application is efficient use of the network. Once you convert to client/server architecture, all processing is no longer performed on a local PC with Microsoft Access. The fastest client/server applications optimize for fewer network roundtrips.
Think of the network as a bottleneck that takes a performance hit every time it is used. Instead of updating record #1, then updating record #2, send one message to the server that updates both records at once. For example, you need to change the discount rate of these three customers because of a new contract. Instead of:
UPDATE customer SET discount = 10 WHERE CustomerID = 5
and then
UPDATE customer SET discount = 10 WHERE CustomerID = 15
and then
UPDATE customer SET discount = 10 WHERE CustomerID = 72
Look for opportunities to batch updates together. Use:
UPDATE customer SET discount = 10 WHERE CustomerID = 5 OR 15 OR 72
Now you have only one statement and one network hit. This query is three times faster than the first three queries.
If you always update two records as a set, batch them together. Instead of:
INSERT orders (ID, qty) VALUES ('AAA', 17)
Then
INSERT RunningTotal(ID,qty) VALUES ('AAA', qty + 17)
Use a stored procedure to do both at once. Use logic in the stored procedure on the server to update both tables. Here is a sample stored procedure:
CREATE PROC sp_NewOrder
(@CustID Char(3), @QtyIn INT)
AS
INSERT orders (ID, qty)
VALUES (@CustID, @QtyIn)
INSERT RunningTotal(ID,qty)
VALUES (@CustID, @QtyIn)
When it's time to send the record, use a pass-through query:
sp_NewOrder('AAA',17)
Now you are sending 21 characters on one network trip instead of 79 characters on two network trips.
Always use set (in the sense of group) operations if possible. Opening recordsets on attached tables or opening server-side cursors is almost always slower than using a set operation. For example, if you are updating the price of all type A products by 10 percent, send one query for the entire set of type A items.
UPDATE products
SET (qty = qty * 1.1)
WHERE type = 'A'
(Do not confuse the SET in the line above, which has the meaning of "adjust," with the word set in "set operations," which means "group operations.")
Often, you need to compare the value of some current record with the value of the previous record. Use a query that treats the data as a set operation.
The pubs database contains a jobs table that has a sequentially numbered job_id field and a tiny integer (tinyint) field called max_lvl. This query reveals the values.
SELECT job_id, max_lvl FROM jobs
Here is a query to compare the value of max_lvl in the current record to its value in the previous record.
SELECT orig.job_id,
"Previous_Record" = previous.max_lvl,
"Current_Record" = orig.max_lvl,
"Previous-Current" = convert(int,previous.max_lvl) - convert(int,orig.max_lvl)
FROM jobs orig JOIN jobs previous
ON orig.job_id = previous.job_id + 1
Here is the output showing the comparison.
| Job ID | Previous Record | Current Record | Previous-Current |
| 2 | 10 | 250 | 240 |
| 3 | 250 | 225 | 25 |
| 4 | 225 | 250 | -25 |
| 5 | 250 | 250 | 0 |
| 6 | 250 | 225 | 25 |
| 7 | 225 | 200 | 25 |
| 8 | 200 | 175 | 25 |
| 9 | 175 | 175 | 0 |
| 10 | 175 | 165 | 10 |
| 11 | 165 | 150 | 15 |
| 12 | 150 | 100 | 50 |
| 13 | 100 | 100 | 0 |
| 14 | 100 | 100 | 0 |
The convert function is necessary because the max_lvl field has a data type of tinyint and cannot contain negative values. The technique of joining a table to itself by using an alias for the second instance of the table can be used in Microsoft Access as well as in SQL Server. Under most circumstances, a set operation like the one above will operate more quickly than stepping through the recordset and making the comparison one row at a time. Set operations should be used whenever possible.
For more information about self-joins, search the Microsoft Access Help.
A key decision you must make is which tables to store locally and which tables to store on SQL Server. If you are not planning to use Microsoft Access as your front end, move all of your tables to the server. If you are connecting to SQL Server from Microsoft Access, remember that you can retrieve data from a local table in Microsoft Access much faster than from the SQL Server across the network. Don't automatically assume that all of your tables should be upsized. The following two requirements are compelling enough to warrant upsizing your Microsoft Access tables to SQL Server.
A table should be kept on the server if it contains data that must be current when accessed by multiple people or one person from several locations. This does not describe a table of the 50 states, the departments in your company, or the tax rates applicable in various parts of your state. Those items don't change very often. Rather, this could describe your company's product catalog. If it only changes monthly, you might be able to distribute monthly changes.
The server must have all the information it needs to resolve a query. If you send to the server an INSERT statement with a stock number and want to save the extended price (price times quantity) the server must have a copy of the products table that stores the price. This doesn't preclude you from keeping a copy locally, too. Some tables should be stored both on the server and kept locally. There is a danger here. If you have a query that joins a server and a local table, you will have a slow query. Make sure that your queries join only tables on the server. If you create a query in Microsoft Access that joins a server table with a local table, the Microsoft Access Jet Engine pulls information from the server and resolves the query locally. This can be much slower than having the server resolve the query.
All of your business rules should be moved to the server. Primary and foreign key constraints are enforced through Declarative Referential Integrity or triggers. Unique constraints are enforced by a unique index. In Microsoft Access, set the required property of a column to Yes to prevent a null value. In SQL Server, define a field as NOT NULL when the table is created or by uses a trigger (the method used by the Upsizing Wizard).
The concepts of rules and defaults are the same in SQL Server as in Microsoft Access but implemented differently. The Upsizing Wizard moves rules and defaults defined in the Microsoft Access table to SQL Server, but the rules and defaults don't have to be placed in the Microsoft Access table. You can place them on fields in forms or perform more complex validations in code modules. However, if the rule is in the form, a user who enters a value directly to the table without using the form bypasses the validation rule and can enter invalid data. This problem remains after the table has been moved to the server. If the rule is in the application, a user who connects from some other application can insert invalid data into the server table. Because of this, you must move the rules and defaults to SQL Server.
In SQL Server, rules and defaults are created apart from the table and then "bound" to it. One rule or default can be bound to many columns and many tables. In contrast, the Upsizing Wizard creates a new rule and default for each column of each table except for a default to zero. The Upsizing Wizard creates a default named UW_ZeroDefault and reuses it as needed.
The Seek command in Microsoft Access opens the table or index and reads all of it until it finds the bytes that you are looking for. This command is not available in SQL Server and cannot be used on attached SQL Server tables from Microsoft Access clients. If your VBA code uses the seek command, open a recordset with a WHERE clause. The WHERE clause restricts the record set. Use wildcard characters as necessary.
Though the concept of transactions is the same in Microsoft Access and SQL Server, the reason for using transactions varies. One reason to use transactions in Microsoft Access is to keep a query plan in memory. If you want to make 10 updates to a table using the same format, once the first update is made, the subsequent nine updates wrapped in the same transaction use the same optimized query structure. In SQL Server, a stored procedure, even a temporary one, automatically holds the query structure in its procedure cache. More importantly, a single wrapped transaction can fail if two updates occur to the same record or on the same datapage.
SQL Server supports only one level of transactions. Multiple levels of transaction nesting in a Microsoft Access application must be removed.
Microsoft Access and SQL Server also accommodate transactions to achieve atomicity, the linking together of two or more statements so that they both succeed or both fail. The classic example uses a banking situation. If you are transferring money from a checking account to a savings account, you want both transactions to succeed or both to fail. This avoids the problem of moving the money out of checking but failing to deposit it into savings. Microsoft Access and SQL Server differ with regard to updating records. Microsoft Access can, and often does, update the same record twice within a transaction, but this attempt fails on SQL Server. Look to the SQL Enterprise Manager Current Activity window to witness the locking behavior.
For example, using the Microsoft Access client, you connect to SQL Server and obtain a server process with the SPID (Server Process ID) 17. Using SPID 17, Microsoft Access inserts the record, asking SQL Server for an exclusive lock on that record and possibly locking the whole page containing the record. The next item in the transaction updates another field on the record just entered. Microsoft Access opens another SPID 18 on the server and attempts to get an exclusive lock on the same record to update it. SQL Server does not know that SPID 17 and SPID 18 are connected to the same transaction. The lock held by SPID 17 prevents SPID 18 from completing. Eventually, one of these SPIDs times out and returns a failure code to Microsoft Access. Microsoft Access rolls back the other SPID. The Microsoft Access program hangs for the duration of the timeout setting until the transaction fails and rolls back. This can be solved in SQL Server 6.5 by linking the two SPIDs using BOUND CONNECTIONs. For information about BOUND CONNECTION, see SQL Server Books Online.
To program around this, write stored procedures that wrap several actions in a single transaction. When the server manages the whole process, it understands the dependencies of the activities and prevents blocking.
SQL Server 6.0 does not support the LEFT OUTER JOIN syntax of Microsoft Access. The equivalent functionality is available using "*=". An example:
SELECT pub_name, title
FROM publishers, titles
WHERE
publishers.pub_id *= titles.pub_id
Under some circumstances this syntax can be ambiguous. SQL Server 6.5 supports the ANSI standard syntax:
SELECT pub_name, title
FROM publishers LEFT JOIN titles
ON
publishers.pub_id = titles.pub_id
This second ANSI standard is preferred. The use of the word OUTER is optional.
Microsoft Access field-level validation rules are evaluated when users leave a field. In SQL Server, the validation rules do not fire until the record is saved. This difference may require changes to your application forms.
Microsoft Access fills in default values and provides Autonumber values when you start editing a record. SQL Server does not provide those values until the record is saved.
Opening a recordset in Visual Basic for Applications code on a SQL Server table with an Identity field requires the dbSeeChanges option. For more information about this option, see OpenRecordSet in Microsoft Access Help.