Access 97 Frontend - SQL Server 2005 Backend Linked Table Error

See more:

SQL

MS-Access

ODBC

Hi,

I have a legacy Access 97 Frontend application which utilises a SQL Server 2005 backend over a SQL Server ODBC Driver (Connection), we use the Linked Table feature on this setup.

I create, amend and link in tables on a daily basis and I am aware of the conversions that occur between the different data types.

There seems to be an issue with one table that I recently created, it has exactly the same setup and permissions as many of the other tables in the database but once I link it into Access 97 it seems to show #NAME in all columns and I also receive an 'ODBC Call Failed' error.

If I remove the Primary Key from the table and do not select a 'Unique Record Identifier' then I am able to view the data in the table but I obviously can't edit it.

There are 3 columns which are VARCHAR's and are over 255, if I reduce these columns to 255 or less I am then able to view the data in the table but if I then try to edit or delete the data I receive a new error 'The Microsoft Jet Database engine stopped the process because you and another user are attempting to change the same data at the same time' - I know this is not possible because at present I am the only one with access to the table.

In this particular table there are 146 columns, if I delete half of these then the table starts to work as it should, again I have tables that have far more columns than this and work perfectly.

Any suggestions on why this isn't working?

Thanks,