I was *forced* to use RunSQL

[DCM Fan]

Access 2000/Windows 2000 Pro with all current updates on the client; SQL Server
7.0/Windows 2000 Server with all current updates on the server.

Bottom line: In a certain context dbs.Execute strSQL, dbfailonerror would cause
Access to GPF, whilst DoCmd.RunSQL strSQL, True would perform as expected.

The context is nothing special, but MUST have something to do with it, even
though I've done this 1000X before. In the AfterUpdate Event of a control on a
subform, I would ask the user if they'd like to have their change affect other
records in the same subform...if YES, then run the target SQL statement, and
refresh the subform so the user could see the changes.

The truly maddening part is that it was intermittent... sometimes it would work,
and sometimes it wouldn't. Some machines would never fail on dbs.Execute.
RunSQL made it work for all machine at all times.

dbs is dimensioned as a Global Variable (DAO.Database), and it is set to
Currentdb in the line before the Execute method.

dbs.Execute is used w/o incident elsewhere in the application.

When running line-by-line in debug mode, everything worked great until the
..Execute method...crasho la. If I were to take the SQL statement and run it from
the query window, it would work, even in debug mode.

CONCLUSION: Something's fishy about the dbs reference during runtime in this
context. The code is similar to the following:

Sub Control_AfterUp date
If Control.OldValu e <> Control.Value Then
If vbYes = MsgBox("Cascade ") Then
strSQL = "UPDATE TableBoundToSub form SET TargetField = ...blah"
Set dbs = Currentdb
dbs.Execute dbfailonerror
End if
End if
End Sub

One more thing: I have code like above on two separate controls in the same
subform. One of the SQL statements was complex enough that I decided to make a
paramterized Action query out of it, so it's code:

Sub Control_AfterUp date
If Control.OldValu e <> Control.Value Then
If vbYes = MsgBox("Cascade ") Then
Set dbs = Currentdb
With dbs.QueryDefs(" MyQDF")
.Parameters("P1 ") = Me.Field1
.Parameters("P2 ") = me.Field2
.Execute dbfailonerror
End With
End if
End if
End Sub

That also fails on the Execute method. When I changed both to:

Sub Control_AfterUp date
If Control.OldValu e <> Control.Value Then
If vbYes = MsgBox("Cascade ") Then
strSQL = "UPDATE TableBoundToSub form SET TargetField = ...blah"
DoCmd.SetWarnin gs False
Docmd.RunSQL strSQL, True
DoCmd.SetWarnin gs True
End if
End if
End Sub

....everything works as expected.

Any thoughts?

[Peter Doering]

On 01 May 2004 06:23:05 GMT, DCM Fan wrote:

[...]
Bottom line: In a certain context dbs.Execute strSQL, dbfailonerror would cause
Access to GPF, whilst DoCmd.RunSQL strSQL, True would perform as expected.
[...]

Any thoughts?

/decompile ?

Peter

--
No mails please.

[Steve Jorgensen]

On 01 May 2004 06:23:05 GMT, dc****@aol.comSPNOAM (DCM Fan) wrote:

Access 2000/Windows 2000 Pro with all current updates on the client; SQL Server
7.0/Windows 2000 Server with all current updates on the server.

Bottom line: In a certain context dbs.Execute strSQL, dbfailonerror would cause
Access to GPF, whilst DoCmd.RunSQL strSQL, True would perform as expected.

The context is nothing special, but MUST have something to do with it, even
though I've done this 1000X before. In the AfterUpdate Event of a control on a
subform, I would ask the user if they'd like to have their change affect other
records in the same subform...if YES, then run the target SQL statement, and
refresh the subform so the user could see the changes.

The truly maddening part is that it was intermittent... sometimes it would work,
and sometimes it wouldn't. Some machines would never fail on dbs.Execute.
RunSQL made it work for all machine at all times.

dbs is dimensioned as a Global Variable (DAO.Database), and it is set to
Currentdb in the line before the Execute method.

