Strange issue with SQL Table read only in Access

#11

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).

#12

Rico (me (AT) you (DOT) com) writes:

Quote:

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, esquel (AT) sommarskog (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Strange issue with SQL Table read only in Access

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Strange issue with SQL Table read only in Access - 04-22-2006 , 01:17 PM

Re: Strange issue with SQL Table read only in Access - 04-22-2006 , 01:23 PM