data appears as deleted when using a bigint

[enders]

Hi,

I have the following problem.
I have created a table with a bigint on a SQL 2000 database
When I make an ODBC link from MS Access 2003 (.MDB) all data in that table
appears as deleted.

When I use an .ADP project everything works fine.
Any suggestions ?

BTW why can't you use the option Object dependencies in an .ADP file ?

CE

 

[Rick Brandt]

Hi,

I have the following problem.
I have created a table with a bigint on a SQL 2000 database
When I make an ODBC link from MS Access 2003 (.MDB) all data in that
table appears as deleted.

When I use an .ADP project everything works fine.
Any suggestions ?

BTW why can't you use the option Object dependencies in an .ADP file ?

CE

Make sure the BigInt is not the Primary Key and add a TimeStamp field to the
table on the SQL Server.

 

[enders]

Make sure the BigInt is not the Primary Key and add a TimeStamp field to the
table on the SQL Server.

Well it is the primairy key. After adding a timestamp and setting the time
stamp as pk the problem is gone but this is totally not what I am expecting.

Access should work fine with bigints as PK's. I have them all over my database.

Will reversing back to an old version of Access help ?

CE

 

[Rick Brandt]

Well it is the primairy key. After adding a timestamp and setting the
time stamp as pk the problem is gone but this is totally not what I
am expecting.

Access should work fine with bigints as PK's. I have them all over my
database.

Will reversing back to an old version of Access help ?

CE

The problem is that when a DataType doesn't have an exact equivelent in Access
some rounding might occur when the value is resolved. For example if you used a
DateTime on SQL Server these can go all the way to the microsecond on SS but
only resolve to the second in Access. This creates a situation where (to
Access) multiple records have the same value because rounded to the second they
are identical even though the server sees them being different by a fraction of
a second. If such a field is the PK this confusion leads to the #DELETED
situation. I suspect BigInt has the same problem.

These DataTypes when NOT the PK often lead to the "Another user has changed this
record..." errors and adding a Timestamp field usually fixes that.