This is an exciting time for PC database users and developers. Microsoft® SQL Server™ continues to be the leading high performance database for PCs. Running on Microsoft's rock solid Windows NT® operating system, it has brought unprecedented ease of use to a stable, high security environment.

There has never been a better time to start migrating your Microsoft Access database to Microsoft SQL Server. This paper helps you to complete the migration smoothly and optimize your application strategy for the fastest performance.

Version Differences

Both SQL Server and Microsoft Access exist in various versions. An explanation of the versions and which Upsizing Wizard to use for upsizing is discussed.

SQL Server

Microsoft SQL Server has three principal versions:

• SQL Server Version 4.21a and its predecessors were developed jointly with Sybase.

  This version lacks the Declarative Referential Integrity (DRI) familiar to Microsoft Access users. Only the Microsoft Access 2.0 Upsizing Wizard can reliably move data from Microsoft Access 2.0 to SQL Server 4.21a. This Upsizing Wizard should not be used to move a Microsoft Access 2.0 database to any version of SQL Server except 4.21a.

• SQL Server Version 6.0 (completely rewritten).

  It includes many new features including DRI and the handy SQL Enterprise Manager for administration of the server. You must have Service Pack 2 for SQL Server 6.0 to use the Microsoft Access for Windows® 95 Upsizing Wizard.

• SQL Server Version 6.5 (similar to 6.0 and is considered a maintenance release).

  There were very few changes to the base features of SQL Server 6.0, although many new features was added. These include improved database engine performance, row-level locking, replication to Microsoft Access databases, Internet/Intranet features, and the Distributed Transaction Coordinator. Your application can be converted to either 6.0 or 6.5 in exactly the same way. The Microsoft Access 95 or 97 Upsizing Wizard automates this process, and those versions are reviewed in this paper.

MS Access

Microsoft Access version 1.0 and its enhancement, version 2.0, have been setting the standard for desktop databases.

Microsoft Access for Windows 95 uses 32-bit architecture fortified with many customer requested features. For the best performance in upsizing a Microsoft Access 2.0 database, it is recommended that you open the database with Microsoft Access for Windows 95, convert it to Microsoft Access for Windows 95, and then use the Upsizing Wizard for migration to SQL Server 6.5.

Though there are differences between Microsoft Access 95 and Microsoft Access 97, the Upsizing Wizards are nearly identical.

Why Migrate from MS Access to SQL Server?

It is a good idea to migrate your Microsoft Access applications to SQL Server if your database or database application:

• Can benefit from client/server architecture.
• Has grown so large that incremental backups are warranted.
• Must run 24 hours a day, 7 days a week.
• Must run many processes in parallel.

This section explores the client/server paradigm, first with a metaphor, then by comparing the Microsoft Access database model to the SQL Server database model. Finally, backing up data with Microsoft Access and SQL Server is discussed.

A Client/Server Metaphor

Using the fictional marketer Jerry and his information requirements, the file server method of Microsoft Access is compared to the client/server paradigm of SQL Server.

The File Server Method

Jerry works in the Marketing Department on the 3rd floor. He needs to review all of the March orders placed by the XYZ company, stored in the Accounting Department on the 7th floor.

Jerry takes the elevator to the 7th floor, where he presents his company badge to the security guard. The security guard sticks it in a pigeonhole behind the security desk.

Jerry checks out a copy of the orders index. He takes it to the elevator and goes back to the 3rd floor.

Jerry opens the alphabetical index to the letter X and finds XYZ. He sees that there are three orders, and that they are in file cabinet drawer 54, folder six; cabinet drawer 70, folder one; and cabinet drawer 95, folder seven.

Jerry takes the elevator to the 7th floor. He has already identified himself to the security guard so he walks in. He tells the security guard that he would like copies of those three files. The security guard walks with him to the Orders room and unlocks file cabinet drawers 546, 701, and 957. The security guard takes them to a photocopy machine and makes copies of all the contents of the drawers although Jerry needs only three records. Jerry takes his 60 pages of copies back to the 3rd floor.

Jerry looks through the stack of copies of the orders, selects the three that interest him, and places them on his desk. He discards the other 57 pages.

If Jerry wants to update these three orders, Jerry cannot get photocopies of the file drawers. Only the originals can be modified. In this case, the security guard unlocks the file drawers and gives Jerry the original orders. The security guard then throws a special padlock on the file drawer and posts a sticky note on the bulletin board behind the security desk.

Alternatively, the security guard could automatically unlock all of the drawers and not post the sticky notes. If Mary arrives at the 7th floor after the security guard has opened the file drawers for Jerry, the security guard won't let her past the front desk. Only after Jerry is finished can Mary get in. Jerry still must pull the index file and take it back to his 3rd floor office, then return to the 7th floor a second time to get the data.

Jerry has been in the elevator four times:

• He went to the 7th floor to get the order index.
• He returned to the 3rd floor to read the index.
• He traveled back to the 7th floor to get copies from the drawers.
• He returned to the 3rd floor to read the copies and dispose of those copies he did not need.

Additionally, he had to retrieve all 60 records himself.

The Client/Server Model

Jerry still needs to review all of the orders placed by the XYZ company in March, stored in the Accounting Department on the 7th floor. Jerry still works in the Marketing Department on the 3rd floor.

When Jerry goes to the 7th floor the first time each day, he shows his company badge.

Jerry takes the elevator to the 7th floor and requests the brochure for the Orders database. Jerry takes the elevator back to the 3rd floor to read the brochure. If Jerry has been to the Orders department before, he already has this brochure so he skips this step.

Jerry tells the helpful staff at the service counter which orders he needs. After receiving Jerry's request, the service desk attendant rings a bell and an assistant fetches the records. The assistant opens the three file drawers and reads the entire contents of the drawer (all 20 records for each drawer) but copies only the records that Jerry needs. While the assistant is copying the first record, the service desk attendant sends another assistant to read the other two records. When the first assistant finishes reading the first file drawer, the second attendant shares the contents of the second file drawer with him, then hurries off to read the third file drawer. When all requested drawers have been read, and the order information extracted, the first assistant returns to the service desk and hands Jerry copies of the three requested records. Jerry takes the elevator back to the 3rd floor.

If Jerry had wanted merely the total of sales for March rather than the complete record, the assistant would have added them up for him. This would have saved Jerry the trip back to his office to perform the calculation.

Except for the often-skipped trip to get the brochure, Jerry has been in the elevator only twice—one round-trip:

• He went to the 7th floor with his initial request.
• He returned to the 3rd floor with the records.

If multiple users request information from the server, the Microsoft Access file server method must serialize each extraction of data pages. Each user must wait until the one security guard is available to unlock file drawers. In contrast, SQL Server's client/server model can take full advantage of the ability of Windows NT to use multiple processors. SQL Server can start multiple threads to manipulate the data and can schedule these threads to run on any available processor. In short, SQL Server can have many users acting as data runners for the service desk.

ISAM Databases

Why are people saying that a client/server system is better? It's not better, of course. It's different. It's only better if you need the features that client/server architecture provides. Microsoft Access is an Indexed Sequential Access Method (ISAM) database. To understand this, let's examine a primitive model: File Input Output.

Take an ASCII text file consisting of:

1,up ,5
2,down ,6
3,left ,7
4,right,8

What you can't see, but is present in the above contents, are carriage returns (ASCII character 13 abbreviated below as CR) at the end of each row. Each row has 10 characters in it (integer comma letter letter letter letter letter comma integer CR). With 10 characters in each of four rows, there are 40 characters plus an end of file character (EOF), making 41 characters in this file. Because each character takes one byte, there are 41 bytes in this file. A file input output system can read this primitive database by reading the 41 bytes into memory. If you want the first row, you can read bytes 0 through 9. If you want the third row you can read bytes 20 through 29. If you want to update the second row changing the 6 to a 9 you can write a new value to the 19th byte. This is still the basic system that Microsoft Access uses to read and update a text file.

If Microsoft Access wants to read the second row and prevent anyone else from changing that data while it is in use, Microsoft Access asks the network operating system to lock the file. If you try to read the file with Notepad while Microsoft Access is updating it, you will get an access denied error. The operating system's network feature locks the file.

Microsoft Access works similarly to the File Input Output model. There are three important differences:

• The Microsoft Access database.mdb file is large and usually won't fit into memory, so Microsoft Access doesn't read the whole file.

  If it requires four rows of a given table, it reads the description of the table from the "system" tables and then reads only the required bytes of the table. In other words, it gets the hard drive address of the table and reads a set number of bytes, then stops.

• Microsoft Access only locks the bytes it needs.

  The granularity of this lock is in sets of 2048 bytes called "pages." It locks the page that contains the record it needs, thereby locking any other records on the same page.

• Microsoft Access uses an index.

  When you create an index with Microsoft Access, the program adds a copy of the field or fields you are indexing to the index file. This added file or fields in the index file has a special structure called a B-Tree. Using this index, Microsoft Access can find the record containing the value "down" or "left" without searching the entire file. In a table of 5 or 50 megabytes, this functionality enables Microsoft Access to find the record in a fraction of a second.

The Microsoft Access .mdb file holds data in sequential order and uses an index to find records. Microsoft Access is an ISAM database. SQL Server is not.

For more information about the Microsoft Access index structure, see the Microsoft Jet Database Programmer's Guide.

Client/Server Databases

The first difference between Microsoft Access and SQL Server databases concerns who is getting the data. In the case of Microsoft Access, the data resides on the hard drive in the file with the .mdb extension (for example, Northwind.mdb). If this file is on machine1 and you have Microsoft Access on machine2, Microsoft Access asks the operating system on machine1 to supply you with the contents of bytes X through Y of the Northwind.mdb file. If someone on machine3 also connects to machine1, they can ask the machine1 to give them the bytes of C through D from the same Northwind.mdb. The operating system keeps track of who has what and who wants which bytes locked. Unfortunately, the operating system isn't really very good at this.

The only programs that know what the data is supposed to look like and who can intelligently manage it are the Microsoft Access programs located on machine2 and machine3.

These two programs cannot talk to each other; in fact, they don't even know they are sharing this mdb file. The first computer to open the .mdb file creates a second file with the same name but an extension of .ldb, and places it on machine1 right next to the .mdb file. If you open Northwind.mdb you also see a file called Northwind.ldb. (In Microsoft Access 2.0, the file stayed there after it was created. In Microsoft Access for Windows 95, the file is deleted when the last person using the .mdb file exits.)

After the .ldb file is created, each copy of Microsoft Access using the .mdb file "posts a sticky note" in the .ldb file that it has checked out some specified range of bytes. This is referred to as an Extended Byte Range Lock. With Extended Byte Range Lock, ranges of data are either locked and no one else can see them, or they are not locked and anyone can see them. There is no compromise.

With SQL Server, the data is still kept in a file on machine1's hard drive, but this time only one program at a time can get to it. The SQL Server program, Sqlsrvr.exe says "I have an exclusive lock on the whole file and anybody who wants any data has to ask me." Then the SQL Server program running on the server implements its own sophisticated locking plan.

First, there are more types of locks:

• Exclusive locks enable one user to lock the data while changing it so that no one else can read it.
• Shared locks allow multiple users to read the same data but none of them can change it.
• Shared intent locks allow a user to read shared data, but disallow others from obtaining an exclusive lock until the first user has changed the data and released the lock.

As with Microsoft Access, each of these SQL Server locks can be a lock on a specific page or a group of specific pages or a lock on a whole table. The server can also lock an extent, a group of eight contiguous pages. But here too, SQL Server is smarter about managing them. SQL Server estimates how many page locks it needs and can escalate a group of page locks to an extent or table lock if it determines that would be faster. You can configure this lock escalation (LE) in SQL Server using the LE maximum and LE percent settings to meet the needs dictated by your data access patterns. If you want more speed for large updates, set SQL Server to escalate to a table lock more quickly. If you have many users, set SQL Server to escalate to a table lock more slowly.

The SQL Server program is optimized to run on Windows NT. This means it can open multiple threads, or start several processes to run simultaneously. The first process, or thread, can start reading the disk. At the same time another thread creates a table to hold the data. By the time the first thread has finished reading the data, a third thread may have started reading ahead on the hard disk just in case you need that data, too. If 50 people are all connected to the server, you can have a hundred mostly independent threads reading and processing data. Since Windows NT supports multiple processors, many of these threads can be running at the same time. Since Windows NT supports striped disk sets, where four or five disks act as one, the server can be reading data simultaneously at an incredible rate. Since this is scalable to more disks and more processors, if your server runs slowly because of increased use, you can pop in another disk or another processor and quickly upgrade your performance back to its previous level.

Dynamic and Incremental Backup

