By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Delete Query

P: 12
I'm uploading data from a table that I need to refresh from time to time. I need to basically delete all of the data & then I repopulate again with fresh data.

I can't seem to get the query to work using either of these query options:

Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM CPL_A3_BASIC_MAT_VIEW;
  2. DELETE * FROM CPL_A3_BASIC_MAT_VIEW WHERE CPL_A3_BASIC_MAT_VIEW.[Material Type]="HIBE";
The error message I receive is Single-row update/delete affected more than one row of a linked table. Unique index contains duplicate values.
The table itself resides on another machine cennected via OBDC. I can update with ease, just can't seem to delete???
Sep 4 '07 #1
Share this Question
Share on Google+
3 Replies


P: 12
**BUMP*** anyone have any ideas what the error message above means??

Cheers, Stevo
Sep 5 '07 #2

Scott Price
Expert 100+
P: 1,384
**BUMP*** anyone have any ideas what the error message above means??

Cheers, Stevo
Two things to try:

Have a look-see on how you're linking to the fields in the backend. For instance if you are opening the table through the .OpenRecordSet method there is a setting called dpAppendOnly that will allow additions, but no deletions. If you have all permissions, try changing the syntax of your delete query:
Expand|Select|Wrap|Line Numbers
  1. DELETE [CPL_A3_BASIC_MAT_VIEW].* FROM [CPL_A3_BASIC_MAT_VIEW] WHERE [CPL_A3_BASIC_MAT_VIEW].[Material Type]="HIBE";
Regards,
Scott
Sep 5 '07 #3

P: 1
When I hit this error message the problem turned out to be exactly as described in the error message: Single-row update/delete affected more than one row of a linked table. Unique index contains duplicate values.

This was because of a mismatch between the underlying definition of the table in MySQL and the (out of date) definition in MS Access. What Access thought was the 'unique index' was not, in fact.

The solution was to relink the table in Access using the Linked Table Manager.

Hope this helps someone!
Dec 30 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.