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

vba - access - ignore duplicate values

P: 2
Hi board,

I'm using excel vba to drive my access database. Now I encountered the following problem:

I have an update qry to update a table which has an index field. Because of this, I always get the error message that it won't let me update the table. I'm aware of the duplicates, but if I run the update query manually, I can ignore the error and it will only update my table with the first row and just drops the duplciates. This is exactly what I want it to do.

Now in vba, the code just blocks. Can someone let me know how I can ignore the message via a vba statement?

This is the part of the code were it goes wrong

Expand|Select|Wrap|Line Numbers
  1. sql = "SELECT PPVinPL.ItemNumber, PPVinPL.Supplier, PPVinPL.Currency" & _
  2.       " FROM PPVinPL" & _
  3.       " GROUP BY PPVinPL.ItemNumber, PPVinPL.Supplier, PPVinPL.Currency" & _
  4.       " HAVING(((PPVinPL.Supplier) Is Not Null))" & _
  5.       " ORDER BY PPVinPL.ItemNumber, Sum(PPVinPL.SumConsumedQty) DESC;"
  6. ' here the command blocks because of duplicate values
  7. rs1.Open sql, conn, adOpenDynamic, adLockOptimistic
can anyone help me with this code?
thansk a lot
shodan
Oct 6 '08 #1
Share this Question
Share on Google+
4 Replies


nico5038
Expert 2.5K+
P: 3,072
Hmm, I don't see an INSERT INTO, just a SELECT query that won't add any data to the database.

I would use a:
Expand|Select|Wrap|Line Numbers
  1. currentdb.execute ("INSERT INTO tblX (ValueA, ValueB) VALUES (123, 'abc')")
  2.  
This won't raise an error and continue.
By using an autonumber as the prime key and by not inclusing the prime key in the INSERT, the error won't be raised.

Nic;o)
Oct 6 '08 #2

P: 2
Nico,

Sorry for the select qry. I was trying several things and therefore I copied a modified code. This is the code I was using:

Expand|Select|Wrap|Line Numbers
  1. sql = "INSERT INTO Supplier_Link ( ItemNumber, Supplier, [Currency] )" & _
  2.       " SELECT PPVinPL.ItemNumber, PPVinPL.Supplier, PPVinPL.Currency" & _
  3.       " FROM PPVinPL" & _
  4.       " GROUP BY PPVinPL.ItemNumber, PPVinPL.Supplier, PPVinPL.Currency" & _
  5.       " HAVING (((PPVinPL.Supplier) Is Not Null))" & _
  6.       " ORDER BY PPVinPL.ItemNumber, Sum(PPVinPL.SumConsumedQty) DESC;"
  7.  
  8. ' here the command blocks because of duplicate values
  9. rs1.Open sql, conn, adOpenDynamic, adLockOptimistic
  10.  
What you are advising is using the autonumber (right??)but that I don't want to do, since I will still have duplicates. The thing is that in this list, I might have an item that is returned twice since there are multiple suppliers linked to it. I am aware of that, and I only want to keep the first item. therefore I indexed the item field. In case I do an upload using the transferspreadsheet method, I don't encouter any problems. access just load the one item and ignore the rest. I want to achieve the same thing with the update qry, by ignoring the error for the duplicate values. If I do it manually, I'm able to choose to ignore it, so I guess it must be possible with vba too?

Any other suggestion is also fine, but please be aware that I'm a real novice!
Oct 7 '08 #3

nico5038
Expert 2.5K+
P: 3,072
Using a table with an autonumber (that's of course not included in the INSERT), will allow you to add all rows and take action afterwards.

Did you check the "currentdb.execute" command ?

When running the VBA from excel you'll first have to open the database and issue the execute on the opened database object.

You'll have to consider the fact that by ignoring dupes you might also miss information and changes afterwards won't be updated.
Sample rows:
ItemID Description
1 Old description
1 New description
Will result in one of the values. Even when the description isn't filled the first time (run), it won't get filled afterwards.

Nic;o)
Oct 7 '08 #4

DonRayner
Expert 100+
P: 489
Nico,

Sorry for the select qry. I was trying several things and therefore I copied a modified code. This is the code I was using:

Expand|Select|Wrap|Line Numbers
  1. sql = "INSERT INTO Supplier_Link ( ItemNumber, Supplier, [Currency] )" & _
  2.       " SELECT PPVinPL.ItemNumber, PPVinPL.Supplier, PPVinPL.Currency" & _
  3.       " FROM PPVinPL" & _
  4.       " GROUP BY PPVinPL.ItemNumber, PPVinPL.Supplier, PPVinPL.Currency" & _
  5.       " HAVING (((PPVinPL.Supplier) Is Not Null))" & _
  6.       " ORDER BY PPVinPL.ItemNumber, Sum(PPVinPL.SumConsumedQty) DESC;"
  7.  
  8. ' here the command blocks because of duplicate values
  9. rs1.Open sql, conn, adOpenDynamic, adLockOptimistic
  10.  
What you are advising is using the autonumber (right??)but that I don't want to do, since I will still have duplicates. The thing is that in this list, I might have an item that is returned twice since there are multiple suppliers linked to it. I am aware of that, and I only want to keep the first item. therefore I indexed the item field. In case I do an upload using the transferspreadsheet method, I don't encouter any problems. access just load the one item and ignore the rest. I want to achieve the same thing with the update qry, by ignoring the error for the duplicate values. If I do it manually, I'm able to choose to ignore it, so I guess it must be possible with vba too?

Any other suggestion is also fine, but please be aware that I'm a real novice!
Try this instead

Expand|Select|Wrap|Line Numbers
  1. sql = "INSERT INTO Supplier_Link ( ItemNumber, Supplier, [Currency] )" & _ 
  2.       " SELECT first(PPVinPL.ItemNumber) as ItemNumber, PPVinPL.Supplier, PPVinPL.Currency" & _ 
  3.       " FROM PPVinPL" & _ 
  4.       " GROUP BY PPVinPL.ItemNumber, PPVinPL.Supplier, PPVinPL.Currency" & _ 
  5.       " HAVING (((PPVinPL.Supplier) Is Not Null))" & _ 
  6.       " ORDER BY PPVinPL.ItemNumber, Sum(PPVinPL.SumConsumedQty) DESC;" 
  7.  
Oct 7 '08 #5

Post your reply

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