You should always back up your database. With Microsoft Access, you copy the .mdb file to tape or another disk. If your .mdb file is 50 MB, you copy 50 MB each time you back up. With SQL Server, you can make incremental backups. During the first backup, you store the entire 50 MB. The next day you only need to back up the transaction log, maybe 1 MB. In case of a failure, you can still restore. Your total time spent backing up is less than it would be if you backed up the entire database every day.

The Microsoft Access .mdb file should be copied while no one is using it. SQL Server, on the other hand, can back up the database while it is being used. This SQL Server feature is especially valuable for servers running applications that must be in service 24 hours a day, 7 days a week. SQL Server can be configured to back up a database dynamically when a specified file size or time of day is reached.

Benefits of SQL Server

Multiuser locking, clustered indexes, and transaction logs are only a few of the benefits when using SQL Server databases.

Multiuser Locking

Microsoft Access offers only two levels of locking: locked or unlocked. It uses the operating system to lock portions of the data to allow two or more users to have simultaneous locks to different areas of the file. However, the Microsoft Access default is to open the .mdb file for exclusive use. In contrast, SQL Server expects many simultaneous users and has much more sophisticated locking strategies.

SQL Server offers these types of locks:

• Shared

  Read operations (SELECT) generally hold shared locks.

• Exclusive

  Write operations (INSERT, UPDATE, and DELETE) generally hold exclusive locks.

• Non-exclusive Update

  In some circumstances, a write operation can obtain a non-exclusive update lock. In this way, the write operation initially shares the datapage with read operations, then promotes its non-exclusive update lock to an exclusive lock when the write action is ready to begin.

• Intent

  An intent lock indicates the intention to acquire a shared or exclusive lock on a data page. An intent lock prevents another transaction from acquiring an exclusive lock on the table containing that page.

• Extent

  An extent lock is held on a group of eight database pages while they are allocated or freed. An INSERT statement that causes the server to allocate more data or index pages uses an extent lock. CREATE and DROP statements also generate extent locks.

• Insert Row-Level

  SQL Server 6.5 has the ability to lock individual records, so that two users can simultaneously insert different records on the same 2K datapage. When Insert Row-Level Locking is selected, the lock is initially established as a page lock and escalated to a row lock if another lock is requested. This helps to reduce overhead.

Usually locks are held on the 2K data pages. If a large number of data pages are affected, the server escalates the datapage lock to a table lock to save overhead and speed up the action. The point at which the lock escalates, called the Lock Escalation (LE) threshold, can be configured in the Server Configuration dialog box or by using the sp_configure stored procedure. The relevant settings are the LE threshold maximum, LE threshold minimum, and LE threshold percent settings.

As a system administrator, you can determine the locks that are being held by using the Current Activity window of SQL Enterprise Manager. This indicates the type of lock, the user holding the lock, the program holding the lock, and the last Transact-SQL statement received for that connection. It also shows if any server process ID (SPID) is blocking other SPIDs or being blocked by other SPIDs. Alternatively, you can view this information with the stored procedures sp_who and sp_lock.

Blocking problems usually arise when an uncompleted transaction holds locks. For information about blocking problems, see Knowledge Base article Q162361, "INF: Understanding and Resolving SQL Server Blocking Problems."

Clustered Indexes

With Microsoft Access, data is initially stored on the data pages in the order entered. As individual records grow and shrink (such as name changes from Rob to Robert) and as records are inserted and deleted, the records are moved around. After a moderate amount of activity, you can no longer predict where any given record is kept in the table.

Conversely, SQL Server has the ability to precisely control datapage placement. One and only one index on each table can be designated as the clustered index. The data will be stored in the table in this clustered index order.

If the clustered index in our table is the part number field, the records are physically stored in part number order. If the clustered index is the name field, the records are kept in order by name. Initially, you might think that the primary key, in this example, the part number, should be the clustered index. However, if you were to search for one specific part number, it wouldn't really matter whether the index was clustered or nonclustered index; you would still find the record that coincided with that specific part number. The best use for a clustered index is to assemble groups of data, like all orders for a particular company or an inventory report in alphabetical order by name.

The Upsizing Wizard

By default, the Upsizing Wizard transfers all indexes as nonclustered indexes. You can modify the Upsizing Wizard to transfer the primary key index to a clustered index. To make this change, start Microsoft Access and open the upsizing wizard library database. For Microsoft Access 95, the filename is Wzcs.mda. For Microsoft Access 97, the filename is Wzcs97.mda. When the database is open, click the Modules tab and open the UT_ModUserConstants module. Search down to the UT_CLUSTERED constant. Change the default value from False to True.

If this flag is set to true, and you're using SQL Server 6.0 or later, tables will be created with a clustered primary key. If you're using SQL Server 4.21, the index on the primary key will be created as a clustered index. The default behavior is to use a nonclustered primary key. Before you change this flag, you should review the section on locking implications of clustered indexes in the Microsoft SQL Server Database Developer's Companion (the default value is False).

Clustered Indexes and Locking

Another purpose of the clustered index is to distribute write activity efficiently. For example, you have 40 people registering new members for your pledge drive. They are recording the name and address of each new member and the table provides an automatically increasing member ID number. This is called a counter field in Microsoft Access 2.0, an AutoNumber field in Microsoft Access 95, and an identity field in SQL Server. Assume that 20 member records will fit on each datapage. Now you have a problem. To insert a new record, a user must get an exclusive lock on the datapage. Assuming the member number field is the clustered index, 20 people call at once, and 20 customer representatives simultaneously attempt to enter a record, there are requests for 20 exclusive locks. Even if the order entry application is written so that each record can be saved in a flash with the lock held for one second, blocking is still a problem. Even if this scenario were feasible, at the rate of one lock per second, the maximum speed of data entry would be no more than 60 records per minute. Insert row-level locking (IRL) in SQL Server 6.5 is intended to solve this problem.

To change our scenario, we move our clustered index to the last name field. Now when 10 new members are inserted, the member number still increases by one for each record. The records are physically written to the table alphabetically by last name. Record 2044 for Smith is written to the S data pages, and record 2045 for Jones is written to the J data pages. As these are not the same datapage, both INSERT functions simultaneously receive exclusive page locks. Blocking is only a problem when customer representatives simultaneously attempt to enter records for very similar names like Andersen and Anderson. As the number of records increases and is dispersed over an ever-increasing number of pages, the likelihood of blocking decreases.

Index Collisions

Although the presence of useful indexes speeds data retrieval, every index on your table slows down the data INSERT function slightly. Therefore, limit your indexes to fields and groups of fields frequently searched and fields required for table-to-table relationships. In the pledge drive example, it does little good to make the last name field the clustered index if a nonclustered index is placed on the member ID number field. In this example, the developer should create a temporary table for the membership drive, and then merge that data with the full membership table the next day. During the membership drive, the full membership table can be made available for read-only searching. Such use does not require exclusive locks.

Avoiding Heaps

A table that does not have a clustered index is called a heap. New data is always added to the end of the table. Empty space created when a record is deleted is never reused. In contrast, if you have a clustered index, new records are inserted among the data pages. If a record is deleted from page 230, a record added after the deletion reuses the available space. If you don't specify a clustered index, more new data pages are created, resulting in a large table and slow data entry. Only rarely can you achieve better data management with a heap than with a clustered index.

Fill Factor

Microsoft Access fits as many records as possible on each datapage. However, SQL Server has an index feature called the fill factor. The fill factor, which can be set for both clustered and nonclustered indexes, is a percentage that tells SQL Server to leave some empty room on each datapage for new records. A fill factor of 50 percent causes SQL Server to write 10 of 20 records to the first datapage, then start a new datapage. If you designated a 100 percent fill factor, SQL Server writes all 20 records to one page. The default fill factor of 0 percent fills the datapage less 2 records, or 18 records in this example. When you want to add a new record, SQL Server divides the page in two and adds your record to one of the resulting data pages. The splitting algorithm is the same, regardless of the original fill factor. The fill factor is applied once when the index is first created. An initial 100 percent fill factor decreases as data pages are split in two. A fill factor originally set at 20 percent gradually increases as the pages fill. If the original fill factor is 50 percent, the value increases as the pages fill up, then decreases as they start dividing by two, until they once again reach 50 percent. Eventually, no pages are less than 50 percent full and some pages are 100 percent full, so the application averages 75 percent full. However, areas of your table that have little change retain their original fill factor, whereas active areas of the same table will drift toward 75 percent. Tables that have many deletions conversely migrate toward low fill factors.

A low fill factor reduces the need for page splits, significantly increasing the speed of data inserts. But there is a major drawback to this technique. If you only fill pages halfway, your table and your database will be twice as large as a table and database with a higher fill factor. When you search the resulting table for data, SQL Server must read more pages than would be required by a higher fill factor, since fewer records are stored on each page. Your search will be slower than it would be if the fill factor were higher. Low fill factors can be great for tables that experience frequent data insertion and can help reduce page locking conflict by storing fewer records per page. However, low fill factors are poor for tables that experience frequent data reads. Therefore, before you set the fill factor, ask yourself how the data on the table will be used.

Smaller Indexes Are Faster

The body of a nonclustered index contains records like any other table. Each record contains an index value and a pointer to the corresponding original table record. If your index is a multi-field index covering many large character (char) or variable character (varchar) fields, each index record will be quite large, and only a few will fit on one datapage. More 2K index pages must be read from the hard disk in order to search for values in such an index, slowing performance. Conversely, if the index is a single integer field, many records can be stored on each index page, resulting in a faster index.

A Covering Index

If you frequently need data from just a few fields of a table, create an index containing those fields. This query fills a list box with names and IDs of preferred customers.

SELECT CustName, CustID
FROM Customers
WHERE Status = 'Preferred'
ORDER BY CustName

To improve performance, create an index on the Status field. The server then reads the index to identify the preferred customers and uses the record pointers in the index to find only those records in the customers table. Though faster than reading the entire table, this method requires reading both the index and underlying table data pages, generating many disk reads.

You can speed up this query by creating a multi-field index over the Status/CustName/CustID fields. When the server searches the index for the preferred customers, it simultaneously obtains the CustName and CustID information. (The Status column should be the first field listed in the index.)

In summary, smaller indexes are faster than larger indexes, but if you can get all of the required data from the index, that is faster still. Carefully match your indexes to your data needs since each index slows INSERT, UPDATE, and DELETE operations.

Compacting the Database

The Microsoft Access utility for compacting databases does not exist for SQL Server. Instead, SQL Server rebuilds indexes. As records are deleted from a table in SQL Server, gaps in the table and its indexes remain untouched. When an entire datapage is emptied, it is reassigned. However, as long as one record remains, the datapage remains untouched, even if it is 90 percent empty. When you rebuild an index, these data pages are rewritten and filled as specified by the fill factor. (If no clustered index exists on the table, create one and then drop it to achieve the same effect.) To fully compact a database, rebuild every index on every table. Of course, tables that experience little activity will benefit only slightly. Though it initially requires some work to set up such a maintenance plan, this system is easy to customize so you can rebuild only selected tables.

The Transaction Log

SQL Server has an important feature common to most production scale databases that is entirely absent in Microsoft Access and other "desktop" databases. The transaction log is a chronological record of all activity that occurs on the server. When a record is added to a table, a copy of the record is written to the transaction log. When a record is deleted, the transaction log records the data that was removed. Every time an existing record is changed, the transaction log records both the old value and the new value. The same details concerning the creation and deletion of tables, views, stored procedures, rules, defaults, and so on are stored also. Every multi-step transaction is recorded, along with whether it was committed or rolled back. Every entry to the transaction log contains a timestamp, an incrementing number to order all entries.

Transaction logs have many benefits. First, you can make a full backup of the database (which can take many hours on a large installation) and then, at later intervals, you need to back up the transaction log. By backing up transaction logs in lieu of backing up the complete database, you save time that can be used to make more frequent backups. By using a transaction log, you can fine-tune your backup system for a wide variety of data integrity requirements. The transaction log is often kept on a separate disk drive or disk array. If the data resides on a disk that fails, you can restore the database from a dump and then apply the transaction log still intact on another disk.

Truncate Log on Checkpoint

Should you decide that you don't need a transaction log, you can set the trunc. log on chkpt. option. With trunc. log on chkpt., you can ignore maintenance of the transaction log, and your database behaves much like a Microsoft Access database. SQL Server is still using the transaction log and you are still gaining some of its benefits, but less space and no maintenance are required.

The checkpoint process, verifying that all data changes are "flushed" to the hard disk and thus saved, occurs regularly on every database. The trunc. log on chkpt. option can be set ON or OFF for each database without regard to the settings of the other databases on the server. With this option set ON, the contents of the transaction log are discarded every time the checkpoint process completes. Checkpoints occur whenever a transaction commits, when a specified number of data pages have been changed, or at least every few minutes. The trunc. log on chkpt. is set on for the pubs sample database, the master database, and the tempdb temporary database.

