SQL Statement or FOR/NEXT loop ?

[prakashwadhwani]

I have 2 columns in a continuous form.

One called Balance_1 and the other Balance_2.

I want to transfer the contents of Balance_2 to Balance_1 ... for all
2000 rows in the table.

How to go about this ? Should I use a for/next loop or a SQL
statement.

If you'll recommend a SQL Statement, could you please post some air-
code ? My SQL is v.weak.

Thx.

[Albert D. Kallal]

The code to do this update is:
dim strSql as string

strSql = "update NameOfTable set Balance_1 = Balance_2"
currentdb.Execute strSql

In fact, you could just use one line of code:

currentdb.Execute "update NameOfTable set Balance_1 = Balance_2"

So, it is lot less code then writing a udpate loop...

It not clear which records you wanted updated? The above statement will
update all records (rows) in the table. If there is some type of filter, or
restriction, then you have to change the above sql to reflect this (in other
words, the above sql has no relation to the form, or records in a form).

Since this is a sql update statement, they are VERY dangerous. So, since you
new to this...do make a backup before you run/test this type of update...
So....say again:

do make a backup before you do this....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[prakashwadhwani]

Thx Albert ! Nice to hear from you after ages. It's me who's been off
this NG for a long time.

I am aware that SQL is far faster than a for/next loop but needed some
hand holding for the statement & syntax.

Secondly, no filter is required. I needed ALL records to be updated.

And yes, I will definitely make a backup of the table before running
this.

Once again ... Thx !!

Best Rgds,
Prakash.


On Aug 6, 1:08 pm, "Albert D. Kallal" <[e-mail]>
wrote:

The code to do this update is:

dim strSql as string

strSql = "update NameOfTable set Balance_1 = Balance_2"
currentdb.Execute strSql

In fact, you could just use one line of code:

currentdb.Execute "update NameOfTable set Balance_1 = Balance_2"

So, it is lot less code then writing a udpate loop...

It not clear which records you wanted updated? The above statement will
update all records (rows) in the table. If there is some type of filter, or
restriction, then you have to change the above sql to reflect this (in other
words, the above sql has no relation to the form, or records in a form).

Since this is a sql update statement, they are VERY dangerous. So, since you
new to this...do make a backup before you run/test this type of update...

So....say again:

do make a backup before you do this....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

[prakashwadhwani]

One more small query:

What's the difference between using DoCmd.RunSQL and
currentdb.Execute. Which is better & Why ? Any pros/cons ??

Rgds,
Prakash.

On Aug 6, 1:08 pm, "Albert D. Kallal" <[e-mail]>
wrote:

The code to do this update is:

dim strSql as string

strSql = "update NameOfTable set Balance_1 = Balance_2"
currentdb.Execute strSql

In fact, you could just use one line of code:

currentdb.Execute "update NameOfTable set Balance_1 = Balance_2"

So, it is lot less code then writing a udpate loop...

It not clear which records you wanted updated? The above statement will
update all records (rows) in the table. If there is some type of filter, or
restriction, then you have to change the above sql to reflect this (in other
words, the above sql has no relation to the form, or records in a form).

Since this is a sql update statement, they are VERY dangerous. So, since you
new to this...do make a backup before you run/test this type of update...

So....say again:

do make a backup before you do this....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

[Tony Toews [MVP]]

fp**************@gmail.com wrote:

>What's the difference between using DoCmd.RunSQL and
currentdb.Execute. Which is better & Why ? Any pros/cons ??

Using Currentdb.execute strsql, dbfailonerrors will give you warning messages.
docmd.runsql ignores such.

Also performance can be significantly different between the two methods. One posting
stated currentdb.execute took two seconds while docmd.runsql took eight seconds. As
always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[prakashwadhwani]

Noted !! Thank you for the explanation. Makes a lot of sense.

Best Rgds,
Prakash.

On Aug 6, 11:13 pm, "Tony Toews [MVP]" <[e-mail]:

[e-mail] wrote:

What's the difference between using DoCmd.RunSQL and
currentdb.Execute. Which is better & Why ? Any pros/cons ??

Using Currentdb.execute strsql, dbfailonerrors will give you warning messages.
docmd.runsql ignores such.

Also performance can be significantly different between the two methods. One posting
stated currentdb.execute took two seconds while docmd.runsql took eight seconds. As
always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/