dbs.Execute is used w/o incident elsewhere in the application.

When running line-by-line in debug mode, everything worked great until the
.Execute method...crasho la. If I were to take the SQL statement and run it from
the query window, it would work, even in debug mode.

CONCLUSION: Something's fishy about the dbs reference during runtime in this
context. The code is similar to the following:

Sub Control_AfterUp date
If Control.OldValu e <> Control.Value Then
If vbYes = MsgBox("Cascade ") Then
strSQL = "UPDATE TableBoundToSub form SET TargetField = ...blah"
Set dbs = Currentdb
dbs.Execute dbfailonerror
End if
End if
End Sub

One more thing: I have code like above on two separate controls in the same
subform. One of the SQL statements was complex enough that I decided to make a
paramterized Action query out of it, so it's code:

Sub Control_AfterUp date
If Control.OldValu e <> Control.Value Then
If vbYes = MsgBox("Cascade ") Then
Set dbs = Currentdb
With dbs.QueryDefs(" MyQDF")
.Parameters("P1 ") = Me.Field1
.Parameters("P2 ") = me.Field2
.Execute dbfailonerror
End With
End if
End if
End Sub

That also fails on the Execute method. When I changed both to:

Sub Control_AfterUp date
If Control.OldValu e <> Control.Value Then
If vbYes = MsgBox("Cascade ") Then
strSQL = "UPDATE TableBoundToSub form SET TargetField = ...blah"
DoCmd.SetWarnin gs False
Docmd.RunSQL strSQL, True
DoCmd.SetWarnin gs True
End if
End if
End Sub

...everythin g works as expected.

Any thoughts?

I don't know if this helps, but....

I had a very similar problem today. I was helping a colleague debug a problem
with a query that used to work and recently started failing. He had added a
criterion to the query that compared a field to a function call that returns
the current company ID, and the comparison was on a left-joined table. His
change wouldn't have worked right anyway because the purpose of the left join
was to check for a mismatch, and applying a criterion to the left-joined table
made it act like an inner join. It still should not have produced an error,
though.

To fix this, I created a separate querydef to return just the records from the
secondary table that apply to the current company, then changed the original
query to left-join to the new query. I could have made it a derived table
using [...]. As <name> syntax, but I thought a separate saved query would be
more clear.

Before making this change, the error message was something like "Unknown JET
Error.". After the change, everything behaved as it should.

[MacDermott]

Have you checked carefully that your query is executing correctly under
RunSQL?
Turning off the warnings is leaving open the possibility that it's quietly
failing and just not letting you know.
(You might get the same effect if you dropped dbfailonerror from your code.)

I'd suggest that you try removing SetWarnings around the RunSQL.
Yes, you'll have to accept a few more messageboxes when it runs, but you may
get a useful error message when it fails.
Once you can see what the problem is, you'll be in a better position to
address it.

HTH
- Turtle

"DCM Fan" <dc****@aol.com SPNOAM> wrote in message
news:20******** *************** ****@mb-m01.aol.com...

Access 2000/Windows 2000 Pro with all current updates on the client; SQL Server 7.0/Windows 2000 Server with all current updates on the server.

Bottom line: In a certain context dbs.Execute strSQL, dbfailonerror would cause Access to GPF, whilst DoCmd.RunSQL strSQL, True would perform as expected.

The context is nothing special, but MUST have something to do with it, even though I've done this 1000X before. In the AfterUpdate Event of a control on a subform, I would ask the user if they'd like to have their change affect other records in the same subform...if YES, then run the target SQL statement, and refresh the subform so the user could see the changes.

The truly maddening part is that it was intermittent... sometimes it would work, and sometimes it wouldn't. Some machines would never fail on dbs.Execute.
RunSQL made it work for all machine at all times.

dbs is dimensioned as a Global Variable (DAO.Database), and it is set to
Currentdb in the line before the Execute method.

dbs.Execute is used w/o incident elsewhere in the application.

When running line-by-line in debug mode, everything worked great until the
.Execute method...crasho la. If I were to take the SQL statement and run it from the query window, it would work, even in debug mode.

CONCLUSION: Something's fishy about the dbs reference during runtime in this context. The code is similar to the following:

Sub Control_AfterUp date
If Control.OldValu e <> Control.Value Then
If vbYes = MsgBox("Cascade ") Then
strSQL = "UPDATE TableBoundToSub form SET TargetField = ...blah"
Set dbs = Currentdb
dbs.Execute dbfailonerror
End if
End if
End Sub

One more thing: I have code like above on two separate controls in the same subform. One of the SQL statements was complex enough that I decided to make a paramterized Action query out of it, so it's code:

Sub Control_AfterUp date
If Control.OldValu e <> Control.Value Then
If vbYes = MsgBox("Cascade ") Then
Set dbs = Currentdb
With dbs.QueryDefs(" MyQDF")
.Parameters("P1 ") = Me.Field1
.Parameters("P2 ") = me.Field2
.Execute dbfailonerror
End With
End if
End if
End Sub

That also fails on the Execute method. When I changed both to:

Sub Control_AfterUp date
If Control.OldValu e <> Control.Value Then
If vbYes = MsgBox("Cascade ") Then
strSQL = "UPDATE TableBoundToSub form SET TargetField = ...blah"
DoCmd.SetWarnin gs False
Docmd.RunSQL strSQL, True
DoCmd.SetWarnin gs True
End if
End if
End Sub

...everything works as expected.

Any thoughts?

[DCM Fan]

<</decompile ?>>

Been there, done that.

<<the error message was something like "Unknown JET Error.". >>

I wish I had that opportunity...A ccess would GPF...no error messages. And
besides, the query worked when run manually.

<<Have you checked carefully that your query is executing correctly under
RunSQL?>>

Of course I did!

[Dimitri Furman]

On May 01 2004, 02:23 am, dc****@aol.comSPNOAM (DCM Fan) wrote in
news:20******** *************** ****@mb-m01.aol.com:

Bottom line: In a certain context dbs.Execute strSQL, dbfailonerror
would cause Access to GPF, whilst DoCmd.RunSQL strSQL, True would
perform as expected.

You may try to change it to

dbs.Execute strSQL, dbfailonerror + dbSeeChanges

Technically, you want to always do it this way when you are using DAO to
work with SQL Server backend.

I have no idea if this has any relevance to your posted issue.

--
remove a 9 to reply by email

[Albert D. Kallal]

>CONCLUSION: Something's fishy about the dbs reference during runtime in
this
context. The code is similar to the following:

did you ever stop to ask the following question?

How does sql server, or MySql or any database engine evaluate the following:

DoCmd.RunSQL "update main set notes = 'hello' where City =
forms!main!Upda teCity"

Ask your self why does the above not test for City = "forms!main!Cit y" but
in fact somehow magically converts the "forms!main!tva lue" to the actual
value? Sql server can't do this. Oracle can't do the above. Yet, we can use
both Oracle, or sql server with ms-access. So, how can the above be
possible?

It is question that should leap out at you!

The fact is that RunSql uses the JET expression service. The expression
service is a thing that runs and evaluates expression in the sql BEFORE the
query is processed. I mean, I never seen sql for Oracle, or MySql, or even
sql server allow expressions like:

where City = forms!main!Upda teCity"

So, when you use currentDB.Execu te, you DO NOT have the expression service
available. That means you can't use a forms ref directly in the sql
(gee...what a mess in the fact that we CAN do this!). I would suggest that
you avoid using ANY sql that has a forms ref. It is simply not needed during
development. Worse, it means your sql or query is now tied to some form that
has to be opened..and if closed..the query will fail. And, even more worse
is that you can NOT use the sql query for other things..as it is tied to ONE
form that must be opened (so, you get very poor re-usability)

So, the following will work:
'
DoCmd.RunSQL "update main set notes = 'hello' where City =
forms!main!Upda teCity"

