Description:
I am using Access 97 sr2 on Windows XP to access Mysql tables on my SCO Unix Server. I am simply linking to the tables and browsing with Access. Everything works fine except for rows with unsigned integer columns (TINYINT or SMALLINT) that have large values in them (i.e. >127 for TINYINT or >32,767 for SMALLINT). These rows are displayed by Access with "#Deleted" in all columns.
My guess is these values are not being handled correctly due to the high order (sign) bit being set. I notice that TINYINT is converted to Integer in Access whereas Byte would be sufficient. I would expect that SMALLINT unsigned values greater than 32,767 would appear as negative values in Access because the Integer type is signed and is not big enough to represent the true unsigned value. This could be avoided by up-converting unsigned SMALLINT fields to Long Integers in Access. Either way would be acceptable as long as the values can be retrieved.
Thank you for your assistance!
How to repeat:
Below is my sql to create two tables with this problem, one with an unsigned TINYINT and the other unsigned SMALLINT. I just link to the tables with Access and open them in Datasheet view. Access displays all fields in rows 128 & 32768 with "#Deleted" instead of the proper values.
drop table if exists uint8;
create table uint8 (
uint8 tinyint unsigned not null,
descr char(25) binary not null,
primary key (uint8)
);
insert into uint8 set uint8=127,descr='works fine';
insert into uint8 set uint8=128,descr='shows #deleted';
drop table if exists uint16;
create table uint16 (
uint16 smallint unsigned not null,
descr char(25) binary not null,
primary key (uint16)
);
insert into uint16 set uint16=32767,descr='works fine';
insert into uint16 set uint16=32768,descr='shows #deleted';
Description: I am using Access 97 sr2 on Windows XP to access Mysql tables on my SCO Unix Server. I am simply linking to the tables and browsing with Access. Everything works fine except for rows with unsigned integer columns (TINYINT or SMALLINT) that have large values in them (i.e. >127 for TINYINT or >32,767 for SMALLINT). These rows are displayed by Access with "#Deleted" in all columns. My guess is these values are not being handled correctly due to the high order (sign) bit being set. I notice that TINYINT is converted to Integer in Access whereas Byte would be sufficient. I would expect that SMALLINT unsigned values greater than 32,767 would appear as negative values in Access because the Integer type is signed and is not big enough to represent the true unsigned value. This could be avoided by up-converting unsigned SMALLINT fields to Long Integers in Access. Either way would be acceptable as long as the values can be retrieved. Thank you for your assistance! How to repeat: Below is my sql to create two tables with this problem, one with an unsigned TINYINT and the other unsigned SMALLINT. I just link to the tables with Access and open them in Datasheet view. Access displays all fields in rows 128 & 32768 with "#Deleted" instead of the proper values. drop table if exists uint8; create table uint8 ( uint8 tinyint unsigned not null, descr char(25) binary not null, primary key (uint8) ); insert into uint8 set uint8=127,descr='works fine'; insert into uint8 set uint8=128,descr='shows #deleted'; drop table if exists uint16; create table uint16 ( uint16 smallint unsigned not null, descr char(25) binary not null, primary key (uint16) ); insert into uint16 set uint16=32767,descr='works fine'; insert into uint16 set uint16=32768,descr='shows #deleted';