SQL Update code problems

  • Thread starter Thread starter nooB
  • Start date Start date
N

nooB

Hi,

I'm running an SQL Update code as part of an On_Click
event of a form button, my problem is that the code
appears to continue to run after the SQL line before the
SQL line has completed the Update to the table.
Is there a way to stop the code after the Docmd.RunSQL
line until the SQL code has completely finished the Update
to the Table?
My proble is occuring as after running this code I'm
trying to reset field values back to 0 to allow for
following changes, should I just move this code to
elsewhere and just *hope* that the previous code has
completed before I get to it?

nooB
 
Just an idea...but wut if u make a Function called
DoCmdRunSQL and pass it the SQL as a string. Now just call
that function behind the form.

Function DoCmdRunSQL(strsql as String)
DoCmd.RunSQL strsql
End Function
 
I had a similar problem. Instead of SQL, I was running
DOS scripts that move and rename files. Then my code had
to read the files. This created a problem because the
files hadn't finnished copying. I resolved this problem
by putting the code that launched the DOS scripts into a
function and Calling it from the main program. I am under
the impression that when you put code into a function and
call that function. The main program cannot proceed until
it gets a return from the function.

Hope this is of some help.

Michael
 
nooB said:
I'm running an SQL Update code as part of an On_Click
event of a form button, my problem is that the code
appears to continue to run after the SQL line before the
SQL line has completed the Update to the table.
Is there a way to stop the code after the Docmd.RunSQL
line until the SQL code has completely finished the Update
to the Table?
My proble is occuring as after running this code I'm
trying to reset field values back to 0 to allow for
following changes, should I just move this code to
elsewhere and just *hope* that the previous code has
completed before I get to it?
Click to expand...

Try using the Execute method instead of RunSQL.
 
how do I use the Execute Method?

just docmd.Execute strSQL?

searching Help for Execute method only shows 'With
fs'(file search) for locating info rather than Updating as
per SQL code.

excuse my ignorance :)

nooB
 
found answer on MS KB site to use execute - seems to work
great thanks, does that always wait until code has
executed fully before code continues? and does it have
much effect on performance? my system runs accross slow
Network with slow PC's :(

thanks,

nooB
 
nooB said:
found answer on MS KB site to use execute - seems to work
great thanks, does that always wait until code has
executed fully before code continues? and does it have
much effect on performance? my system runs accross slow
Network with slow PC's :(
Click to expand...

Who knows which is faster under what circumstances? But
remember that code that isn't required to produce a correct
result can be made to run extremely fast, ha, if any result
is acceptable, why bother running a query ;-)

At least, the DAO Execute must wait for completion because
it sets the db object's RecordsAffected property.
--
Marsh
MVP [MS Access]


 
Back
Top