Transaction Completion and Rollback

With Microsoft Access, if you experience a power failure, you may be left with an incomplete transaction upon restart. SQL Server's transaction log prevents this. Upon startup after a power failure, the transaction log rolls the database activity forward to a stable condition. Transactions, if completed in the log, are applied to the data pages. Incomplete transactions are rolled back. This is an advantage even if you don't use transactions in your code. Normal database activity produces transactions, such as page splits, that are required when a datapage is full. Such transactions are completed or rolled back, leaving your database in an internally consistent state. This occurs regardless of the trunc. log on chkpt. setting.

Database Replication

Database replication is also based on the transaction log. If you have marked a table for replication, it is recorded in the database transaction log. A special process called the log reader monitors the log and sends changes marked for replication to the distribution database.

Introduction to SQL Server

This section is intended for the experienced Microsoft Access user who has never explored the features of Microsoft SQL Server. If you are familiar with SQL Server and SQL Enterprise Manager, skip this section. For more information about any of the features discussed here, see SQL Server version 6.5 Books Online.

SQL Server Setup

The SQL Server Setup program performs the following actions for you:

The Setup program copies the SQL Server files to the disk. The default directory is C:\Mssql. There will be a variety of subdirectories, the most important ones being:

• C:\Mssql\Binn holds the main executable file sqlservr.exe and other important executables and files.
• C:\Mssql\Data stores the data files with the extension .dat. These Windows NT files are called "devices" in SQL Server. When SQL Server is running, it creates an exclusive lock on these files. You cannot copy these files while SQL Server is running and Windows NT backup cannot back up the files in this directory. SQL Server's backup program can copy this data.
• C:\Mssql\Log contains the error logs as files named Errorlog, Errorlog.1, Errorlog.2, and so on, up to and including Errorlog.6. The most current error log is always Errorlog. When the server stops and starts again, it deletes Errorlog.6, renames Errorlog.5 as Errorlog.6, renames Errorlog.4 as Errorlog.5, and so on, ultimately renaming Errorlog as Errorlog.1. Then, it creates the current error log as Errorlog. The error logs are opened from SQL Enterprise Manager but can also be opened with Notepad or Wordpad.
• The Setup program completes the required registry entries. There are more than 50 of these but the most interesting entries are in:

  \HKEY_LOCAL_MACHINE
  \SOFTWARE
  \Microsoft
  \MSSQLServer
  \MSSQLServer
  \Parameters
  SQLArg0 = -dC:\MSSQL\DATA\MASTER.DAT

  This is the path to the Master.dat file. This must be accurate or SQL Server will not start.

  SQLArg1 = -eC\MSSQL\LOG\ERRORLOG

  This is the path to the error log.

  \Setup
  SetupStatus = Installed

  If the Setup program did not complete properly, the SetupStatus will say Upgrade.

• The Setup program starts the server and runs the setup scripts in the Install subdirectory consistent with the options you have selected.
• The Setup program creates your program group icons and Start menu entries.

Windows NT Services

SQL Server creates and uses two Windows NT Services. To see these, use the Services application in Windows NT Control Panel. The MSSQLServer Service is the basic server. The SQLExecutive Service is the scheduling manager for SQL Server. Each of these services can be started with the local System Account or with a Windows NT domain account. The System Account is usually sufficient unless your network has several servers that need to talk to one another. Replication from one computer to another computer requires using a domain account that is recognized by both servers.

Security

If you are installing SQL Server for the first time, select mixed security to give you the maximum connection options.

Network Protocols

The Setup program selects Named Pipes by default. If you are new to SQL Server, start with at least Named Pipes and perhaps TCP\IP. The server can easily listen on several protocols and the resource cost of doing so is minimal. When you understand the network issues, you can run Setup again and remove the unneeded protocols. If you plan to use Windows NT integrated security, you must select either Named Pipes or Multi-Protocol.

SQL Enterprise Manager

SQL Enterprise Manager is the workhorse of SQL Server. You use it to manipulate devices, databases, tables, and indexes. SQL Server does not have a user interface. SQL Enterprise Manager is a client, as is Microsoft Access. True, it is constructed to give you tools specifically for dealing with SQL Server, but it connects as a client and has no special status. SQL Enterprise Manager can connect to several SQL Servers at the same time. The first time you start SQL Enterprise Manager you are asked to register a server. Provide the Windows NT computer name in the server box, click Use Standard Security, and provide "sa" as the Login ID with no password.

Exploring Server Manager

Once the server is registered, you can start exploring the Server Manager. Click the plus signs (+) to expand lists of objects, drilling down to greater detail. A normal installation creates five databases:

• master contains the system tables for the server.
• model is a database that is copied to create new databases.
• msdb is a database used by SQL Executive to store information about automatic task scheduling.
• pubs is a sample database.
• tempdb is a database used to hold temporary tables. It is recreated by copying the model database each time you start SQL Server.

SQL Query Tool

This tool enables you to send SQL statements to the server. Select a server or database in the Server Manager window. On the Tools menu, click SQL Query Tool. (SQL Enterprise Manager does not have a tool like the Microsoft Access query by example grid.) In the Query dialog box, click the DB list to expand it and select the pubs database. Enter a query:

SELECT * FROM titles

To run the query, click the green arrow at the upper right of the dialog box. You can execute the query by typing CTRL+E or by selecting Execute from the Query menu of SQL Enterprise Manager.

ISQL/w

This program is similar to the SQL Query Tool in SQL Enterprise Manager. You can more directly control the login for troubleshooting connection problems. It also excels at passing error messages to the users. This makes troubleshooting easy to perform with the simple ISQL\w client program.

Client Configuration Utility

When you install SQL Server, you select one or several network protocols. Before you connect from SQL Enterprise Manager or ISQL/w program, you can change the protocol you use with the Client Configuration Utility. To do so, on the Start menu, select Programs, click Microsoft SQL Server 6.5, then click SQL Client Configuration Utility. Next, click the Net Library tab. Select the desired protocol from the Default Network box and click Done.

Before You Migrate Your Application

You should review the following items within the Microsoft Access application and SQL Server installation prior to migrating your application.

Back up, Back up, Back up

Current backups of your data are always important, but they are essential when you make major changes to your database. Though the upsizing process does not delete any data, you can expect to delete the local Microsoft Access tables in the .mdb file after the process is complete and has been tested. Also, you may need to consult a working copy of your original Microsoft Access application in the future.

Using Windows Explorer or File Manager, make a backup copy of the .mdb file containing your database, giving your file an extension of .bak. Thus Northwind.mdb becomes Northwind.bak. You needn't copy the file with the .ldb extension if one is already present. If you created user and group IDs for your application, make a backup of the system database as well. (By default, the filename is System.mdw in Microsoft Access 95 and System.mda in Microsoft Access 2.0.) Do not rename or move the original system file.

Evaluate Object Names

Review the names of your tables and columns. Objects in Microsoft Access can contain spaces, for example, you can name a table "January Orders." Microsoft SQL Server does not permit spaces in table or column names. The Upsizing Wizard changes the spaces to underscores, transferring your table to SQL Server as "January_Orders."

Microsoft Access tables ending in "_local" cannot be upsized to SQL Server. The Upsizing Wizard renames the original Microsoft Access tables, adding "_local" to the original table names. For example, a Microsoft Access table originally named "customers" is renamed "customers_local" by the Upsizing Wizard. This permits the new SQL Server table to be linked to the Microsoft Access database with the original name of "customers," while leaving the original table untouched with the new name of "customers_local."

Macros and code modules treat tables differently than queries. Open them in design view and redirect them to the table name as it has been renamed by SQL Server. (Alternatively, attach to a table named "January_Orders" and alias the attachment to "January Orders." However, this can confuse people who see the attached "January Orders" table, but see the different name "January_Orders" on the server.)

A common way to avoid the underscores is to use capitalization to separate the portions of the names, for example "JanuaryOrders". If you have used this naming convention in your Microsoft Access tables, you needn't make any changes when moving your data to SQL Server.

SQL Server limits table names to 30 characters. No symbols, except #, $, and _, are allowed in table names. The first character of a table name cannot be a number. Do not use the number sign (#) as the first character of an upsized table name because it indicates a temporary table.

Though the default installation is case-insensitive, SQL Server can be installed with case sensitivity so that a table called MyTable is not the same as a table called mytable or MYTABLE. These three object names can coexist if the server was installed with a "Case Sensitive" sort order. Determine the sort order on your server by running the stored procedure sp_helpsort. If you plan to change any table names, do it before the Upsizing Wizard starts copying your tables.

Evaluate Cascading Updates and Deletes

SQL Server offers two methods of maintaining referential integrity:

• Triggers.

  A trigger is Transact-SQL code that runs when set off by an action such as an INSERT, UPDATE, or DELETE to a table. Triggers are flexible and can be written to do almost anything. Triggers are your only choice for cascading updates and deletes discussed below. The Upsizing Wizard writes them for you if you choose. For more information about cascading deletes and updates, see Knowledge Base Article Q142480, "INF: Cascading Deletes and Updates of Primary Keys."

• Declarative Referential Integrity (DRI) is similar to Microsoft Access and was added in SQL Server 6.0.

  SQL Server's DRI will enforce referential integrity. (For example, an order cannot be created for customer XYZ unless there is a corresponding customer XYZ in the orders table.) However, DRI does not offer the cascade update feature. (You cannot change the customer name to ABC and expect all existing orders to be changed automatically.) Similarly, DRI does not support cascading deletes. In Microsoft Access, if you delete the customer XYZ, Microsoft Access prompts you to delete all of XYZ's orders from the orders table. With SQL Server DRI, you have to do this in two steps: first delete the orders for the customer, then delete the customer.

Set the cascade updates and deletes feature in Microsoft Access from the Relationships window. Double-click a relationship line to see the option. The Upsizing Wizard detects the cascading option and suggests triggers to support your referential integrity. If that option is not selected on your joins, the Upsizing Wizard uses SQL Server DRI to support referential integrity.

One drawback to using triggers is slower performance in the case of inappropriate data. If you use DRI, the primary key constraints fire before data is entered into the table. Inappropriate data is thus rejected quickly. Triggers, on the other hand, permit the data to be entered into the table, then the trigger fires to validate the entry. If the relationship is not valid, the change to the table is then rolled back.

The Upsizing Wizard uses triggers to prevent entry of data into fields that were marked as "Required" in Microsoft Access. A trigger can easily be changed, retaining the flexibility that Microsoft Access exposes for a table. SQL Server developers generally define a required field as NOT NULL when creating the table. The Upsizing Wizard can be configured to do that for you.

For more information about configuring the Upsizing Wizard to specify NULL or NOT NULL, see "Server Error 191: SQL Statement Nested Too Deeply," in the "Common Error Messages" section of this paper.

Now you are ready to upsize your database.

Prepare the Server and Create a Database

SQL Server should be installed and running on the server computer.

A Microsoft Access database is held in the .mdb file on the computer's hard drive. SQL Server data is also kept in a file on the hard drive. It uses the .dat file extension (for example, Data.dat). SQL Server refers to the hard drive file as a "device." A database can be placed on one device or spread over several devices. Several databases can also be placed on one device. The Upsizing Wizard prompts you to select a device or create a new device to hold your data. Generally, it is best to place your database on a new device created specifically for it. This reduces your administrative complexity. You will also be prompted for the location of the transaction log. If you want to postpone the decision, put the transaction log on a separate device for now.

The size of your database will not change greatly when it is transferred to SQL Server. It could be somewhat smaller than your Microsoft Access .mdb file because you are moving the tables only, not forms, reports, or Visual Basic® for Application modules.

Connecting to SQL Server

The first migration steps are to install SQL Server and establish a connection from Microsoft Access. Consult SQL Server Books Online for information about installing the server. You must create a data source on the client computer, the computer running Microsoft Access, to connect to the server. If Microsoft Access and SQL Server are on the same computer, the data source is still required.

What Is a Data Source?

A data source is a method of standardizing database connectivity. Data sources are created with the Open Database Connectivity (ODBC) dialog box accessible from Windows Control Panel. Windows NT and Windows 95 operating systems have a 32-bit version of the ODBC application, while Windows 3.1 and Windows for Workgroups have a 16-bit version. Microsoft Access 95 and 97 can create a new data source from within the application.

A data source (abbreviated DSN for data source name) can be created as a system data source or a user data source. Should you create a user DSN or a system DSN? A system DSN tends to be more convenient but less secure than a user DSN. Select a user DSN if you have multiple users of the same client computer and you want to hide the existence of the data source from some of the users.

Creating a System Data Source

To create a system data source

