Update Query with many different criterias question

[silentwolf]

Hi guys,

I am trying to update several fields in a access table but have about 50 different criterias and am not sure how to set it up correctly.

Following situation.. I need to look in a field and find a part of a string within the string then update this and other fields according to the criteria.

At present I am realising it with a update Query like so

Sub Mueller()
Dim rcsKonto As Recordset

Dim db As Database
Set db = CurrentDb

Set rcsKonto = db.OpenRecordset("SELECT * FROM tbl_CSV WHERE Umsatztext LIKE'*Müller*'", dbOpenDynaset)
Do Until rcsKonto.EOF

rcsKonto.Edit
rcsKonto.Fields("Feld11").Value = "IG Erlöse"
rcsKonto.Fields("Umtext").Value = "Müller"
rcsKonto.Fields("Gegenkonto").Value = "4100"
rcsKonto.Update

rcsKonto.MoveNext
Loop

End Sub

this works but the problem is that I got about 50 or more those kind of statements and when I am trying to run all at once it gets quite slow..

How could I do it better to realise all my search criterias in one go and still have a good speed plus keep it quite clear to get around the code.

Is it better using custom functions to update the fields or a class module or or or...

Hope someone can give me a hint or a direction for me to get it better sortet.

Many thanks for your input!!!


Albert

 

[Micron]

Not 100% sure I follow the process. I think a local query would be faster and less work overall.
If your 50 variables are static, put them in a table and use that table in an update query as the source of the values. You need a relationship between that table and the one you're updating so you can do an equal join. In the query design grid Update To row, you put [tblMyTable].[myField] as the value to be used in the update. What I don't see here is if you are reusing the recordset without closing the it AND destroying the recordset object before doing it again (if not, expect slowness). Or maybe you're just saying after 50 loops it starts slowing down.

 

[silentwolf]

Hi Micron,

thanks for your reply! Unfortunatelly the varibles are not static so the use of a table like you mentioned doesn't work. But I know about it and it is a very fast way!
The issue is that I need to look for parts in a string.
Unfortunately there are many of them....

I can try that but is there a other way of sorting those kind of things out? Or can a lookup table have parts of a string as well?

How would I need to close the recordset after each Loop?

Sub Update()
    Dim rcsKonto As Recordset
    
    Dim db As Database
    Set db = CurrentDb
    
     Set rcsKonto = db.OpenRecordset("SELECT * FROM tbl_CSV WHERE Umsatztext  LIKE '*Müllgrundgebühr Th*'", dbOpenDynaset)
    Do Until rcsKonto.EOF
    
    rcsKonto.Edit
    rcsKonto.Fields("Buchtext").value = "Müllgrundgebühr *"
    rcsKonto.Fields("Umsatztext").value = "Müllgebühr 10%"
    rcsKonto.Fields("Gegenkonto").value = "7398"
    rcsKonto.Update
    
        rcsKonto.MoveNext
    Loop[CODE]

rcsKonto.nothing

Set rcsKonto = db.OpenRecordset("SELECT * FROM tbl_CSV WHERE Umsatztext LIKE '*Müllgrundgebühr A*'", dbOpenDynaset)
Do Until rcsKonto.EOF

rcsKonto.Edit
rcsKonto.Fields("Buchtext").value = "Müllgrundgebühr"
' rcsKonto.Fields("Umsatztext").value = "Müllgebühr 10%"
rcsKonto.Fields("Gegenkonto").value = "7398"
rcsKonto.Update

rcsKonto.MoveNext
Loop
End Sub
[/CODE]

Hope you could give me further assistent for this issue.

Many thanks

 

[Micron]

How would I need to close the recordset after each Loop?

If you're saying you need to do 50 loops because you have 50 searches to do, I misunderstood. Based on what I read, I wondered if you were doing a set of loops, then exiting the loop portion, then going at it again with another set of search terms. In that case, your code is declaring a new instance of the recordset in memory without purging the old one. This can bog your pc down. Even if you are only doing this once, this is what all recordset code should have where you have SET anything or opened a recorset:

rcsKonto.Close
Set rcsKonto = Nothing
Set db = Nothing

This should be written in a way that will do this even if the procedure exits on error.
Now for a bigger issue. I reviewed this post before saving my answer and decided to leave the above in since it could still apply for something you do in the future. The worst thing you are doing here is reusing the same variable for a new recordset without closing or destroying the first one:

Set rcsKonto = db.OpenRecordset("SELECT * FROM tbl_CSV WHERE Umsatztext  LIKE '*Müllgrundgebühr Th...
Set rcsKonto = db.OpenRecordset("SELECT * FROM tbl_CSV WHERE Umsatztext LIKE '*Müllgrundgebühr A...

Better to create a rs for each query and do as I said - close and destroy them when done. After you've fixed that, let's see what happens to the performance. Also, I don't get what this is

LooprcsKonto.nothing

Not important: most times you don't need .value - it is usually the default property of a text box or combo box. The fact that it is not capitalized in your post looks suspicious.

 

[Norie]

Do you have a table with the search terms and update values in records?

If you do you might be able to do this all in one go.

To do that you would need to use wildcards in the join between the two tables.

Now using a wildcard join will slow things down but it if you can do the update in one go then it should be quicker than running multiple individual queries.

PS As for the data not being static that's not a problem, you can just update the table with search terms and update values as and when needed.

 

[silentwolf]

Hi Norie,

thanks for your reply!!Nice to see you on here again its been a long time

I don't have a table like a search table.. unfortunatelly.. the string is always different and there are only parts of the string wich are always the same..

For example
Company A Creditor ID: AT1454512121254 Mandatsnummer:00000222222222224444444 Auftraggeberreferenz 00000111111112121212 REF:362232323-02999333-000555222
Company A Creditor ID: AT1454512121254 Mandatsnummer:00000222222222224444444 Auftraggeberreferenz 00000111111112121215 REF:362232323-02999333-000555244

so there is a difference between the two only the Mandatsnummer and the company are exactly the same

So now I am not sure how I can manage to find all records and update other fields from this information.

I have different companies and different numbers and so on.

I used a lookup table to update all in one go but then it has to match the string exactly in order to run.

How can I do it if there are as above some numbers or string change?

Hope I could make myself clear with this??

Thanks

 

[silentwolf]

Hi Micron,
sorry did not see your reply!
I understand to destroy the recordset when closing! I will look at it!
To run one query after the other it seams a quite time spenting task.. And I was wondering if there is a better way of doing it...
But can't seam to work it out just yet

Cheers!

 

[Norie]

Don't you have a table with search terms like Müller, Müllgrundgebühr etc along with the 'update' values that correspond to the search terms?

 

[james_lankford]

why are not doing simple update statements

this is very inefficient
"select blah blah blah "
open recordset
edit recordset
update recordset

much faster to do

dim sql as string 


sql = "update tbl_CSV set  Buchtext = 'Müllgrundgebühr', Umsatztext = 'Müllgebühr 10%', Gegenkonto = '7398' where Umsatztext LIKE '*Müllgrundgebühr Th*'" 


DoCmd.RunSQL sSQL


sql = "update tbl_CSV set  Buchtext = 'Müllgrundgebühr', Umsatztext = 'Müllgebühr 10%', Gegenkonto = '7398' where Umsatztext LIKE '*Müllgrundgebühr A*'" 


DoCmd.RunSQL sSQL


or do them bot at the same time like this 


sql = "update tbl_CSV set  Buchtext = 'Müllgrundgebühr', Umsatztext = 'Müllgebühr 10%', Gegenkonto = '7398' where Umsatztext LIKE '*Müllgrundgebühr Th*' or Umsatztext LIKE '*Müllgrundgebühr A*' " 


DoCmd.RunSQL sSQL

 

[Norie]

James

Perhaps running multiple queries over a whole table individually might slow things down a bit.

PS Pretty sure that last query won't work.