Don't add a record if exists(via VBA)

[peskywinnets]

Bit of a mental block with this one, I'm parsing some XML retrieved from the internet, I want to open a table & write data to the table but only if the data is not already in the table.

I guess what I seek is the equivalent of an append query with 'no duplicates allowed'

What would be the high level approach here?

Many thanks.

 

[jdraw]

?? Not sure I understand, but the most general approaches I can think of are:

a)Put a unique index (no duplicates) on the field you're concerned with. The database will complain about the attempt to store a duplicate -- or you can trap the error 3022,

b) do a Dcount on the field involved before attempting to update the table. If Dcount > 0 then a record with that value exists.

Good luck.

 

[stopher]

Just set your key field in your table to no duplicates allowed. Then you can append all you like, the duplicates won't get added.

 

[peskywinnets]

Thanks chaps, I didn't explain myself very well, imagine a simple table (it's a bit more complicated than this, but the principle remains the same)..


Name Age
Peter 29
Tom 43
Mary 36

Ok so now I go & retrieve some xml from the web & parse it within Access, I end up with lines of new data 'in memory' that I want to write to a table, lets say the parsed xml is this...

Fred 63
Tom 43
Jane 38


I would want Tom & his age to be rejected, but the other two names appended to the original table, to ultimately look like this...

Name Age
Peter 29
Tom 43
Mary 36
Fred 63
Jane 38

I'll look at both the methods (I'm trying to work out if the 'no duplicates' is set for name, it would be rejected, but the age wouldn't ....that not the outcome I seek!)

 

[stopher]

You can create an index (no duplicates) on multiple fields.

But I don't understand your logic. You final table includes Mary 36 twice and Peter 29 twice. And what happened to Fred?

 

[peskywinnets]

But I don't understand your logic. You final table includes Mary 36 twice and Peter 29 twice. And what happened to Fred?

That was a mistake (sorry).....my earlier post has now been corrected.

I'm still not explaining..I actually want to reject the whole row of XML from being added (the new XML data is all 'tied' together so in the above example, Iwant Tom to be rejected only if the XML is Tom and 43 arriving from the XML parsed data (as opposed to reject and Tom or 43, which is what I think would happen with your no duplicates method?)

 

[stopher]

Create an index in your table based on two fields - Name & Age and set no duplicates.

 

[peskywinnets]

Create an index in your table based on two fields - Name & Age and set no duplicates.

But then if someone else had the age of 43 (just like like tom) for example Mike 43 , then that age data wouldn't be added ...that's not what I want.

 

[jdraw]

Do you understand No duplicates allowed?
Whatever field(s) makes a record unique is the field(s) you must check.
If you find a duplicate, you don't add the record.

 

[stopher]

But then if someone else had the age of 43 (just like like tom) for example Mike 43 , then that age data wouldn't be added ...that's not what I want.

Why do you think that? Sure if you create two separate indexes - 1 for name and one for age then what you say above will happen. But if you create a single index based on both fields then both field would have t match for them to be rejected.

 

[peskywinnets]

Guys...thanks for your patience & contributions...I clearly need to go & have a dabble...

 

[peskywinnets]

I sort of have my append VBA code now working as required, but one thing defeats me (not sure whether a new thread would be more appropriate).

I'm using this code (slimmed down for ease of reading)...

dim OrderFullyShipped as Boolean
OrderFullyShipped = true

DoCmd.RunSQL "INSERT INTO OrderDateTime(OrderID,OrderDateTime,OrderFullyShipped)VALUES('" & OrderID & "','" & OrderDateTime & "', '& OrderFullyShipped & ')"

The first two values (OrderID and OrderDateTime) are being inserted just fine into my table (the table is also called OrderDateTime), but the last value (OrderFullyShipped with the boolean value of true) is not being inserted into my table?

if i change the final value of the insert into command to TRUE (vs the contents of a variable), like this...

      DoCmd.RunSQL "INSERT INTO OrderDateTime(OrderID,OrderDateTime,OrderFullyShipped)VALUES('" & OrderID & "','" & OrderDateTime & "', TRUE)"

it works, so it looks like it's probably a syntax problem :-(

Any ideas?