Knowledge Base Article Number: Q1064870

Question by: Loretta Yuen    Answered by: David Klein    Last Updated: 13/09/2005 3:37:25 PM

Spot an error? Please comment about this article

Why do I have problems with certain tables in my Access 97 frontend when I upsize to SQL Server? Do I need to change datatypes? If so, how?

Answer:
Access 97 was not designed at the outset with a SQL Server 7 or 2000 backend in mind. Access 97 in particular has many issues with unicode and bit fields.

See this article for more information on Access issues with bit fields

BIT FIELDS:
To overcome this problem with bit fields, SSW recommends that you change the data type from bit to integer. You cannot use tiny int, as it cannot represent -1 (The value of true in Access). Instead, use a datatype of integer. Normally, this should have a default value of (0). Also note that when you change the datatype from bit to int, SQL Server will keep the values as 1 for True and 0 for false (as it was in the bit field). The problem is, Access will interpret the value of True incorrectly. You will need to do an update statement that will update all bit fields with a value of 1, to a value of -1.

To do this, use the following queries. Do this BEFORE you change the structure to integer fields:
SELECT * INTO _ztBitFields
FROM information_schema.columns
where data_type = 'bit'

After making the changes, run the output of this query to correct the values of your bit fields:
SELECT 'UPDATE ' + Table_Name + ' SET [' + Column_Name + '] = -1 WHERE [' + Column_Name + ']=1'
FROM _ztBitFields

NVARCHAR AND NCHAR FIELDS:
With nvarchar fields with a size of 255, Access 97 will have problems with modifying and viewing rows with 255 characters in them. This is because unicode (the n prefix designates unicode) stores extra information in SQL Server, and Access does not interpret it correctly. To simplify matters, just convert any nvarchar fields varchar fields. A simple way to do change data types to:

1) Generate a full script of your database
2) Do a find and replace on the script for nvarchar, nchar and bit in the text of the script. Replace them with varchar, char and int respectively.
3) Run the script to generate the corrected database.
4) Use Red Gate SQL Compare to correct the structure of your current database (with all the data) to the newly corrected one.

Keywords: SSW Upsizing PRO!  SSW SQL Auditor  Hot Tip - Access  Hot Tip - SQL Server 2000    

New Search SSW Custom Support