Query Running Show progress 1

[jeffwest2]

I have a an Access 2007 program that imports in some data, then runs a query to check for duplicates and removes the new records where duplicates exists, the issue is this runs really slowly.

I have tried to change this to VBA SQL, but it runs just as slowly, so, my next idea was to try and create on a form a text box that is showing which record it is currently working on to show the user that it is actually doing something, my issue here is that I cannot seem to work out a way of returning to the text box the record being worked on from the query.

My other thought was to run a query or VBA sql that first counts the number of records to be deleted, then runs the delete query against those records and so a real time progress meter could show
A) the count taking place
B) The deletion taking place with the count as 100%.
or C) Show the record being deleted as above

but here I could only seem to create a progress bar with a pre-determined maximum.

I realise that with access you can't havit it doing the query and running the progress meter, which is why I guess I am having so many issues with this, but anything that will either point me in the rightb direction or give me something to stop me banging my head against a brick wall would be very helpful.

Any help with this would be appreciated

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[Andrzejek]

program that imports in some data, then runs a query to check for duplicates and removes the new records where duplicates exists, the issue is this [removing part?] runs really slowly."

I would take another look at the import logic.
If you need to remove duplicates, why not check for duplicates while importing the data?

If record does not exist in your table, insert it.
If it is already there, skip it, go to the next record.


Have fun.

---- Andy

 

[jeffwest2]

Andy

Thanks for the logic check, think I was trying to do too much with a program thathas got a bit out of control, rethinking it has made it run quicker, thanks for that.



Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[vbajock]

You also might want to look at your primary key logic, your append query can ignore duplicates if you have your primary keys designed correctly.

 

[jeffwest2]

Primary keys are fine, I have a customer controlled Id that I need to be matching against, so PK's aren't too much of a problem, but thanks anyway. New version went live yesterday and seems to work fine.

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.