why does db.execute misbehave?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello folks,

I have a question regarding the .execute command for a database. I have an
application that needs data to be cleared from a linked SQL server table
values are reinserted. I used the following code to clear the values I want
cleared:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
db.Execute "delete * from mytable where myfield = '" & myfieldvalue & "'",
dbFailOnError
Set rs = db.OpenRecordset("mytable", dbOpenDynaset)


The code is in a subroutine that gets called multiple times. I find that the
code runs fine the first time through, but on the second call I get the
system message:

Access is generating errors (etc)

and the program crashes. Stepping through it in break mode it is the
db.execute command that is crashes on. I can work around this by using the
docmd.runsql but I prefer to use db.execute because I am giving the user
feedback in the status bar and having access display running query does not
give much time to see where what set of data the code is up to.

Is there anything that I can do to get the .execute command to work?

Much appreciated.

Scott
 
Scott,

Just fishing in the mud here, but do you close / reset the rs and db
objects at the end of your procudure?

HTH,
Nikos
 
Iterate through the dao errors collection to see if there are
any other ODBC error messages which you have missed.

dim dao_err as dao.error
for each dao_err in dao.errors
msgbox dao_err.description
next

(david)
 
Hi Nikos.

Yes, both db and rs are set to nothing before the procedure exits and also
are set to nothing in the error trap before displaying the error message.

Regards

Scott
 
Hi David,

I don't have the option to iterate through the errors because the message is
from the operating system. Access crashes before on db.execute command and
doesn't get any further.

Regards

Scott
 
Back
Top