1. On the Start menu, click Settings, then click Control Panel. (In earlier versions of Windows, select Control Panel from the Main program group.)
2. Double-click ODBC to open the driver manager's user interface, the ODBC Data Source Administrator. What you see varies with the version of the Odbc32.dll (or Odbc.dll) that you are using. ODBC 3.0 presents the ODBC Data Source Administrator dialog box.

   ODBC 2.5 presents a dialog box with much of the same information, but without tabs and without the option of creating a System DSN. If you are presented with that dialog box you may want to switch to the ODBC files on the SQL Server CD (Odbc32.dll and Sqlsrv32.dll for 32-bit operating systems or Odbc.dll, Sqlsrvr.dll for 16-bit operating systems.)

3. Click the System DSN tab.
4. Click Add.
5. From the installed ODBC drivers list, select SQL Server and click OK to bring up the ODBC SQL Server Setup dialog box.
6. Give your data source a name, such as Payroll. This can be either the name of the server to which you are connecting, or the name of the database to which you are connecting or some other name that is meaningful to you, such as the purpose of the connection. You are likely to accumulate several data sources, so the name should be descriptive.
7. The Description field is optional.
8. Enter the name of the SQL Server in the Server box. The SQL Server should have the same name as the Windows NT-based computer on which it is running.

   If Windows NT was reinstalled with a new computer name after SQL Server was installed, they can be different and then you may experience connection problems. See sp_rename in SQL Server Books Online if you suspect this.

9. Click the Options button and provide the name of the database on SQL Server to which you want to connect. Remember that several databases with different names can exist on one SQL Server.
10. At the bottom of this dialog box is a check box called Convert OEM to ANSI Characters. IMPORTANT: THIS BOX SHOULD NOT BE CHECKED.
11. Click OK to return to the ODBC Data Source Administrator dialog box, and click OK again.

DSN-less Connections

Users can easily make their connections with data sources. Moderately skilled users can create and maintain their own DSNs. However, there is also room for user error. Many programmers decide to make programmatic DSN-less connections. This can be more reliable because it does not require maintenance on the client computer, but it cannot be updated without rolling out a new version of the client program.

To make a connection without a DSN

1. Create a new SQL Pass-through query in Microsoft Access.
2. On the View menu, click Properties.
3. In the ODBC Connect Str. field of the Query Properties box, enter a connection string like the one below. The italic text must be replaced with your information. Enter the string on one line:

   ODBC;driver=;server=YourServerName;database=
   YourDatabaseName;uid=UserName;pwd=Password;dsn=;

   This example uses a server named Acctg1, a database named Purchases, and the sa account with no password:

   ODBC;driver=;server=Acctg1;database=
   Purchases;uid=sa;pwd=;dsn=;

For more information about making DSN-Less connections with VBA code, see the Visual Basic Knowledge Base Article Q147875, "How To Use 'DSN-Less' ODBC Connections with RDO and DAO."

Using the Upsizing Wizard

It's time to move your Microsoft Access database to SQL Server using the Upsizing Wizard. This section assumes you are moving data to Microsoft SQL Server 6.0 or 6.5 using Microsoft Access 95 with the Upsizing Wizard, although you can also use Microsoft Access 97 with the Upsizing Wizard.

Load the Upsizing Wizard into Microsoft Access by double-clicking the Upsizing Wizard executable Upsize95.exe, or Wzcs.exe if you are using Microsoft Access 95, or Wzcs97.exe if you are using Microsoft Access 97. This will place the wizard database Wzcs.mda in your Microsoft Access directory and make the necessary changes to the Windows registry.

Start Microsoft Access and load your database.mdb file. On the File menu, click Add-ins, then Upsizing Wizard. Microsoft Access loads the Upsizing Wizard into memory and displays the Upsizing Wizard dialog box.

You must decide whether to create a new database for your tables or use an existing database. If you are moving a small sample database, select Use existing database. If you are moving your data for a production database, select Create new database, unless you have prepared a database on the SQL Server to receive your data.

The Select Data Source dialog box prompts you for the data source of the server.

The SQL Server Login dialog box prompts you for the server's Login ID and Password. When first installed, Microsoft SQL Server has a system administrator (sa) account with no password. If your SQL Server administrator has established a login ID for you, use your network login and password. If the server uses integrated security (where you make a trusted connection) and if your login or domain group has privileges on the server, you need no login ID and no password.

Click Options to see if a database has been provided as part of the data source. If you know the name of an existing database on the server, you can provide it or select one from the list.

Click OK. If you receive any errors, see SQL Server Books Online.

This dialog box prompts you for tables to be exported to SQL Server. Select some or all of them. Include all the relevant tables in your referential integrity relationships. If in doubt, and if the table is not huge, move it to the server for now. For more information about which tables make sense to move to the server, see "Changes to Your Application," later in this paper.

What Table Attributes Do You Want to Upsize?

You can upsize indexes, validation rules, defaults, and table relationships.

Indexes

You will probably want to modify your indexes once the tables are on the server. As a minimum, designate a clustered index. Though indexes are easy to rebuild or drop and recreate, that can be time consuming on large tables. As a general rule, upsize the indexes of tables less than 10 or 20 MB. If your tables exceed 100 MB, skip the indexes until you are sure what you want. Building indexes does take time and selecting this check box slows down the data transfer.

You can also check the box to accept the indexes you have been using in Microsoft Access. If this is not a large table, go ahead and do that for now. Microsoft Access creates all indexes as nonclustered indexes.

Validation Rules

Validation rules are checked in almost all cases. If you have a validation rule (a rule in SQL Server) that says the Qty field must be greater than 5 or the DeliveryDate field must be later than the OrderDate field you should keep it. The Upsizing Wizard uses triggers rather than SQL Server rules to enforce field-level validation because SQL Server rules do not allow you to display custom error messages. When the Upsizing Wizard creates the trigger, it provides an appropriate custom error message explaining why an invalid INSERT was rejected.

Defaults

You should leave this checked in almost all cases, too. A default value of Net30 for payment terms or Date() for the OrderDate works the same in SQL Server as it does in Microsoft Access. The Upsizing Wizard provides the correct Transact-SQL syntax for common functions such as Date().

Table Relationships

This one can be tough. "Use DRI" will create the SQL Server tables with Declarative Referential Integrity, the designations that enforce foreign key to primary key relationships. SQL Server 6.0 and 6.5 do not enforce cascade updates and deletes, so if you have that selected in your relationships you will get a dialog box reminding you that you are foregoing that feature. If you select Use triggers, the Upsizing Wizard will write triggers for your tables to enforce cascading updates and deletes. This may be a valid reason to make more than one pass through the Upsizing Wizard selecting triggers to move the tables that have cascading updates or deletes and selecting DRI to move the tables that do not. All the tables involved in the relationships must be present or must have been created on the server with the DRI option, so move the triggered tables before the DRI tables.

You can forego the relationships entirely by unchecking the Table relationships box. This is a good choice for fast data transfer if you intend to revisit the data and table structure once loaded on the server.

What Data Options Do You Want to Include?

You can add timestamp fields to tables and create a table structure without upsizing data.

Add timestamp Fields to Tables?

Selecting Yes, let wizard decide is recommended for now. Only select No, never or Yes, always if you understand the impact of data conversion and timestamps. A timestamp field is an easy way for Microsoft Access to tell if the data in a record has changed since its creation. This field is not visible to Microsoft Access users but can speed data entry and avoid the #Deleted error described in the Common Error Messages section of this document.

If your table contains text or image fields, you should have a timestamp field. Because such fields are often large, Microsoft Access does not automatically check to see if the value of the field has changed before updating a record. If there is no timestamp field in the table, Microsoft Access may overwrite changes to these fields.

Only create table structure, don't upsize any data

Only create table structure, don't upsize any data is your choice if you have set up the data structure but want to begin anew when the tables are moved into production on the server. This helps transfer tables that are large due to high quantities of data and insufficient database locks. Use this option to transfer the table structure, then move the data in smaller chunks using APPEND queries. For more information about this technique, see "Other Ways of Moving Data," later in this paper.

How Should Your MS Access Database Be Modified?

You can attach newly created SQL Server tables, save a password and user ID with linked tables, and create an upsizing report.

Attach newly created SQL Server tables

If you plan to use your Microsoft Access database as a front-end program to manipulate the SQL Server data, attach to the tables. If you are abandoning your application because you plan to use ODBC from a Visual Basic-based application to connect to SQL Server, do not select this option.

Save password and user ID with linked tables

Do not use this option if you are using a trusted connection and integrated security. If you are using standard security, this applies to you. If you do not check this box, a user connecting with the Microsoft Access database is asked for login ID and password whenever they open the table, or a query or form based on the linked table. This can be an important security feature. If you check this box, the login ID and password is cached with the connection and users of the database have rights to the SQL Server database consistent with their login ID and password. If in doubt, avoid an inadvertent hole in your security by not checking this box.

Create upsizing report

The Create upsizing report option is strongly recommended. The Upsizing Wizard intercepts many server error messages so that it can continue transferring tables without interruption. It saves them for the report. Consequently, you will not see all of the errors unless you see the report.

You must have a default printer driver loaded for Microsoft Access. For information about the most common errors, see "Common Messages," later in this paper.

One of the first status messages you will see concerns the character translation table. The Upsizing Wizard uses this table to transfer extended characters: ANSI characters 128 through 255. This table is deleted when the wizard finishes. If you have an unusual program termination and notice that this table has been left on the server, delete it.

The Upsizing Wizard creates the tables on the SQL Server and copies the data into the new tables. It then renames the tables in the Microsoft Access database, appending "_local" to each table name. If spaces exist in the table names and it changes the spaces to underscores, it redirects all queries from the original name to the underscored name. Then it links to the new tables on the server.

Other Ways of Moving Data

Though the Upsizing Wizard works for most circumstances, there are times when you will want to move your data in some other way. Sometimes the Upsizing Wizard does not move a table, and sometimes the amount of data is too large for the configured server locks to accommodate. Perhaps you upsized once already and now need to add data from another copy of your database to the tables already on the server.

Exporting Tables

To export tables and queries directly to the server

1. On the File menu, click Save as Export.
2. Click To an external File or Database.
3. In the Save as type box, click ODBC Databases().
4. Type in a table name, and click OK.
5. Select a Data Source, and click OK.
6. Complete your login information, and click OK.

When you create a table on the server this way, the correct data type and data is transferred, but you will not get the other features that the Upsizing Wizard provides. Microsoft Access AutoNumber fields are converted to integer fields but not identity fields. No indexes are created. No table relationships exist. No triggers for enforcing cascading updates and deletes are created. You will need to attend to these matters on the server. Of course, this may be desirable if you plan to optimize the structure, changing the database schema as you migrate to a server environment.

To link (attach) to a table you have exported

1. On the File menu, click Get External Data, then click Link Tables.
2. Select ODBC Databases().
3. Select your SQL Server Data source.
4. Complete the SQL Server Login dialog box.
5. Select the table(s) in the Link Tables dialog box, and click Save password if desired, then click OK.
6. If your table does not have an index, you will be prompted for the unique field.
7. The table is added as dbo_tablename. The dbo stands for database owner. You can rename the table in Microsoft Access to the original table name to avoid confusion. (All tables in SQL Server have owners and the fully qualified name of a table on SQL Server is databasename.owner.tablename, for example, pubs.dbo.authors.)

Creating Append Queries

If the table already exists on the server, you can move data to it from Microsoft Access. In Microsoft Access, link/attach to the table. Then, create a new query by selecting data from the Microsoft Access table. With the query open in design view, select Append from the Query menu. When you are prompted for the target table, select the name of the linked SQL Server table. This creates an append query. Run the query to write the designated records to the SQL Server table. This can be a useful method for moving only portions of tables, for example, if you are combining data from several databases or from several tables. If the data size is too large to transfer all at once, use criteria (like OrderID <1000, then OrderID between 1000 and 2000, and so on) to move the data over in pieces.

The Bulk Copy Program

SQL Server offers a command-line program called the Bulk Copy Program (bcp) for moving data into a SQL Server table. The SQL Server Transfer Manager feature uses bcp. To use bcp, create your table in SQL Server and move your Microsoft Access data to a text file. If you are moving data over a slow link wide area network, bcp is a good method for moving only data. If you are distributing data on floppy diskettes for a manual transfer to the server, bcp is also a good choice.

To use bcp to transfer Microsoft Access data to a SQL Server database, you must first create a text file with the data. On the File menu in Microsoft Access, click Save As/Export and select Save table name: To an external File or Database. In the Save Table screen, select Save as type Text Files and click Export. Complete the rest of the Text Export Wizard, selecting your choice of Delimited or Fixed Width file. Now you have the text file containing your data. For information about bringing the data into SQL Server, see the Microsoft SQL Server Administrator's Companion.

There are two types of bcp, fast and slow. Fast bcp occurs automatically if no indexes exist on the table and if the Select Into/Bulk Copy option is set to true for the database. Fast bcp does not log the data inserts, so you must dump the entire database following the bcp action for a reliable backup.

On the server, open a command prompt window. Move to the BINN subdirectory of the SQL Server directory. Enter a command like this:

bcp dbname..tbl in text.txt /fmy.fmt /Sservername /Usa /Ppw

Moving AutoNumber Fields

The Upsizing Wizard knows all about AutoNumber/counter/identity fields, but if you are moving the data yourself you must deal with this issue. You can define the field on the server's target table as an integer (int) data type and identity field. Use an append query to select all the fields except the AutoNumber field. SQL Server provides new identity numbers for the records as they go into the table; these numbers are different from the original Microsoft Access record numbers. To preserve the Microsoft Access AutoNumber values, mark the identity field as temporarily inactive so it will accept your numbers instead of supplying new ones.

1. Create but do not execute an append query to move the records from the Microsoft Access table to the linked SQL Server table.
2. Create and execute a SQL Pass-Through Query with the SQL Statement:

   SET IDENTITY_INSERT newtablename ON

3. Execute the append query. Execute a SQL Pass-Through query:

   SET IDENTITY_INSERT newtablename OFF

This plan usually works. There is one possible problem. The SET IDENTITY_INSERT statement is valid only for subsequent statements on the same connection, so it only works if Microsoft Access uses the same connection for the pass-through query as for the append query. For more information on the use of IDENTITY_INSERT, see Knowledge Base article Q152035 INF, "Appending Data from Access Table to SQL Table."

(If you use Visual Basic for Application code to open a recordset containing an identity field, you must use the dbSeeChanges option.)

Changes to Your Application

Changes should be made to the application that make better use of SQL Server's features.

Stop Browsing Tables

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.

Snapshots vs. Dynasets

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:

• You do not need to update the server tables.
• The table contains relatively few columns.
• The table does not contain SQL Server text (Access memo) fields or SQL Server image (Access OLE) fields.

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.

Batching Inserts

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.

Set Operations

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.

Storing Data Locally

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.

The Need for Concurrency

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.

Resolving Queries on the Server

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.

Moving Business Rules

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.

Replacing the Seek Command

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.

Using Transactions

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.

Outer Join Syntax

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.

Field-level Validation Rules

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.

Default and Autonumber Fields

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.

Changes to Your Data

Changes must be made to certain types of data in a Microsoft Access database to take advantage of SQL Server features.

Float vs. Double Data Type

Two of the most common errors that occur with linked SQL Server tables after modifying a record are "#Deleted" and the related "Data has Changed. Operation stopped." These errors are most often caused by the presence of a float data type column in SQL Server, by a datetime data type on the server, or by a trigger that modifies a value after it is inserted. For more information about these errors, see "Common Error Messages," later in this paper.

Before you can update a table, the table must have a unique index so that Microsoft Access can identify records. When making an update, Microsoft Access prepares an update query, for example:

Update mytable set(CustName = 'XYZ') where CustID = 12345

Before Microsoft Access sends this UPDATE statement, it must consider a possible problem. If someone else edited this record after Microsoft Access first read the message, this update could silently overwrite someone else's changes. To avoid that, Microsoft Access reads the message record again to ensure that it has not changed. If the unique index or the column being changed is a float data type, this process may generate a discrepancy.

Microsoft Access does not have a float data type. When linked to a SQL Server database with a float field, Microsoft Access converts the values to a Visual Basic double data type. The double and float data types are similar and this is usually a reasonable match. However, these are not exact data types, they are approximate data types. For example, the value of one-third (1/3) cannot be represented as an exact decimal or binary value. To resolve this, the float and double data types use an approximation not unlike scientific notation. However, the C language float and the Visual Basic language double do not use the same formula and calculations may be imprecise. If Microsoft Access can't find the specific value in a SQL Server table because of an imprecise calculation, it will come to one of two conclusions:

• If the float data type was the table's unique index, Microsoft Access assumes the record was deleted because it can't locate the record.
• If the unique index remains unchanged but the value of the float field is different, Microsoft Access assumes that someone else changed the record and presents the data changed error.

A similar problem occurs if a SQL Server table has a trigger that modifies the value of a column. For example, if Microsoft Access inserts a record with order number 1234 into a table with a trigger that adds the year to the order number (971234), Microsoft Access returns the deleted error as it no longer can find the record it inserted.

Work around these problems by avoiding float data types as unique indexes identifying the rows. Also include a timestamp field in the table when you use float or datetime fields. When a timestamp field is present, Microsoft Access checks the timestamp only, not all the values in the record, as that would have changed if any column had been updated.

Identity Data Type

SQL Server has an automatically incrementing field, called an Identity field, similar to the Microsoft Access AutoNumber field. The Upsizing Wizard creates a table on the server with an identity field, turns off the functionality of the field with the IDENTITY_INSERT option, copies the existing AutoNumber values into the table, and turns the IDENTITY_INSERT option back on. This preserves the original AutoNumber values from your Microsoft Access table and begins future records with the highest number in your table, incrementing by one. For more information on the use of IDENTITY_INSERT, see Knowledge Base article Q152035 INF, "Appending Data From Access Table to SQL Table."

Changing True from -1 to +1

Microsoft Access stores either a zero (0) or a minus one (-1) in the Yes/No data type. Microsoft Access interprets 0 as 0, No, or False. Microsoft Access interprets any non-zero value as –1, Yes, or True.

A bit field stored in SQL Server that is selected in ISQL/w appears as either a zero (0) or a plus one (1). Think of the result as True (1) or False (0). When you link to this table with Microsoft Access, you can accurately check for a value of 0 but not 1. The correct method in Microsoft Access is to test for a value of True or False.

Data Type Conversions

The most common data type changes when upgrading from Microsoft Access to SQL Server are:

• Microsoft Access Memo fields become SQL Server text fields.
• Microsoft Access OLE fields become SQL Server image fields.
• Microsoft Access text fields become SQL Server varchar fields. This is the default, but the Upsizing Wizard can be modified to convert Microsoft Access text fields to char fields instead. To make this change, start Microsoft Access and open the Upsizing Wizard library database. For Microsoft Access 95, the filename is Wzcs.mda. For Microsoft Access 97, the filename is Wzcs97.mda. When the database is open, select the Modules tab and open the UT_ModUserConstants module. Search for the UT_USE_CHAR constant and change the default value from False to True.

For more information about data type conversions, see the "Table of Data Type Conversions" in the Appendix.

Top N Queries

SQL Server does not have the Microsoft Access syntax for a Top N or Top N Percent query. You can provide this functionality in SQL Server by ordering the output and returning the specified number of rows. The SET statement precedes the SELECT statement.

SET rowcount 5
SELECT qty, ord_date
FROM sales
ORDER BY qty DESC

To calculate a percentage of the rows returned, declare a variable. Count the records and compute the number of records that you want. Select the correct number of records. This query returns the top third of the records.

DECLARE @desiredrows int
SELECT @ desiredrows = COUNT(*)/3 FROM sales
SET ROWCOUNT @ desiredrows
SELECT qty, ord_date
FROM sales
ORDER BY qty DESC

Unsupported Data Access Objects and Methods

The following data access objects are not supported for linked SQL Server tables:

Container	Document
Index	QueryDef
Relation	Dynaset object in exclusive mode

The following methods are not supported:

CompactDatabase	CreateDatabase
CreateField	CreateQueryDef
DeleteQueryDef	ListParameters
ListTables	OpenQueryDef
RepairDatabase	Seek
SetDefaultWorkspace

Implementing Cascading Updates and Deletes

SQL Server Declarative Referential Integrity (DRI) does not include the ability to cascade updates or deletes. You can implement this feature with triggers or allow the Upsizing Wizard to write the trigger for you.

Here is a sample of the triggers created by the Upsizing Wizard when the Northwind Orders and Customers tables are upsized with cascade updates selected.

This is the update trigger written by the Upsizing Wizard for the Northwind Customers table:

If exists (select * from sysobjects where id = object_id('dbo.Customers_UTrig') and
sysstat & 0xf = 8)
drop trigger dbo.Customers_UTrig
GO

CREATE TRIGGER Customers_UTrig ON Customers FOR UPDATE AS

/*
* PREVENT NULL VALUES IN 'CompanyName'
*/
IF (SELECT Count(*) FROM inserted WHERE CompanyName IS NULL) > 0
BEGIN
RAISERROR 44444 'Field ''CompanyName'' cannot contain a null value.'
ROLLBACK TRANSACTION
END
/*
* CASCADE UPDATES TO 'Orders'
*/
IF UPDATE(CustomerID)
BEGIN
UPDATE Orders
SET Orders.CustomerID = inserted.CustomerID
FROM Orders, deleted, inserted
WHERE deleted.CustomerID = Orders.CustomerID
END

GO

If the CustomerID field is changed (IF UPDATE(CustomerID)) in the Customers table, this trigger updates the Orders table, changing the old CustomerID to the new CustomerID (SET Orders.CustomerID = inserted.CustomerID) for every order that contained the old CustomerID value (WHERE deleted.CustomerID = Orders.CustomerID).

This trigger also includes code to prevent null values for CustomerID in the Customers table. The wizard also creates three other triggers to enforce the referential integrity:

• Customers-Delete trigger
• Orders-Insert trigger
• Orders-Update trigger

For more information, see Knowledge Base article Q142480, "INF: Cascading Deletes and Updates of Primary Keys."

ODBC Driver Features

SQL Server version 6.5 implements some features not present in SQL Server 6.0. Some of these features require ODBC drivers to connect with the client application. Make sure that your client computer (where Microsoft Access is running) has the most recent version, or at least the version corresponding to your server, of the SQL Server ODBC driver. Look for Sqlsrvr.dll for 16-bit clients and Sqlsrv32.dll for 32-bit clients.

The ANSI_DEFAULTS Setting of the SQL Server 6.5 Driver

One of the significant improvements implemented with SQL Server 6.5 is compliance to the ANSI standard. The ODBC driver for SQL Server 6.5 uses and enforces that syntax. When the driver sets ANSI_DEFAULTS to ON, the following ANSI standards are activated:

• ANSI_NULLS
• ARITHABORT
• ANSI_NULL_DFLT_ON
• CURSOR_CLOSE_ON_COMMIT
• ANSI_PADDING
• IMPLICIT_TRANSACTIONS
• ANSI_WARNINGS
• QUOTED_IDENTIFIER

Queries written by Microsoft Access adhere to these standards. Avoid problems by writing your queries to the ANSI standards, too. For more information, see SQL Server Books Online. Also see Knowledge Base Articles Q135533 INF, "Differences in SQL Behavior Between ODBC and ISQL," and Q152032 INF, "Changes to SQL Server 6.5 That Affect 6.0 Apps."

Altering Tables

One of the reasons for Microsoft Access' tremendous success is the user interface. You can create tables, determine data types by example, and make changes to the table design even after it has been implemented. Actually, in many cases Microsoft Access doesn't really modify existing tables. Instead, it creates new tables according to your specifications, then moves data into it.

SQL Server makes changes to tables in the same way, but you must guide it through the steps. At first, you may regard this as a loss of functionality, but there is a reason for the difference in approach. Microsoft Access tables are typically smaller than SQL Server tables; data in a Microsoft Access table is usually measured in the tens of thousands or hundreds of thousands of records. In contrast, SQL Server tables often have millions of records. Many SQL Server databases are over 50 GB and some exceed 100 GB. Think twice before changing the structure of a table with 5,000,000 records. SQL Server will rewrite the table schema, copy the records to the new table, and rebuild all of the indexes for the table.

SQL Browser and Visual Studio DataTools

The SQL Server Browser Add-In included in the Upsizing Tools permits you to make some changes to your SQL Server data structure from within Microsoft Access, using a format similar to Microsoft Access design view. This tool has tabs for Tables, Views, Defaults, Rules, and Procs. This tool allows you to make changes equivalent to those that can be made with the ALTER TABLE syntax. More complex changes such as changing the data type of a field cannot be made here.

Microsoft has now released a new product Add-In called Visual Database Tools. This ships with the Enterprise Editions of Visual Studio^™ 97 development software, Visual Basic 5, and Visual C++® 5. This Add-In is a more sophisticated interface than the SQL Server Browser. It looks much like Microsoft Access and writes the SQL statements necessary for more complicated changes such as changes to data types.

Adding Fields

Adding fields is the easiest change to make. Using SQL Enterprise Manager, select your server in the Server Manager window. Click on the plus sign (+) to drill down through databases and select your database. On the Manage menu, select Tables. Select the desired table from the list. In this window you can view the design of the table, add new fields to the table, and change the names of the columns. You can't delete columns or change a column's data type.