However, for Execute..we need to use:

currentDB.Execu te "update main set notes = 'hello' where City = '" &
forms!main!Upda teCity & "'"
I also notice that in your posted example you for strange and bizarre reason
left out the sql! I mean here you are wondering about a problem with
sql..and then you post a whole bunch of code...but then leave out the sql?
(not a big deal..but then we could have spotted the fact that you use direct
forms refs in your sql).

Anyway..likely you have some sql that requites the expression service to
resolve some value to a form, or some function..and that is not a good idea
at all..

Further, why use a global var set to currentdb? If any error occurs that you
don't trap..then that var is going to be destroyed..and then any code you
try to run will fail.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
pl************* *****@msn.com
http://www.attcanada.net/~kallal.msn

[DCM Fan]

<,How does sql server, or MySql or any database engine evaluate the following:

DoCmd.RunSQL "update main set notes = 'hello' where City =
forms!main!Upda teCity">>

You're joking, right? Do actually think that I have an embedded form reference
in a SQL statement? Did you read the entire original post, where I clearly
showed that I create the SQL statement in-process (in a strSQL variable),
before the call to dbs.execute, and where I said that the SQL statement
evaluates properly and can be run when pasted into the query window?
(TRANSLATION: It's not the SQL statement)

<<I also notice that in your posted example you for strange and bizarre reason
left out the sql! >>

Yes, b/c the SQL is NOT the problem! Recall in the original post that I also
used a parameterized QueryDef as well...with the same problem. (TRANSLATION:
Not the SQL statement)

<<If any error occurs that you don't trap..then that var is going to be
destroyed..and then any code you
try to run will fail.>>

An error would be great, and my error-trap would've caught it, but I clearly
stated in the message that this was a crash...GPF in fact. (TRANSLATION: Not
the SQL statement)

Anyway, a previous poster did give me something useful to try, and I should've
tried it before: using the dbSeeChanges constant with dbFailOnError. Normally,
Access complains about this when I leave it off, but it's not right now...just
crashing. I bet that's the problem, and I should know better. It's certainly
not the SQL statement!

Thanks anyway!

[DCM Fan]

<<dbs.Execute strSQL, dbfailonerror + dbSeeChanges

Technically, you want to always do it this way when you are using DAO to
work with SQL Server backend.

I have no idea if this has any relevance to your posted issue.>>

Right on! I need to do that! I forgot all about this, b/c Access normall
complains with a pretty straight-forward message (You must use dbSeeChanges
when connecting to ...) but it didn't this time...just crashed.

I'm sure this will solve the problem.

Thanks.

[David W. Fenton]

dc****@aol.comSPNOAM (DCM Fan) wrote in
news:20******** *************** ****@mb-m01.aol.com:

Sub Control_AfterUp date
If Control.OldValu e <> Control.Value Then
If vbYes = MsgBox("Cascade ") Then
strSQL = "UPDATE TableBoundToSub form SET TargetField =
...blah" Set dbs = Currentdb
dbs.Execute dbfailonerror
End if
End if
End Sub

I can't really offer any advice for your problem, but I will observe
this, on the assumption that your air code example above is not
wholly divorced from the reality of your situation:

What I see is that you're using the AfterUpdate of a bound control
to update a table that is bound to an open form. I won't assume that
the bound control you're testing is bound to the same table as
you're updating with SQL, but if it's one of the other open forms
(my guess is that the control is on the parent form and you're using
SQL to update the subform's records), perhaps that form is dirty,
and there's a lock on the records and that's causing an error that
Access is not recovering from (granted, it shouldn't IPF).

I just don't see a need for the kind of operation your example uses.
If all the records in the subform need to be updated, then use the
control that you're updating as part of the link criteria.

Also, don't disregard Turtle's note about testing with SetWarnings
on, as some error may be occuring that is being discarded in your
RunSQL context, but not in the .Execute context, and the attempt to
handle that error is what is leading to the IPF.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc