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.
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.
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.
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.
Rico (m...@you.com) 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, esq...@sommarskog.se
"Rico" <m...@you.com> 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.
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.
> "Rico" <m...@you.com> 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.
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).
Rico (m...@you.com) 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, esq...@sommarskog.se