You can also add a column using Transact-SQL language from SQL Enterprise Manager or from Microsoft Access with a SQL Pass-Through Query.

Type in the Transact-SQL statement in the following format:

ALTER TABLE SomeTable
ADD
Field1 INT NULL,
Field2 varchar(10) NULL

Because SQL Server rewrites tables to add the field(s), expect large table changes to take some time. SQL Server moves all of the data to new pages and rebuilds the indexes.

Changing the Data Type of a Field

You can change the data type of a field in two ways:

• Use a SELECT INTO statement. This method is rapid, but provides default behavior that may not be desirable.
• Use SQL scripts to give yourself full control over the process.

Using SELECT INTO

In the Server Manager window double-click on the database name to open the Manage Database window. Click Options and place a check in the Select into/Bulk Copy box. Now you can make a copy of a table, for example the jobs table in the pubs database.

SELECT * INTO newjobs FROM jobs

You can modify this query slightly with the CONVERT function. Here is the query to make a copy of the jobs table, changing the max_lvl field from a data type of tinyint to a float.

SELECT job_id, job_desc, min_lvl,
CONVERT(float,max_lvl) as max_lvl
INTO changedjobs FROM JOBS

Open each table in the Manage Tables window to see that although the table definition has changed, no defaults or constraints have been applied to the new table. Read the descriptions in the old table and recreate them in the new table. More dangerously, the converted column will have been created with the default NULL behavior (NULL on SQL Server 6.5, NOT NULL on SQL Server 6.0). If this is the desired result, verify the data in the new version, drop the old version of the table, and rename the new version with the original name. If you have any foreign key constraints, drop them before dropping the old table. Then recreate them with an ALTER TABLE statement.

Using Scripts

Changing a table by creating a new version from a SQL Script takes longer than using SELECT INTO, but it gives you more control over the process. You must make a new version of the table with the changed data type, move the data to the new table, rename (or delete) the old table, and rename the new table with the original table's name. Since you can't delete tables with relationships, you must first drop the relationships and recreate them. Recompile stored procedures that refer to the table to get a new object number for the new table. If the table you are changing is part of a complex structure of tables, you may find that it is quite complex to get all the details reconstructed properly. Document the structure well before you start making changes. Make a backup of databases before modifying them.

The following is the Transact-SQL syntax for changing a data type of a field. This example is intentionally complex to illustrate the level of detailed knowledge required to accurately make such a change. A field named ZipCode is changed from an integer to a five-character field (char(5)).

1. Drop any foreign key constraints that reference the table. None exist on this table, but if they occur in your tables, use the following syntax:

   ALTER TABLE Orders
   DROP CONSTRAINT UPKCLSuppliersConstr
   GO

2. Create a script for the Suppliers table. This creates the original table named Suppliers. This script incorrectly creates the ZipCode field as an integer field.

   set quoted_identifier on
   GO
   
   if exists (select * from sysobjects where id =
   object_id('dbo.Suppliers') and sysstat & 0xf = 3)
   drop table "dbo"."Suppliers"
   GO
   
   CREATE TABLE "dbo"."Suppliers"
   (
   "Name" varchar (50) NOT NULL ,
   "Address" varchar (50) NULL ,
   "City" varchar (20) NULL CONSTRAINT "CityDefault"
   DEFAULT ('Seattle'),
   "State" char (2) NULL CONSTRAINT "StateDefault"
   DEFAULT ('WA'),
   "ZipCode" "int" NULL ,
   CONSTRAINT "UniqueConstraint" UNIQUE NONCLUSTERED
   (
   "Name"
   ),
   CONSTRAINT "StateConstraint" CHECK
   (State = 'CA' or (State = 'OR' or (State = 'WA')))
   )
   GO

3. On the Server Manager window, select Generate SQL Scripts from the Object menu. Uncheck all of the Scripting Objects and select the Suppliers table. Presumably you will select all of the Scripting Options. Click Script and provide a name and path for the output. When the scripting is complete, close the Generate SQL Scripts window and open a SQL Query Tool window from the Tools menu. In the query window click on the file folder for the Open SQL Script option and select your script file. This brings up a script like the one above for the Suppliers table. Make the necessary changes to the script. For this example, we are changing the ZipCode field from an Int data type to a Char(5):

   "ZipCode" int NULL ,

   is changed to

   "ZipCode" Char (5) NULL ,

4. Change the script to refer to NewSuppliers, NewUniqueConstraint, NewStateConstraint, NewCityDefault, and NewStateDefault wherever you see Suppliers, UniqueConstraint, StateConstraint, CityDefault, and StateDefault.

   set quoted_identifier on
   GO
   
   if exists (select * from sysobjects where id =
   object_id('dbo.NewSuppliers') and sysstat & 0xf = 3)
   drop table "dbo"."NewSuppliers"
   GO
   
   CREATE TABLE "dbo"."NewSuppliers"
   (
   "Name" varchar (50) NOT NULL ,
   "Address" varchar (50) NULL ,
   "City" varchar (20) NULL CONSTRAINT "NewCityDefault"
   DEFAULT ('Seattle'),
   "State" char (2) NULL CONSTRAINT "NewStateDefault"
   DEFAULT ('WA'),
   "ZipCode" char (5) NULL ,
   CONSTRAINT "NewUniqueConstraint" UNIQUE
   NONCLUSTERED
   (
   "Name"
   ),
   CONSTRAINT "NewStateConstraint" CHECK
   (State = 'CA' or (State = 'OR' or (State = 'WA')))
   )
   GO

5. Save and run this script in the Query Tool. If it is successful, the standard message will appear: "This command did not return data, and it did not return any rows." Refresh the Table list to see the new table name. Inspect the NewSuppliers table and you should find it identical to the Suppliers table except for the data type of the ZipCode field and the slightly modified names of the constraints.
6. Move the data from the Suppliers table to the NewSuppliers table, converting the ZipCodes from integer to character data:

   INSERT INTO NewSuppliers
   SELECT Name, Address, City, State, convert(char(5),ZipCode)
   FROM Suppliers

7. Check your data to confirm that it is intact and converted, then rename the Suppliers table to OldSuppliers and rename the NewSuppliers table to Suppliers.
8. When we started we ran an ALTER TABLE statement to drop any foreign key constraints. You should now add back any such constraints to all dependent tables. Though our new table has the same name as the old, it has a new object ID number, so you must recompile any stored procedures that use it.

Deleting Fields

You can't delete fields in a table with SQL Server, but you can use the process just discussed. Instead of specifying a change in data type, omit the field that you wish to drop from the table creation script. Omit the field in the INSERT INTO statement and transfer all of the data except the unwanted field.

Binary, VarBinary, and Timestamp Fields

Microsoft Access attempts to display binary, varbinary, and timestamp fields as ASCII characters. If the data in one of these field types coincidentally corresponds to an ASCII character you may see strange letters or numbers. If there is no ASCII equivalent, the field will appear to be blank. This is discussed in Knowledge Base article Q121834 ACC, "Linked SQL Server Binary Data Displayed Incorrectly."

Timestamps

The SQL Server timestamp data type has no counterpart in Microsoft Access. In spite of its name, it is neither a time nor a date, nor is it some encoded representation of a time or date. When the Upsizing Wizard adds timestamp fields to a table it names them upsize_ts. If you query "SELECT upsize_ts FROM tablename" from an upsized table, a value similar to 0x0000000100002057 is returned. This is a hexadecimal representation of a number, so it often contains the letters a, b, c, d, and e. A timestamp is a number that increases each time it is used. The timestamp column on a table gets a new value each time it is updated. This permits the client program to confirm whether values have changed since it last checked the record.

The timestamp field indicates only that a record was changed, not when it was changed. You cannot set the timestamp column to any specific value. Because this data type does not have a corresponding Microsoft Access data type, it cannot be displayed in the Microsoft Access user interface.

Common Error Messages

Table name skipped or export failed

This generic message is provided by the Upsizing Wizard when something goes wrong. Check for a more meaningful error message on the Upsizing Report. If there is no additional information there, review the section below on the Convert OEM to ANSI Problem and review your data source. Find additional information in the SQL Server error log or the Windows NT Event Log.

If you can't find a more meaningful message, use the Upsizing Wizard to upsize the table structure without the data. Then link to the table and use an append query to move the data from Microsoft Access to SQL Server.

If the table structure won't transfer, try it again without transferring the table relationships. If that succeeds, modify the final table later to restore referential integrity. If all else fails, try exporting the table without the wizard. If you do this, you must attend to the indexes, referential integrity, defaults, and other elements on your own.

The Convert OEM to ANSI Problem

The common upsizing error "Table <table name> skipped or export failed" is usually caused by an incorrect setting in the ODBC data source.

Open Windows Control Panel and double-click ODBC. Select the data source for your server and click Configure. Click Options and verify that the Convert OEM to ANSI Characters check box is not checked. If it is, uncheck it, click OK, and close the ODBC dialog box. Try the Upsizing Wizard again.

If this fails, it may be because a user selected the language options associated with OEM to ANSI conversion while the box was checked. If that is the case, you can't easily undo those selections. Instead, make a note of the other values in the dialog box, remove the whole data source, and recreate it, taking care not to check the OEM to ANSI check box. Upsizing will normally proceed without this error if you use a data source newly created in this way.

ODBC Call Failed

This is an incomplete error message. After you click OK, it should be followed by the error that caused the ODBC call to fail.

ODBC Query Timeout
Server Error 0: Timeout expired
SQL that Caused Error(s)
UT_CopyData

The queries that move the data are internal to the wizard. Their timeout was lengthened programmatically to prevent the default 60-second timeout. Unfortunately, if you have a table that is several megabytes or if your network has a lot of traffic, the timeout still may not be long enough. Upsize the table structure without data, link to the new table, then move the data into it a Microsoft Access append query. Either set the ODBC Timeout property to 0 for your append query, or use a criteria that selects and moves only a portion of the data at a time. Repeat this until the entire table has been moved.

Microsoft does not support modifications of the Upsizing Wizard tool, however if this problem is causing a major stumbling block, try this:

1. Close and reopen Microsoft Access.
2. Open the wizard database Wzcx.mda (Microsoft Access 95) or Wzcs97.mda (Microsoft Access 97).
3. Open the UT_modGlobals module and search for "CLng(vODBCTimeout)".
4. You should have found the following:

   If Not IsMissing(vODBCTimeout) Then
   qry.ODBCTimeout = CLng(vODBCTimeout)
   End IF

5. Comment out this line:

   'If Not IsMissing(vODBCTimeout) Then

6. Change the ODBC Timeout to zero.

   qry.ODBCTimeout = 0

7. Comment out this line:

   'End If

8. Now, the Upsizing Wizard should never time out. Use this carefully.

SQL Server error 1105: Can't allocate space

Here is the full error.

Can't allocate space for object '<usually a table name>' in database '<dbname>'
because the '<name>' segment is full. If you ran out of space in Syslogs, dump
the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase
the size of the segment.

This error means your database is full. There are two situations in which this problem will occur. First, your database may be too small to hold the data. You must make the SQL Server database larger.

The second problem is more complicated and more common. The Upsizing Wizard moves the tables over in a transaction so that either the whole table is successfully transferred or none of the table is transferred. During the move, the data is written to the database twice: to the target table and to the transaction log. The log is integral to the transaction mechanism. If the transfer fails, the log establishes which records must be removed again. Because of this, you need to have twice as much room in the database as the largest table being moved. If the log and data are on separate devices, both the log portion and the data portion must be large enough to hold the largest table.

Sometimes you don't want to increase the size of your database. During the upsizing you are moving a lot of data, an activity that may not be a typical use pattern. The large log size required during the move may not be needed later. Though it is easy to increase the size of a database, it is difficult to shrink one. To get around this problem, consider one of these options:

• Use the Upsizing Wizard to create the table structure. Move the data over in smaller sections. Link to the table and use a Microsoft Access append query with a criteria that selects only portions of the data at one time. Repeat this until the entire table has been moved.
• Use trunc. log on chkpt. to clear out the transaction log after each transaction. That way, the logged data from the first table is dropped before you start moving the second table.

Server Error 191: SQL Statement Nested Too Deeply

Each field in a Microsoft Access table has a required property. If that field has a Yes property, indicating that a value must be entered, the Upsizing Wizard preserves that property when the table is move to SQL Server. By default, the Upsizing Wizard creates a trigger to enforce a required entry, nesting IF statements for each required field. The result is a trigger like:

