Linked to SQL Server Bigint Primary Key

  • Thread starter Thread starter KarenW
  • Start date Start date
K

KarenW

From Access 2002 (and Access 97) on XP, when you link to
a SQL Server 2000 table that has a primary key that is of
the BIGINT datatype, the table is displayed in Access as
all fields #DELETED. If the datatype of the primary key
is changed to INT, then all is fine and displays
correctly. If I create a view of the BIGINT table using
as CAST as INT for the field that is BIGINT, then all
works fine displaying correctly. If BIGINT is any field
other than the primary key and the primary key is int,
then all displays correctly.

I have applied all Access, Jet, SQL Server, XP, and
Windows services packs and updates. I have reproduced
this problem on numerous desktops.

Is anyone else seeing this problem?
 
I'm not sure about Access 2002 but I *know* bigint isn't supported on
Access 97. That data type was introduced in SQLS 2000, which was
released after Access 97. My guess is that a bigint in a non-PK field
is being implicitly converted to a double, but that in a PK that isn't
being done, for reasons best known to the engine. I applaud you on
your diligent application of service packs -- always a good thing, and
known to fix many bugs -- but not this problem, which is basically one
of more recent software attempting to interact with older versions
which were written without the benefit of knowing what features would
be available in the future.

-- Mary
MCW Technologies
http://www.mcwtech.com
 
My guess is that a bigint in a non-PK field
is being implicitly converted to a double, but that in a PK that isn't
being done, for reasons best known to the engine.
Click to expand...

I guess it's because bigint stores more significant figures than double,
so it would be possible for different bigint PK values to map to the
same double value.
 
Back
Top