SQL linked tables in Access FE occasionally go read only

[Bobby]

Hi,
Not sure if this is Access, SQL or ODBC. I have a SQL database with an
Access Front End. They are linked with ODBC. Occasionally (it's
happened 3 times in 4 months) some of the linked tables in Access
become read only and I can't add new records to them. If I go into
design view (in Access) the tables have lost their Primary Key. In
SQL, they still work just fine. The only way I have found to get the
links working again is to delete the link and recreate it.

This doesn't happen on all of the tables that are linked, and I
couldn't even say that its all of the tables with Primary Keys.
However, it does seem to be the same few tables each time. It happened
this morning on a Test version of my database, which only ever has one
user. Still the same tables.

Can anybody offer any suggestions?

Thanks a lot

Colin

[Rick Brandt]

Bobby wrote:

Hi,
Not sure if this is Access, SQL or ODBC. I have a SQL database with an
Access Front End. They are linked with ODBC. Occasionally (it's
happened 3 times in 4 months) some of the linked tables in Access
become read only and I can't add new records to them. If I go into
design view (in Access) the tables have lost their Primary Key. In
SQL, they still work just fine. The only way I have found to get the
links working again is to delete the link and recreate it.

This doesn't happen on all of the tables that are linked, and I
couldn't even say that its all of the tables with Primary Keys.
However, it does seem to be the same few tables each time. It happened
this morning on a Test version of my database, which only ever has one
user. Still the same tables.

Can anybody offer any suggestions?

Thanks a lot

Colin

Those tables do not have primary keys (or a unique index) on the SQL Server.
When creating a link to such a table Access will prompt you for the fields to
use so it can build a local index. If you skip that step the link will be read
only. If you DO select the appropriate fields then the link will be editable.

Now, if you later refresh that link, either in code or with the linked table
manager that information for the local index is discarded and the link reverts
to read only. The best solution of course is to add a PK to those tables on the
SQL Server.

Don't be confused by the "key icon" you see if you look at a link in design
view. That "might" be the PK defined on the server or it migth just be the
fields that were selected when the link was created.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Bobby]

>

Those tables do not have primary keys (or a unique index) on the SQL Server.
When creating a link to such a table Access will prompt you for the fields to
use so it can build a local index. If you skip that step the link will be read
only. If you DO select the appropriate fields then the link will be editable.

Now, if you later refresh that link, either in code or with the linked table
manager that information for the local index is discarded and the link reverts
to read only. The best solution of course is to add a PK to those tables on the
SQL Server.

Don't be confused by the "key icon" you see if you look at a link in design
view. That "might" be the PK defined on the server or it migth just be the
fields that were selected when the link was created.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -

Once again thanks a lot Rick. The tables that fail do not have Primary
Keys in SQL as you suggested. However, when I try to assign them a PK,
I get the following error:

'Cutting List' table
- Unable to create index 'PK_Cutting List'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
already an object named 'PK_Cutting List' in the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.

Cutting List is the name of the table.

The reason for the above error is as follows: I also have a table
called Cutting List1 with a Primary Key which was created before we
went live. I suspect that what has happened is that Cutting list1 was
originally called Cutting List but then was renamed. Cutting List was
then created wihout a Primary Key. It appears that PK_Cutting List is
actually the primary key of Cutting list1. I have proven this by
deleating the Primary Key from Cutting list1. I am now able to create
a Primary key for Cutting List.

Phew! Thanks again

Colin