CREATE TRIGGER customers_Utrig ON customers FOR UPDATE AS
/* PREVENT NULL VALUES IN 'cust_ID' */
IF (SELECT Count(*) FROM inserted WHERE 'cust_ID' IS NULL) >0
BEGIN
RAISERROR 44444 'Field' 'cust_ID' 'cannot contain a null value.'
ROLLBACK TRANSACTION
END
ELSE
/* PREVENT NULL VALUES IN 'name' */
IF (SELECT Count(*) FROM inserted WHERE 'name' IS NULL) >0
BEGIN
RAISERROR 44444 'Field' 'name' 'cannot contain a null value.'
ROLLBACK TRANSACTION
END
ELSE

The Upsizing Wizard uses triggers because they can be changed easily to allow null values later, most closely resembling Microsoft Access behavior. A SQL Server developer would more likely specify either NULL or NOT NULL for each field when creating the table definition. This looks like:

CREATE TABLE customers
(
cust_ID (CHAR(5) NOT NULL,
name VARCHAR(50) NOT NULL,
license VARCHAR(7) NULL,
U

There are three reasons why you might not use triggers to prevent the entry of null values into a field. First, a NOT NULL constraint (the NOT NULL table definition) fires before data is entered into the table. It is very fast. A trigger, on the other hand, permits the entry of the null record before evaluating the entry. If the trigger discovers an invalid value, it rolls back the insert or update. This is inherently slow.

Second, if you have many required fields, the creation of the trigger fails with errors similar to the following:

Server Error 170: Line 600: Incorrect syntax near 'WHERE;.
Server Error 191: Some part of your SQL Statement is nested too deeply.
Please re-write the query or break it up into smaller queries.

This is because the nesting level eventually fails.

Third, database purists like to use NULL and NOT NULL in their table definitions because that is the ANSI standard. Triggers don't exist in all databases, and when they do they are not implemented the same across all databases. Defining your tables with NULL and NOT NULL is good programming practice because it makes your applications more portable.

The Upsizing Wizard can be changed to use the standard NULL / NOT NULL syntax to specify required fields. Start Microsoft Access and open the Upsizing Wizard library database. For Microsoft Access 95, the filename is Wzcs.mda. For Microsoft Access 97, the filename is Wzcs97.mda. When the database is open, click the Modules tab and open the UT_ModUserConstants module. Search for the UT_USE_NULL_CONSTRAINTS constant and change the default value from False to True.

Run Out of Locks

SQL Server is initially installed with the configuration setting of 5,000 locks. If you run out you will receive this error:

Server Error 1204: SQL Server has run out of LOCKS. Re-run your command when there are
fewer active users, or ask your System Administrator to reconfigure SQL Server with
more LOCKS.

Use the Server Configuration dialog box to increase the locks. The lock setting can be increased while you upsize your data, then reduced for the smaller activity expected during normal use. Alternatively, use the Edit Database dialog box to put your database in single-user mode so that SQL Server can maintain transaction integrity without using locking.

#Deleted or Data has Changed, Operation Stopped

These errors occur when Microsoft Access thinks a record is no longer present. It usually occurs because of data type conversion issues. The solution is to add a timestamp column to the table.

If adding a timestamp field to your table does not resolve the problems, you probably are using a float data type as your primary key. To update a record, Microsoft Access identifies it by the primary key value. If that key is of a data type that cannot be accurately converted, the update will fail. The best solution is to avoid float and datetime fields as primary keys or as part of combined field primary keys. If this is not possible, use stored procedures to manipulate the table data.

Often, a special problem occurs when you upsize a Microsoft Access 95 database to a SQL Server 6.5 database. The Upsizing Wizard creates the table, moves the data into it, and then alters the table to add the timestamp. The timestamps are still blank when the process is completed. The SQL Server 6.5 ODBC driver enforces ANSI syntax not supported by Microsoft Access 95. Microsoft Access mistakenly creates queries with NULL timestamp fields "= 0x00" instead of "IS NULL." This syntax error prevents Microsoft Access from finding timestamp fields with null values. To solve this problem, update every record in the table after the upsizing has completed. This will populate the timestamp field. You can update simply by setting a field value to itself. Pick some non-indexed field to do this quickly. For example:

UPDATE mytable
SET zipcode = zipcode

This will update every record without changing data.

If you get the #Deleted value when sorting large record sets in SQL Server 6.5, apply Service Pack 2.

Invalid Argument

To resolve this problem with zero-length strings in Microsoft Access 2.0, replace the zero-length string with a null. Create a query with the field defined below, replacing <fieldname> with the name of your problem field:

Expr1:Len([<fieldname>])
Critieria: 0

Run this query to return records with fields containing zero-length strings.

Now change the SELECT query to an UPDATE query and type null in the Update To row of <fieldname>. Run the query. All zero length strings in the field are changed to null values. Now you can export the table to SQL Server. For more information, see the Microsoft Access Knowledge Base article Q131584, "ACC2: 'Invalid Argument' Error Msg Exporting Table to SQL Server."

Table Is Read Only

Because the data in a SQL Server table is not in a native Microsoft Access format, Microsoft Access cannot manage it the same way it manages a native Microsoft Access table. It must have a unique index for identifying each row individually. For best performance, provide a unique index on each table.

When you link to a table without a unique index, you are prompted for a unique field or combination of fields.

In order to update records in this table you must select which field or fields
uniquely identify each record. Select up to ten fields.

The status bar at the bottom of the screen presents the message "This Recordset is not updateable" if it attempts to edit a record in a table without a unique index.

The status bar at the bottom of the screen presents the message "Records not deleted. Data is read only" if it attempts to delete a record in a table without a unique index.

If a unique field or combination of fields exists but has no unique index, you can create an index in Microsoft Access, creating a copy of the field or fields so that Microsoft Access can identify each record. You can do one of the following:

• Identify the field or fields for the unique index when you link to the table.
• Open a new query in design view and then create and run a query like:

  CREATE UNIQUE INDEX myindex
  ON
  NameOfTheTable(UniqueColumnName)

No matter how the index is created, it keeps a copy of the field or fields necessary to identify each row uniquely. If you can create a unique index on the server, SQL Server performs much faster in almost all circumstances. An index constructed in Microsoft Access for a SQL Server table is less useful, requires more space, and needs more maintenance to keep current. Use a Microsoft Access index only when the table must be updated and the index cannot be created on the server.

Numeric Data Out of Range

This error occurs with the original release of Microsoft Access for Windows 95 and a linked SQL Server table with an identity column. This bug is fixed in the free upgrade Microsoft Access 7.0a. Knowledge Base articles Q149535 and Q153151 describe this problem. To obtain this upgrade, call the Microsoft Order Desk at (800) 360-7561.

Timeout Expired from 16-bit Applications

If you receive a timeout expired message immediately after your second asynchronous retry, you may need an updated SQL Server ODBC driver. This problem can occur with any network library when connecting with Microsoft Access 2.0, Visual Basic 3 or Visual Basic 4 16-Bit in asynchronous mode. The problem occurs in the SQL Server ODBC driver (Sqlsrvr.dll) version 2.65.0201 and is corrected in the drivers shipped with SQL Server 6.5, Service Pack 1. Upgrade to the new driver. You can also work around the problem by setting DisableAsync = 1 in the Msacc20.ini file. For more information, see Knowledge Base article Q153908, "16 bit Driver Times Out in calls to SQL Server."

Domain Credentials Are Requested Twice on a Novell Network

When you make a trusted connection to SQL Server using the 16-bit Multi-Protocol Netlibrary on a Windows for Workgroups client computer that is logged on to both a Microsoft domain and a Netware network, you may be asked for your domain credentials again. This can happen even if the connection is forced over Named Pipes. This problem is discussed in Knowledge Base article Q154631, "BUG: Domain Credentials Inappropriately Requested." Currently, the only resolutions are to provide the domain credentials again or to remove the Netware network shell.

Tables Do Not Show in SQL Enterprise Manager After Upsizing

Remember that SQL Enterprise Manager is not SQL Server, it is a client just like Microsoft Access. If you make a change to your server using SQL Enterprise Manager, it will reflect that change. If you make a change to the server from some other client (such as Microsoft Access), SQL Enterprise Manager will not know about it until it checks with the server for new information. In the Server Manager window of SQL Enterprise Manager, right-click in the appropriate place ("Tables" or "Databases") and select Refresh from the menu.

Text Fields Are Incomplete

By default, a limit of 4K is transmitted for text fields. Increase this amount by using the SET statement. When upsizing tables, the Upsizing Wizard increases the text size to 2,147,483,647 bytes. You can do the same with the command:

SET TEXTSIZE 2147483647

SET statements only persist for the current session and connection.

Conclusion

After you have migrated your Microsoft Access database to SQL Server, your data retrieval and data processing needs will visibly benefit from SQL Server's client/server architecture. Your SQL Server database will be available 24 hours a day, 7 days a week. Parallel processing will be easy, and your database can grow with your data requirements.

Finding More Information

MS Knowledge Base Articles

The most important reference for any developer is the Microsoft Knowledge Base (KB). Available on the Microsoft TechNet compact disc and free on the Microsoft Web site at http://www.microsoft.com, the KB is a compendium of articles written on thousands of topics for each of Microsoft's major desktop applications, BackOffice® programs, and operating systems. Each article is a discussion of some feature, technique, problem, or bug and describes how to optimize your application to take advantage of features or work around problems. These articles are written by the Microsoft technical staff who assist customers every day and are concentrated in the areas where customers require the most guidance. New information is frequently posted. This resource is particularly important to use with new product releases and service packs.

The MS Jet Database Programmer's Guide

Familiarly known as the Jet Book, this comprehensive Microsoft Press® publication by Dan Haught and Jim Ferguson (ISBN 1-55615-877-7) is an authoritative resource about the Microsoft Jet Engine, the database engine that underlies Microsoft Access. The Jet Book has detailed discussions about query optimization, rule enforcement, and database implementation, along with many other features of the Jet Engine. This is required reading for advanced program developers in Microsoft Access 95 and Microsoft Access 97.

Upsizing Tool Files

Both the Microsoft Access 95 and Microsoft Access 97 Upsizing Tools include Readme and Help files that contain many helpful topics.

Appendix A

Table of Data Type Conversions

When you move a Microsoft Access table to SQL Server either with the Upsizing Wizard or by exporting the table, the data types change to their corresponding SQL Server data types. Since SQL Server bases its data types on the C programming language while Microsoft Access bases its data types on the Visual Basic programming language, the data types are converted as described in the following table. Though you can easily change a field's data type in Microsoft Access, it is not possible to change a data type in SQL Server. A new table must be created and the data can then be transferred to that table. You may want to review your data types before upsizing and make any desired changes before moving the table.

Microsoft Access	SQL Server
Text	Varchar *
Memo	Text
Byte	Smallint
Integer	Smallint
Long Integer	Int
Single	Real
Double	Float
Replication ID	Varbinary
Date/Time	Datetime
Currency	Money
Autonumber (Long Integer)	Int (Identity)
Yes/No	Bit
OLE Object	Image

* For more information about modifying the Upsizing Wizard to transfer Microsoft Access text files as char fields instead of varchar fields, see "Changes to Your Data" in this paper.

The following table shows a SQL Server data type and how it is displayed by Microsoft Access when the table is linked.

SQL Server	Microsoft Access
Binary	Binary
Varbinary	Binary
Char	Text
Varchar	Text
Datetime	Date/Time
Smalldatetime	Date/Time
Decimal	Text
Numeric	Text
Float	Double
Real	Single
Int	Integer
Smallint	Integer
Tinyint	Integer
Identity	Autonumber
Money	Currency
Smallmoney	Currency
Bit	Yes/No
Timestamp	Binary
Text	Memo
Image	OLE Object
User-defined data types	Varies

Appendix B

Expression Translation Table

When you add defaults to SQL Server with the SQL Server Browser, the SQL Server Browser converts most common Microsoft Access expressions to SQL Server expressions. The following expressions are converted:

Microsoft Access functions	SQL Server functions
String functions
chr$(x)	char(x)
asc(x)	ascii(x)
str$(x)	str(x)
space$( x)	space(x)
lcase$(x)	lower(x)
ucase$( x)	upper(x)
len(x)	datalength(x)
ltrim$( x)	ltrim(x)
rtrim$(x)	rtrim(x)
right$(x,y)	right(x,y)
mid$(x,y,z)	substring(x,y,z)
Conversion functions
cint(x)	convert(smallint,x)
clng(x)	convert(int,x)
csng(x)	convert(real,x)
cdbl(x)	convert(float,x)
cstr(x)	convert(varchar,x)
ccur(x)	convert(money,x)
cvdate(x)	convert(datetime,x)
Date functions
now(x)	getdate(x)
date(x )	convert(datetime,convert(varchar,getdate(x)))
year(x)	datepart(yy,x)
month(x)	datepart(mm,x)
day(x)	datepart(dd,x)
weekday(x)	datepart(dw,x)
hour(x)	datepart(hh,x)
minute(x)	datepart(mi,x)
second(x)	datepart(ss,x)
datepart("<Access datepart>", x)	datepart(<SQL Server datepart>, x)
dateadd("<Access datepart>", x, y)	dateadd(<SQL Server datepart>, x, y)
datediff("<Access datepart>", x, y)	datediff(<SQL Server datepart>, x, y)
Math functions
int(x)	floor(x)
sgn(x)	sign(x)

The Upsizing Wizard and the SQL Server Browser replace a number of delimiters, operators, constants, and wildcard characters, as listed in the following table.

Description	Microsoft Access	SQL Server
Date delimiter	#	'
String delimiter	"	'
Mod operator	mod	%
Concatenation operator	&	+
Wildcard character	?	_
Wildcard character	*	%
Constant	Yes	1
Constant	On	1
Constant	True	1
Constant	No	0
Constant	Off	0
Constant	False	0

Appendix C

Example of Using Vbsql.ocx

The following example uses the bcp functions of Vbsql.ocx to copy the authors table in the pubs database to a file called Authors.sav. For this code example to work you must have the file Vbsql.ocx present and registered on your computer. With a code module open in Microsoft Access, click References on the Tools menu and be sure that Vbsql.ocx is checked in the list of Available References. If the file does not appear in the list, browse for it, and select it.

Some lines of code below exceed the printed page. Such lines use an underscore as a line continuation character.

'Connection declarations
Declare Function SqlLogin Lib "VBSQL.OCX" () As Long
Declare Function SQLSETLUSER Lib "VBSQL.OCX" Alias_
"SqlSetLUser" (ByVal Login As Long, User As String) As Long
Declare Function SqlSetLPwd Lib "VBSQL.OCX" (ByVal Login As_
Long, Pwd As String) As Long
Declare Function SqlSetLApp Lib "VBSQL.OCX" (ByVal Login As_
Long, App As String) As Long
Declare Function SqlOpen Lib "VBSQL.OCX" (ByVal Login As Long,_
Server As String) As Long
'bcp declarations
Declare Function SqlbcpInit Lib "VBSQL.OCX" (ByVal SqlConn As_
Long, TblName As String, HFile As String, ErrFile As String, ByVal_
Direction As Integer) As Long
Declare Function SqlbcpExec Lib "VBSQL.OCX" (ByVal SqlConn_
As Long, RowsCopied As Long) As Long
Declare Function SqlbcpColfmt Lib "VBSQL.OCX" (ByVal SqlConn_
As Long, ByVal FColumn As Long, ByVal FType As Long, ByVal_
FPLen As Long, ByVal FCLen As Long, FTerm As String, ByVal_
FTLen As Long, ByVal TCol As Long) As Long
Declare Function SqlbcpColumns Lib "VBSQL.OCX" (ByVal_
SqlConn As Long, ByVal ColCount As Long) As Long
'Global values for bcp in and out and succeed and fail
Global Const DBIN& = 1 ' transfer from client to server
Global Const DBOUT& = 2 ' transfer from server to client
Global Const SUCCEED& = 1
Global Const FAIL& = 0
'Connection closing declarations
Declare Sub SqlClose Lib "VBSQL.OCX" (ByVal SqlConn As Long)
Declare Sub SqlExit Lib "VBSQL.OCX" ()
Declare Sub SqlWinExit Lib "VBSQL.OCX" ()

Function bcp_out()
Dim Loginrec As Long
Dim SqlConn As Long
Dim Result As Long
Dim RowsCopied As Long
Dim strServer As String

On Error GoTo bcp_out_err

'Specify the login parameters
Loginrec = SqlLogin
strServer = InputBox("Enter the name of your server.""Server_
Name?")
Result = SQLSETLUSER(Loginrec, "sa")
Result = SqlSetLPwd(Loginrec, "")
Result = SqlSetLApp(Loginrec, "AccExamp")
'Open a connection to SQL Server
SqlConn = SqlOpen(Loginrec, strServer)
If SqlConn = 0 Then MsgBox "Failed to open connection to_
server." & Chr$(10) & "Check server name."
'Initialize bcp.
Result = SqlbcpInit(SqlConn, "pubs..authors", "c:\authors.sav",_
"c:\bcperr.txt", DBOUT)
If Result = FAIL Then
MsgBox "bcp Init failed!"
GoTo bcp_out_err
End If
'Execute the bulk-copy.
Result = SqlbcpExec(SqlConn, RowsCopied)
If Result = FAIL Then
MsgBox "Incomplete bulk-copy. Only " & RowsCopied & " rows_
copied."
GoTo bcp_out_err
End If
'Close the connection
SqlClose (SqlConn)
Exit Function
bcp_out_err:
MsgBox "Error " & Err & ": " & Err.Description
'Close the connection
SqlClose (SqlConn)
End Function

Appendix D

Example of ODBC Cursors in VBA

The following example makes an ODBC connection to your specified datasource and returns the first five rows.

Some lines of code below exceed the printed page. Such lines use an underscore as a line continuation character.

'Selected ODBC Core API's Definitions -- 32 bit versions
Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal_
hEnvironmentv&, phConnect&) As Integer
Declare Function SQLAllocEnv Lib "odbc32.dll" (phEnvironmentv&)_
As Integer
Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal hConnect&,_
phstmt&) As Integer
Declare Function SQLConnect Lib "odbc32.dll" (ByVal hConnect&,_
ByVal szDSN$, ByVal cbDSN%, ByVal szUID$, ByVal cbUID%, ByVal_
szAuthStr$, ByVal cbAuthStr%) As Integer
Declare Function SQLSetStmtOption Lib "odbc32.dll" (ByVal_
hstmt&, ByVal fOption%, ByVal vParam&) As Integer
Declare Function SQLSetCursorName Lib "odbc32.dll" (ByVal_
hstmt&, ByVal szCursor$, ByVal cbCursor%) As Integer
Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal hstmt&,_
ByVal szSqlStr$, ByVal cbSqlStr&) As Integer
Declare Function SQLNumResultCols Lib "odbc32.dll" (ByVal_
hstmt&, pccol%) As Integer
Declare Function SQLExtendedFetch Lib "odbc32.dll" (ByVal_
hstmt&, ByVal fFetchType%, ByVal irow&, pcrow&, rgfRowStatus%)_
As Integer
Declare Function SQLSetPos Lib "odbc32.dll" (ByVal hstmt&, ByVal_
irow%, ByVal fOption%, ByVal fLock%) As Integer
Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal_
hConnect&) As Integer
Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal_
hConnect&) As Integer
Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal_
hEnvironmentv&) As Integer
Declare Function SQLGetData Lib "odbc32.dll" (ByVal hstmt&,_
ByVal icol%, ByVal fCType%, ByVal rgbValue As String, ByVal_
cbValueMax&, pcbValue&) As Integer
Global Const SQL_ERROR As Long = -1
Global Const SQL_INVALID_HANDLE As Long = -2
Global Const SQL_NO_DATA_FOUND As Long = 100
Global Const SQL_SUCCESS As Long = 0
Global Const SQL_SUCCESS_WITH_INFO As Long = 1
Global Const SQL_CONCUR_READ_ONLY As Long = 1
Global Const SQL_CONCUR_LOCK As Long = 2
Global Const SQL_CONCUR_ROWVER As Long = 3
Global Const SQL_CONCUR_VALUES As Long = 4
Global Const SQL_CURSOR_FORWARD_ONLY As Long = 0
Global Const SQL_CURSOR_KEYSET_DRIVEN As Long = 1
Global Const SQL_CURSOR_TYPE As Long = 6
Global Const SQL_CONCURRENCY As Long = 7
Global Const SQL_ROWSET_SIZE As Long = 9
Global Const SQL_NTS As Long = -3
'Global Const SQL_FETCH_NEXT As Long = 1
Global Const SQL_FETCH_FIRST As Long = 2
Global Const SQL_POSITION As Long = 0
Global Const SQL_LOCK_NO_CHANGE As Long = 0
Global Const SQL_CHAR As Long = 1

