Strange issue with SQL Table read only in Access

[Rico]

I have a data file that I converted from Access to SQL Server using the
upsizing wizard. I then attached the tables to the Access front end using an
ODBC connection. I have one table that is read only for some reason. I'm
not sure how that happened, it went through the same procedures as the other
tables in terms of upsizing and connecting. The original table was not read
only or hidden or anything else, it had full permissions.

I've tried disconnecting and reconnecting the table but still the same
thing. In SQL Server, the permissions are the same as all the other tables.
I can't seem to figure out what's making this one read only.

I'm using SQL Server 2005 Express and Access 2003. Any ideas would be
greatly appreciated.

Thanks!

[Anthony England]

"Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN
CAPS> wrote in message news:zA72g.5541 1$7a.37458@pd7t w1no...

I have a data file that I converted from Access to SQL Server using the
upsizing wizard. I then attached the tables to the Access front end using
an ODBC connection. I have one table that is read only for some reason.
I'm not sure how that happened, it went through the same procedures as the
other tables in terms of upsizing and connecting. The original table was
not read only or hidden or anything else, it had full permissions.

I've tried disconnecting and reconnecting the table but still the same
thing. In SQL Server, the permissions are the same as all the other
tables. I can't seem to figure out what's making this one read only.

I'm using SQL Server 2005 Express and Access 2003. Any ideas would be
greatly appreciated.

Thanks!

Does the table have a primary key? Tables linked to SQL Server need a
primary key to be updateable.

[Lyle Fairfield]

Does the table have a primary or unique index?
I don't use ODBC as I my hands are arthritic and I can't hold a chisel
and a hammer (for the stone tablets) any more but I think MS-SQL
requires a table to have a primary or unique index in order to be
updateable.

[Rico]

That would be it. I learn something new every day. ;)

Thanks guys!

Just out of curiosity, is there any reason why that is? I wound up
installing a work around for the time being in the form of a stored
procedure, but is there any resource that provides an explanation of that
reasoning?
"Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN
CAPS> wrote in message news:zA72g.5541 1$7a.37458@pd7t w1no...

I have a data file that I converted from Access to SQL Server using the
upsizing wizard. I then attached the tables to the Access front end using
an ODBC connection. I have one table that is read only for some reason.
I'm not sure how that happened, it went through the same procedures as the
other tables in terms of upsizing and connecting. The original table was
not read only or hidden or anything else, it had full permissions.

I've tried disconnecting and reconnecting the table but still the same
thing. In SQL Server, the permissions are the same as all the other
tables. I can't seem to figure out what's making this one read only.

I'm using SQL Server 2005 Express and Access 2003. Any ideas would be
greatly appreciated.

Thanks!

[Lyle Fairfield]

How would you find the house if you didn't know the address?

[Erland Sommarskog]

Rico ([e-mail]) writes:

That would be it. I learn something new every day. ;)

Thanks guys!

Just out of curiosity, is there any reason why that is? I wound up
installing a work around for the time being in the form of a stored
procedure, but is there any resource that provides an explanation of that
reasoning?

I don't know Access and what these "linked tables" are all about. But
I assume that you get to see the table data in some grid in Access, and
you can change data in it, and write that change back to the database.
The problem is then to locate that row in the database.

And the way to locate data in an relational database is through primary
keys, that is the data itself. So if there is no primary key, there is
no way to know which row you updated. To avoid disasters, Access is
smart enough to prevent you from even trying.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Rico]

Just because I don't know the address, doesn't mean the people that own it
can sell it (UPDATE People Set Owner=NewOwner) .

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ e56g2000cwe.goo glegroups.com.. .

How would you find the house if you didn't know the address?

[Greg D. Moore \(Strider\)]

"Rico" <[e-mail]> wrote in message news:f2s2g.5832 $Fd6.4310@edtnp s82...

Just because I don't know the address, doesn't mean the people that own it
can sell it (UPDATE People Set Owner=NewOwner) .
Congratulations , you've just updated every record in your table to the new
owner.

In other words, you've just sold every house on the block to the same
person.

You need a where clause and in order to pick a SPECIFIC house, you need
something that ID's it uniquely.


"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ e56g2000cwe.goo glegroups.com.. .

How would you find the house if you didn't know the address?

[rkc]

Greg D. Moore (Strider) wrote:

"Rico" <[e-mail]> wrote in message news:f2s2g.5832 $Fd6.4310@edtnp s82...

Just because I don't know the address, doesn't mean the people that own it
can sell it (UPDATE People Set Owner=NewOwner) .

Congratulations , you've just updated every record in your table to the new
owner.

Must have been Donald Trump.

[Rico]

Yea, I know what you're saying (and that should have been UPDATE HOUSE). My
comment wasn't on the practicality of identifying records, but on the
updatability of the table (since the table is not updatable with no ID). In
a perfect world, there would be no bad design, but I have the uneviable task
of converting a poorly designed Access FE to use an SQL Server back end.
The table in question has a single record in it, and was never intended to
have anything more than a single record in it. There is no ID field, just a
field that is updated to either 'Yes' or 'No'. If I had the budget I would
rewrite the whole program, which is very intensly complicated in terms of
the information, calculations etc (it's forestry related), so it would be a
greater task to take this crappy design and rebuild it properly. FWIW I
always use an ID field, that's how I was taught. I would just like to know
if there is reasoning that the table is not updatable without an ID field.


"Greg D. Moore (Strider)" <mo************ ****@greenms.co m> wrote in message
news:zd******** *********@newsr ead2.news.atl.e arthlink.net...

"Rico" <[e-mail]> wrote in message news:f2s2g.5832 $Fd6.4310@edtnp s82...

Just because I don't know the address, doesn't mean the people that own
it
can sell it (UPDATE People Set Owner=NewOwner) .

Congratulations , you've just updated every record in your table to the new
owner.

In other words, you've just sold every house on the block to the same
person.

You need a where clause and in order to pick a SPECIFIC house, you need
something that ID's it uniquely.

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ e56g2000cwe.goo glegroups.com.. .

> How would you find the house if you didn't know the address?
>