Change case in access table

[ootkhopdi]

i have a table which having a large number of records..
i want to change Name field data into Proper Case

i can do change data in upper case by field format ">" or in lower case by "<"
but how can i change into proper case ..
i dont want to create an query to change data in proper case because my data having many records and query works too much slow. many times access not responding and get closed...


please give me solution for this . how can quickly change my data..

thanks in advance

 

[arnelgp]

Its much easy with query. Try it first:

Update yourTable set field=Strconv(field, 3)

 

[The_Doc_Man]

query works too much slow

The query processor is more efficient than anything else you will be able to use.

If you tried to do this using a VBA loop, that code is pseudo-compiled, not fully compiled, and therefore will be much slower. Besides which, you would have to add code to diddle around with the recordset. The loop would become complex rather quickly.

A query is interpreted ONCE to determine the optimum data-scan method (by index, by relation scan, or by phase of the moon - just kidding about the last one). But then, the query processor IS fully compiled code. If you have a good SQL statement (such as the one offered by ArnelGP), that is THE fastest you will ever get with Access. Further, if you don't put in any other qualifiers, then the query engine doesn't even have to do a sort operation. You just let it handle all fields in whatever way it finds them.

The ONLY way this would be faster would be if your database was split and the back end was something like SQL server or ORACLE server or MySQL or SyBase i.e. an active back end that was on a stand-alone SQL engine. Because then you could make it execute entirely on the local back end server. If the database is NOT split, then no speed enhancement is available.

Well... that's not entirely true. There IS one way this would be faster - not doing it at all and only using a StrConv( field, 3 ) when formatting a report or form i.e. do it on the fly. You would never notice the minuscule amount of time for the StrConv function in that context. Yes, you would have to repeat the StrConv every time you visited a record, but for reports and forms, the amount of time is hardly noticeable among all the other things that happen during formatting.