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:

The following methods are not supported: