Access "lost" updatable connection

[Jeff Boyce]

Access 2002, fully service-packed, with linked SQL-Server 2000 tables.

Somewhere between Wednesday morning (everything worked as expected) and
Thursday, sometime, the front-end Access .mdb file "forgot" that a couple of
the SQL-Server tables were supposed to be updatable. Refreshing the link to
those did not make a difference -- the tables were not updatable (no adds,
no edits).

Adding a new link to the table, and ensuring that the Primary Key field was
identified, creates an undatable connection to the SQL-Server table.

But nothing was changed in the front-end?! Any ideas how this could have
happened?

(and just in case you wondered, the same problem cropped up in multiple
front-ends, on multiple PCs. The only thing those separate, independent
installations have in common is the back-end.)

Thanks for any ideas/leads

Jeff Boyce

 

[Jerry Whittle]

Hi Jeff,

If it's not the FE, then it must be the BE or something in between them. I'd
check if there have been any changes to security or permission settings on
the SQL Server database or the network where it resides. I'd also check if MS
did any recent updates to that server or even your PCs. The automatic updates
ususally happen on Tuesday evenings.

 

[mscertified]

My guess would be that someone logged off their PC without exiting the
database.

-Dorian

 

[Jeff Boyce]

I'm not sure I understand how that would cause the SQL-server link in MY PC
to go bad?

Thanks.

Jeff

 

[Jeff Boyce]

I came to pretty much the same conclusions. The network folks and the SQL
DBAs are quite certain they haven't done anything...

Isn't confidence admirable?

Thanks for the leads.

Jeff

 

[Rick Brandt]

Access 2002, fully service-packed, with linked SQL-Server 2000 tables.

Somewhere between Wednesday morning (everything worked as expected)
and Thursday, sometime, the front-end Access .mdb file "forgot" that
a couple of the SQL-Server tables were supposed to be updatable. Refreshing
the link to those did not make a difference -- the tables
were not updatable (no adds, no edits).

Adding a new link to the table, and ensuring that the Primary Key
field was identified, creates an undatable connection to the
SQL-Server table.
But nothing was changed in the front-end?! Any ideas how this could
have happened?

(and just in case you wondered, the same problem cropped up in
multiple front-ends, on multiple PCs. The only thing those separate,
independent installations have in common is the back-end.)

Thanks for any ideas/leads

Jeff Boyce

If you link to a table with no Primary Key or unique index then you are prompted
to identify the fields that Access should use for a local index. That make the
link editable. If later you refresh the link that local index is lost and the
link reverts to read only. You would have to delete the link and rebuild it
from scratch at which point you will again be prompted so a local index can be
built.

The long-term fix is to out a PK on the table so that a local index is not
required.

 

[Jeff Boyce]

Thanks, Rick. I'll rummage (at least as far as the SQL DBAs will allow me)
to see what I can do on those tables.

Jeff