Function callODBCcursor()
Dim Result As Integer 'Return value.
Dim hEnvironment As Long 'Environment handle.
Dim hConnect As Long 'Connection handle.
Dim hstmt As Long 'Statement handle.
Dim errorlocation As String
Dim RowSetSize As Long
Dim strSQL As String
Dim ColCount As Integer
Dim strThisRecord As String * 256
Dim outlen As Long
Dim strData As String
Dim strDataFinal As String
Dim RowCount As Integer
Dim Cols As Integer
Dim RowNum As Long
Dim RowsGot As Long
Dim RowStat As Integer

On Error GoTo callODBCcursor_err

' Get an environment and connection handle
Result = SQLAllocEnv(hEnvironment) 'Allocate environment handle
' allocate connection handle
Result = SQLAllocConnect(ByVal hEnvironment, hConnect)
' Set up the connection string
Dim strdsn As String, strUID As String, strPWD As String
strdsn = InputBox("Enter the name of your_
datasource.""Datasource Name?")
If strdsn = "" Then
errorlocation = "DatasourceName"

GoTo callODBCcursor_err
End If
strUID = "sa"
strPWD = ""
'Establish the connection
Result = SQLConnect(hConnect, strdsn, Len(strdsn), strUID,_
Len(strUID), strPWD, Len(strPWD))
If Result = SQL_ERROR Then 'SQL_ERROR is +1
errorlocation = "SQLConnect"
GoTo callODBCcursor_err
End If
'Allocate a statement handle.
Result = SQLAllocStmt(hConnect, hstmt)
If Result = SQL_ERROR Then
errorlocation = "SQLAllocStmt"
GoTo callODBCcursor_err
End If
'Specifies "optimistic concurrency control, comparing row versions"
Result = SQLSetStmtOption(hstmt, SQL_CONCURRENCY,_
SQL_CONCUR_ROWVER)
If Result = SQL_ERROR Then
errorlocation = "SQLSetStmtOption, Concurrency"
GoTo callODBCcursor_err
End If
'Specifies a keyset driven cursor
Result = SQLSetStmtOption(hstmt, SQL_CURSOR_TYPE,_
SQL_CURSOR_KEYSET_DRIVEN)
If Result = SQL_ERROR Then
errorlocation = "SQLSetStmtOption Cursor Type"
GoTo callODBCcursor_err
End If
'Specifies the number of rows that are returned with the keyset
RowSetSize = 5
Result = SQLSetStmtOption(hstmt, SQL_ROWSET_SIZE,_
RowSetSize)
If Result = SQL_ERROR Then
errorlocation = "SQLSetStmtOption Rowset Size"
GoTo callODBCcursor_err
End If
Result = SQLSetCursorName(hstmt, "C1", SQL_NTS)
If Result = SQL_ERROR Then
errorlocation = "SQLSetCursorName"
GoTo callODBCcursor_err
End If
'The SQL String that defines the data being returned by the cursor
strSQL = "Select title_id, title from titles"
'Submit the SQL Statement
Result = SQLExecDirect(hstmt, strSQL, Len(strSQL))
'How many columns came back?
Result = SQLNumResultCols(hstmt, ColCount)

RowCount = 1

Result = SQLExtendedFetch(hstmt, SQL_FETCH_FIRST,_
RowNum, RowsGot, RowStat)

'Loop through each row "fetched" by the cursor
For RowCount = 1 To RowSetSize
Result = SQLSetPos(hstmt, RowCount, SQL_POSITION,_
SQL_LOCK_NO_CHANGE)

'Loop through each column
strData = ""
For Cols = 1 To ColCount
Result = SQLGetData(hstmt, Cols, SQL_CHAR,_
strThisRecord, 256, outlen)
strData = strData & Left(strThisRecord, outlen)
'Add_ spaces for formatting

If Cols < ColCount Then strData = strData & " "
Next Cols
strDataFinal = strDataFinal & strData & Chr$(10) & Chr$(13)
Next RowCount

'Display final output
Beep
MsgBox strDataFinal

'Disconnect and free the connection and environment handles
Result = SQLDisconnect(hConnect)
Result = SQLFreeConnect(hConnect)
Result = SQLFreeEnv(hEnvironment)

Exit Function

callODBCcursor_err:
MsgBox "Error in section " & errorlocation & "."
MsgBox "Error " & Err & ": " & Err.Description
Result = SQLDisconnect(hConnect)
Result = SQLFreeConnect(hConnect)
Result = SQLFreeEnv(hEnvironment)
End Function

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

©1997 Microsoft Corporation. All rights reserved.

Microsoft, BackOffice, the BackOffice logo, Microsoft Press, Visual Basic, Visual C++, Visual Studio, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Other trademarks and tradenames mentioned herein are the property of their respective owners.

The names of companies, products, people, characters, and/or data mentioned herein are fictitious and are in no way intended to represent any real individual, company, product, or event, unless otherwise noted.