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.55411$7a.37458@pd7tw1no...

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.55411$7a.37458@pd7tw1no...

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****@sommarskog.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.googlegr oups.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@edtnps82...

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.googlegr oups.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@edtnps82...

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.com> wrote in message
news:zd*****************@newsread2.news.atl.earthl ink.net...

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

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.googlegr oups.com...

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

[Lyle Fairfield]

I don't know what happens when the Access wizard transfers a boolean
field to MS-SQL. Is it a bit or character field? If it's character all
you would have to do, I beleieve is to index the field uniquely. TTBOMK
bit fields cannot be indexed.
Tables in general do not require an ID field, although I think an ID
field is a great idea for all tables, nor do they need a Primary Key.
They need a unique index (in order to be updateable through Access).

[Erland Sommarskog]

Rico ([e-mail]) writes:

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.

An ID field is not required. What is required is a primary key. And
that's a fine difference there. A primary key does not have to be an
ID, it could be license-plate numbers to take one (dubious) example.
Most of all, it could be a composite key. For instance in an OrderDetails
table the key would be (OrderID, RowNo) or (OrderID, ProductID), but
not (OrderDetailID).

Without a key, it's not possible to determine which row that is to be
updated.

Yes, in a one-row table it is possible, but apparently no one thought
special case be worth covering. Particularly since most tables start out
empty, and then pass through a phase as one-row tables. Would be
confusing if the table got read-only because you added a second row.

Easiest is to add a primary-key to the one row table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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