Problem with attached SQL Server tables in Access database

B

Ben Webber

Hi,

Directly in the SQL Server database, I can delete rows from a SQL server
table.

However, when that same table is attached to Access, it tells me the data is
read-only and I can't delete data anymore.

I CAN delete even from inside Access XP from other tables in the same SQL
server database.

Any idea what I might check to make this table no longer read-only?

Thanks in advance,
 
T

Tom Wickerath

Hi Ben,

A linked table or view will be read only if it does not have a primary key
or unique index defined:

Remote ODBC Tables Are Read-Only Without a Unique Index
http://support.microsoft.com/?id=209807

SQL Server Views Are Read-Only When Linked
http://support.microsoft.com/?id=209569

Is your table missing a primary key? If you need to add it, you might also
want to make sure that your table has a timestamp field. While this won't
help you with your current problem, it will speed up operations because
Access can use the timestamp field to determine if any other field has been
edited. A timestamp field is a special type of field within SQL Server, which
is basically a GUID. It is not a timestamp in the traditional sense. Here is
an article that includes some information on the use of Timestamps:

Optimizing for Client/Server Performance
http://support.microsoft.com/?id=208858

*Important*
You should drop the link and re-establish it (especially if you add or
remove any fields, but certainly to help troubleshoot the immediate problem).
Don't just refresh it, because Access caches information about the link that
doesn't get updated properly via a refresh.


Tom
________________________________________

:

Hi,

Directly in the SQL Server database, I can delete rows from a SQL server
table.

However, when that same table is attached to Access, it tells me the data is
read-only and I can't delete data anymore.

I CAN delete even from inside Access XP from other tables in the same SQL
server database.

Any idea what I might check to make this table no longer read-only?

Thanks in advance,
 
B

Ben Webber

Adding a primary key to the table on the SQL Server side appears to have
fixed the problem.

Thanks!

(I don't know why it works but knowing it does is useful)
 
6

'69 Camaro

Hi, Ben.

The reason it works is because Access needs a method of uniquely identifying
which records to update or delete. If Access can't identify each specific
record, Access will complain that the linked table is read-only when you try
to make changes. A primary key on the linked table will allow Access to
determine precisely which records to update or delete.

I highly recommend adding a primary key when designing a table, even if you
think you don't need one. Some day someone probably will need it, as in this
example. (Temporary tables may or may not need a primary key -- it depends
upon the situation.)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.
 
Top