Append query versus error handling

[June7]

Yes, I suppose DLookup would be slower and if there were hundreds of DLookups in sequence, I would rethink. But I do use them when just need to retrieve a single value instead of opening a recordset object. Don't notice performance issue. So if you do run INSERT action and let Access deal with rejecting duplicate, use CurrentDb.Execute in VBA and there won't even be a warning message.

[Remster]

Got it. And for completeness, what are your thoughts about relying on error handling?

(Edit: That question is for Dave.)

[Remster]

So if you do run INSERT action and let Access deal with rejecting duplicate, use CurrentDb.Execute in VBA and there won't even be a warning message.

Great, thanks for the tip.

[davegri]

And for completeness, what are your thoughts about relying on error handling?

Completeness is up to you in error handling, depending on your interpretation of the severity of the error and how it is to be handled.

[isladogs]

I'm a bit late to the party but
1. DLookups are many times slower ….
2. Depending on an error as a method for running a process is bad practice. Sometimes its unavoidable but if a better method exists, avoid it.
3. I would use an append unmatched query which only appends records that don't exist. That will be fast.

But with all questions like this, I suggest timing the different approaches and then you'll know the answer for yourself!
Suggest you read this article on optimising queries http://www.mendipdatasystems.co.uk/s...s-8/4594556613
You are welcome to adapt the utility for your own tests

[Remster]

Thanks for this. Just for clarification, is an "append unmatched query" a distinct thing, or do you just mean to use a subquery to determine whether the value is matched or unmatched?

[isladogs]

Its a single query.
Create an unmatched select query using a left join and filter the records to only be shown where they don't exist in the destination table.
Then change it to an append query. For example

If the ID field is an auto number it would not be included as a field to append in the query.

[Remster]

Blimey, that's so obvious that if I didn't try it first, I need to boil my own head.

[isladogs]

No need to go quite that far.
Another time I'll tell you all about the UPEND query which is an APPEND and UPDATE in one query....

[Remster]

Can't wait.

I'll have to see if I can work out the Append query when I'm next at work (Friday), as in this case I need to compare a control in a form with Table 2, rather than Table 1 with Table 2. Brain is working slowly tonight.

[June7]

AKA UPSERT because the keyword is actually INSERT for an Append query.

[isladogs]

Two more links you may find useful.
Firstly a thread from earlier today - see post 5 in https://www.accessforums.net/showthread.php?t=75922
Also a lengthy article on synchronising data from my website http://www.mendipdatasystems.co.uk/s...a-1/4594514001

HTH

[Remster]

isladogs, I'm struggling a bit to apply your 8.59 pm suggestion to my situation.

At the moment I'm enforcing referential integrity, which obviously means there will never be a value in Table1.ID that isn't already in Table2.ID. I've been thinking in terms of appending records to Table2 in the form control's Before Update event.

Is your suggestion that I remove referential integrity and append records to Table2 in the form control's After Update event?

[isladogs]

Going back to post #1, you asked which was the best method:

1. An Append query that runs every time data is entered in the field, but skips over values that are already in the "rowsource".
2. An Append query that runs depending on the results of a Select query that checks whether the value is already in the "rowsource".
3. An Append query that runs in response to the data error that occurs when a value isn't in the "rowsource".

All of those clearly indicate there are records in one that aren't in the other....otherwise what is the point of the thread?

However using the idea I showed in post #22, you don't need to link by the PK field if that's not helpful
That's why I gave you two additional links in post #27.

If neither of those links lead to a solution, it would help to see a screenshot of both your relationship between the 2 tables with RI & your append query.
At the moment all any of us can do is offer general advice that may or may not apply in your situation

[Remster]

[T]here are records in one that aren't in the other....otherwise what is the point of the thread?

No, there are values in the form field (which is bound to Table1.ID) that aren't already in Table2.ID, so Table1.ID can't update until the value is in Table2.ID. I'll be in the office later, so I'll take some screenshots then. I may have just designed this badly.