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

 

[silentwolf]

Hi Norie,
no I don't have a search table.. I used a search table before but there was the correct name in a table and updated after all but what if I need to search for parts in that string it will not work or??
Could you post a litte example perhabs??

Thanks

 

[silentwolf]

Hi James,
also thanks to you for your input!
I will also look into yours.

Many thanks

 

[james_lankford]

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.

but isn't he already running multiple queries over a whole table ?

he's just doing it manually with vba
opening a recordset, looping through each row and updating each field individually

then repeating the entire process with the next "where criteria"

and are you saying the last query won't work because its an update with two "likes" in the where clause ?

 

[tiedied61]

James has a correct, simple solution. The only thing about performance would be to be sure that the Umsatztext field (and whatever other criteria fields) are indexed before running. With indexes, these updates will be nearly instantaneous. However, if this is not an access table, but a CSV as the name suggests, try importing into access, add the appropriate indexes, run the queries and then dump back out.

And yes Norie, the 3rd SQL will work, as long as the values being Set are the same for all rows represented by the criteria.


Hope this helps
Art
MS Certified Access Developer
25+ years Experience

 

[silentwolf]

Hi guys,
sorry that I was not on here for quite sometime !
Unfortunatelly I still don`t get it to work ... I guess I have a lack of understanding it all.
HOwever I thank you guys for your input!! I will look into it when I do have a bit more time and let you guys know how I am going about

Cheers!!