deleting full table gets "single row update/delete affected more than one row " error

  • Thread starter Thread starter tdr
  • Start date Start date
T

tdr

I'm trying to run delete * from dbo_depend_table and i'm getting
"single row update/delete affected more than one row" why?

I can understand if I had a where clause, but I dont.

system info:
ms access 2000
ms sql server 2000

TIA
 
tdr said:
I'm trying to run delete * from dbo_depend_table and i'm getting
"single row update/delete affected more than one row" why?

I can understand if I had a where clause, but I dont.

system info:
ms access 2000
ms sql server 2000

TIA
Click to expand...

That usually indicates that your server table has no primary key and no
unique indexes. When you created the link you were prompted to designate
which fields could be used by Access to identify unique rows and you
supplied a field (or fields) that are NOT actually unique on the table.

For example if I link to a table with no primary key that contains two
fields (FirstName and LastName) and I tell Access to use those fields to
build a local unique index (required to allow updates on such tables), then
I update the row having "John" and "Smith" in those two fields, I will
actually be applying that update to ALL of the rows on the server where
FirstName has "John" and LastName has "Smith".

It is best to actually have a PK or unique index on all of your server
tables, but if you don't and you tell Access to use one or more fields to
build a unique index on then you MUST correctly specify fields that are
actually unique. Otherwise you run a serious risk of trashing your data.
 
That usually indicates that your server table has no primary key and no
unique indexes. When you created the link you were prompted to designate
which fields could be used by Access to identify unique rows and you
supplied a field (or fields) that are NOT actually unique on the table.

For example if I link to a table with no primary key that contains two
fields (FirstName and LastName) and I tell Access to use those fields to
build a local unique index (required to allow updates on such tables), then
I update the row having "John" and "Smith" in those two fields, I will
actually be applying that update to ALL of the rows on the server where
FirstName has "John" and LastName has "Smith".

It is best to actually have a PK or unique index on all of your server
tables, but if you don't and you tell Access to use one or more fields to
build a unique index on then you MUST correctly specify fields that are
actually unique. Otherwise you run a serious risk of trashing your data.
Click to expand...

thanks for the quick reply.

I tried to select columns when I linked the table, but that did not
work.
I select group, plan, start date, end date, first name , and last
name.
got the same error.

there really is not a unique record for this table, I'm bulk loading a
set of records that could contain some dups.
(which are ok the system that we are loading into will handle them ,
via it's own update)
 
thanks for the quick reply.

I tried to select columns when I linked the table, but that did not
work.
I select group, plan, start date, end date, first name , and last
name.
got the same error.

there really is not a unique record for this table, I'm bulk loading a
set of records that could contain some dups.
(which are ok the system that we are loading into will handle them ,
via it's own update)- Hide quoted text -

- Show quoted text -
Click to expand...

I think i found the problem,
the Sql server did not have a index , I thought the table on the
server had one.
 
Hi,

I got a form working well when not connecting to SQL.
After I import and link tables to SQL , my form does not work any more.

The form I used is connected to a table, which has 1 index value.
when importing that table to Access, I set the index to be PK.
Whenever I add a new record to a textbox in the form, it just copy the previous record and dont keep my new record in. Even though I did use "Allow addition, Allow edit, Allow delete...

If I refresh the ODBC link, the PK of table is disappeared.
The form does not show any textbox to enter data.
I just can't input data in the form.

Any idea of how I can fix this problem?


After
 
Back
Top