Problem deleting records from SQL table using Access

[hardrock]

Hi Guys,

i am having a right game trying to delete some records from a simple table on my SQL server.

Say for instance the table dbo_Resource contains these records, where tasknumber 4 has 3 persons against it e.g.


tasknumber |person
4 | paul
4 | andy
4 | steve

and tasknumber is the primary key (because Ms Access makes you set a primary key with a linked table from SQL server)

When i run the code below and combo0 = 4

' Delete selected record
SQ = "DELETE * FROM dbo_Resource WHERE tasknumber = " & Me.Combo0 & ";"

'Execute the SQL statement
DoCmd.RunSQL (SQ)


I get a run time error message consisting of:

3362 Single-row update/delete affected more than one row of an attached (linked) table. Unique index contains duplicate values.

Any ideas?

PS// if i use an internal table in access it works with no error message! but the table must go on the SQL server because multiple users will use the app.

Thanks for any advice.

 

[boblarson]

Telling Access that the field is the primary key when it isn't, isn't going to help you. You need to put a REAL PK field in the SQL table and let Access know that is what is the PK. Then you can delete where TaskNumber = whatever.

 

[vbaInet]

SQ = "DELETE FROM dbo_Resource WHERE tasknumber = " & Combo0 & ";"
CurrentDb.Execute SQ, dbFailOnError

Try that

 

[boblarson]

SQ = "DELETE FROM dbo_Resource WHERE tasknumber = " & Combo0 & ";"
CurrentDb.Execute SQ, dbFailOnError

Try that

Won't work as long as Access thinks that TaskNumber is the PK when it isn't.

 

[vbaInet]

Won't work as long as Access thinks that TaskNumber is the PK when it isn't.

I thought it was something to do with his syntax in the Delete query, using asterisk?

 

[boblarson]

I thought it was something to do with his syntax in the Delete query, using asterisk?

Nope, this part is the problem:

and tasknumber is the primary key (because Ms Access makes you set a primary key with a linked table from SQL server)

 

[vbaInet]

Nope, this part is the problem:

Ah, I should have read that bit. Thanks boblarson

 

[hardrock]

Thankyou guys, finally got round to testing it and all sorted now

 

[vbaInet]

Thankyou guys, finally got round to testing it and all sorted now

Great job.