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?
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.
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."