By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,400 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

I was *forced* to use RunSQL

P: n/a
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...crashola. 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform 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_AfterUpdate
If Control.OldValue <> 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform SET TargetField = ...blah"
DoCmd.SetWarnings False
Docmd.RunSQL strSQL, True
DoCmd.SetWarnings True
End if
End if
End Sub

....everything works as expected.

Any thoughts?
Nov 12 '05 #1
Share this Question
Share on Google+
21 Replies


P: n/a
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.
Nov 12 '05 #2

P: n/a
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...crashola. 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform 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_AfterUpdate
If Control.OldValue <> 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform SET TargetField = ...blah"
DoCmd.SetWarnings False
Docmd.RunSQL strSQL, True
DoCmd.SetWarnings True
End if
End if
End Sub

...everything 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.
Nov 12 '05 #3

P: n/a
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.comSPNOAM> 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...crashola. 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform 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_AfterUpdate
If Control.OldValue <> 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform SET TargetField = ...blah"
DoCmd.SetWarnings False
Docmd.RunSQL strSQL, True
DoCmd.SetWarnings True
End if
End if
End Sub

...everything works as expected.

Any thoughts?

Nov 12 '05 #4

P: n/a
<</decompile ?>>

Been there, done that.

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

I wish I had that opportunity...Access 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!
Nov 12 '05 #5

P: n/a
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
Nov 12 '05 #6

P: n/a
>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!UpdateCity"

Ask your self why does the above not test for City = "forms!main!City" but
in fact somehow magically converts the "forms!main!tvalue" 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!UpdateCity"

So, when you use currentDB.Execute, 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!UpdateCity"

However, for Execute..we need to use:

currentDB.Execute "update main set notes = 'hello' where City = '" &
forms!main!UpdateCity & "'"
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

Nov 12 '05 #7

P: n/a
<,How does sql server, or MySql or any database engine evaluate the following:

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

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!
Nov 12 '05 #8

P: n/a
<<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.
Nov 12 '05 #9

P: n/a
dc****@aol.comSPNOAM (DCM Fan) wrote in
news:20***************************@mb-m01.aol.com:
Sub Control_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform 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
Nov 12 '05 #10

P: n/a
<<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.>>

Yup. Done it 1000X before.

<<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)>>

No...As I clearly stated in the original post: "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..."

<< perhaps that form is dirty, and there's a lock on the records and that's
causing an error that>>

Yup, always a possibility, but as I also stated in the original post "If I were
to take the SQL statement and run it from
the query window, it would work, even in debug mode." So that would mean I
already tested that. For if locking were an issue it would arise in debug mode
as well.

<<I just don't see a need for the kind of operation your example uses.>>

Chaging a single date in the subform could cause all other dates to change, on
existing records. It's just a "nice to have" feature, because in reality all
dates are completely independent of each other. This feature just says "Hey,
you changed the current record's date. Would you like all future dates to be
adjusted accordingly?" If the user clicks YES, then code runs.

I'm sure it's the dbSeeChanges problem...When DAO passes update SQL strings to
SQL Server, SQL Server complains when that dbSeeChanges constant is not there.

In the past one of two things would normally happen when I left off
dbSeeChanges:

1) It would work anyway
2) Access would error-out, with a clear message stating that dbSeeChanges was
required in this context.

That allowed me to be lazy.

But now, in May 2004, with all the new updates to Office/Windows/MDAC, maybe
this GPF behavior is a third possibility....

I'll test it next week and get back with the group.
Nov 12 '05 #11

P: n/a
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...crashola. 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform 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_AfterUpdate
If Control.OldValue <> 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform SET TargetField = ...blah"
DoCmd.SetWarnings False
Docmd.RunSQL strSQL, True
DoCmd.SetWarnings True
End if
End if
End Sub

...everything works as expected.

Any thoughts?


I have experienced exactly the same problem you have described on two occasions.
..Execute would fail and RunSQL would succeed.
Due to time constraints I lived with RunSQL, but revisited it a few months later and with no changes to the SQL, .Execute now worked without error.

I don't know what the problem was or what fixed it, but in the months between rechecking the code I would have decompiled several times and I would
have imported all objects into a clean mdb several times and recreated the file by exporting and importing from text files (thanks Lyle).

I would try each of the above and see which if any resolves the problem.

Wayne Gillespie
Gosford NSW Australia
Nov 12 '05 #12

P: n/a
<<rechecking the code I would have decompiled several times>>

Did that.

<<have imported all objects into a clean mdb several times>>

yea, I do that when developing a lot, but this is an app that has been in
production for years, to which I had just add this functionality last week.

<<and recreated the file by exporting and importing from text files>>

Yea, I was going there next, but I ran out of time. I'll try that too. Gawd,
the Access fileformat sucks...
Nov 12 '05 #13

P: n/a
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...crashola. 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform 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_AfterUpdate
If Control.OldValue <> 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform SET TargetField = ...blah"
DoCmd.SetWarnings False
Docmd.RunSQL strSQL, True
DoCmd.SetWarnings True
End if
End if
End Sub

...everything works as expected.

Any thoughts?


Do you have any API calls in your application? There appears to be an
alleged problem with the loading of DLLs in A2K2, that problem may exist
in A2K as well, all API declarations load the DLL at app startup
regarless of whether you actually call the function delcared, this seems
to cause a random GPF/UAE/IPF/General exception or whatever they happen
to be calling it these days. All I can suggest at this stage is (if
possible) try it under WinXP and send the error report to Microsoft each
and every time.

Also a thought, do you also have a global db variable pointing to the
SQL server? I usually have gdbSQL and use that a lot in conjunction with
dbSQLPassThrough, if it's Jet getting it's knickers in a twist then a
passthru query will avoid it.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 12 '05 #14

P: n/a
On Sun, 02 May 2004 10:47:58 +0100, Trevor Best <nospam@localhost> wrote:
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...crashola. 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform 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_AfterUpdate
If Control.OldValue <> 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_AfterUpdate
If Control.OldValue <> Control.Value Then
If vbYes = MsgBox("Cascade") Then
strSQL = "UPDATE TableBoundToSubform SET TargetField = ...blah"
DoCmd.SetWarnings False
Docmd.RunSQL strSQL, True
DoCmd.SetWarnings True
End if
End if
End Sub

...everything works as expected.

Any thoughts?


Do you have any API calls in your application? There appears to be an
alleged problem with the loading of DLLs in A2K2, that problem may exist
in A2K as well, all API declarations load the DLL at app startup
regarless of whether you actually call the function delcared, this seems
to cause a random GPF/UAE/IPF/General exception or whatever they happen
to be calling it these days. All I can suggest at this stage is (if
possible) try it under WinXP and send the error report to Microsoft each
and every time.

Also a thought, do you also have a global db variable pointing to the
SQL server? I usually have gdbSQL and use that a lot in conjunction with
dbSQLPassThrough, if it's Jet getting it's knickers in a twist then a
passthru query will avoid it.


FWIW the db I experienced the problem with was in A97.
Wayne Gillespie
Gosford NSW Australia
Nov 12 '05 #15

P: n/a
Wayne Gillespie <be*****@NObestfitsoftwareSPAM.com.au> wrote in
news:47********************************@4ax.com:
I don't know what the problem was or what fixed it, but in the
months between rechecking the code I would have decompiled several
times and I would have imported all objects into a clean mdb
several times and recreated the file by exporting and importing
from text files (thanks Lyle).


Surely a decompile would have nothing whatsoever to do with it,
unless your SQL were calling a user-defined function.

Too often in this forum I think we wield "decompile" as a club
against every problem, when all it ever really addresses is possible
corruption within the Access project. That means only the forms,
reports and modules.

Decompiling it likely to have zero effect on stored queries and on
the execution of SQL that does not include calls to UDFs.

On the other hand, a simple compact can cause SQL to execute
differently, because it discards the compiled query plans for all
saved SQL (including the temporary compilation that is stored for
certain kinds of SQL in an MDB that is not stored in a saved query).

While it is certainly remotely possible that corruption of a project
could somehow lead to the failure of SQL that is not dependent on
UDFs, it is extremely unlikely.

And the way to test if the problem is in the Access project and not
the SQL is to test the failing SQL in a freshly-created MDB that has
only the Jet objects in it (tables, table links and saved queries).
If .Execute still fails in that situation, and the SQL does not use
saved any queries, then the problem is with the communication
between Jet and the back end.

But more likely, in my experience, is something wrong with the
compiled and saved query plan of something referred to in the SQL
being executed.

Keep in mind, also, that in the case of a SQL Server back end, the
server will cache and save query plans for SQL strings that it sees
on an ad hoc basis, so the problem could be with the SQL Server's
cached plan for the SQL you're sending it. I don't know exactly how
you'd trick SQL Server into not using its cached query plan or if
you can force it to discard them, but that is the most likely place
to look for the source of problems of this nature when SQL Server is
involved.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #16

P: n/a
He was over-excited, but technically correct.

Pasting into the query window is the same as using RunSQL,
not the same as using .Execute, so it says nothing more about
the validity of the SQL. Creating SQL Statement in-process
in a strSQL variable does not preclude the use of an
embedded form reference - we commonly advise against it
to people who have done so.

BTW, the reference to embedded functions is also worth
following up: deeply embedded functions gave me some grief
with GPF's

I also get GPF's from type mismatches in queries,
some legitimate, some that should be automatic type
conversion, and some that are just plain flaky (problems
with memo-type parameters).

Finally, I also get GPF's in A2K sometimes with things that
were just trappable errors in A97: A2K is noticeably more
likely to crash. Lack of 'dbSeeChanges' could just be another
example of this.
(david)
"DCM Fan" <dc****@aol.comSPNOAM> wrote in message
news:20***************************@mb-m29.aol.com...
<,How does sql server, or MySql or any database engine evaluate the following:
DoCmd.RunSQL "update main set notes = 'hello' where City =
forms!main!UpdateCity">>

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!

Nov 12 '05 #17

P: n/a
On the evidence I have seen, no version of Access loads
declared DLL's at startup.

Our application loads declared DLL's explicitly.
I have code to do the explicit loading. Use of the DLL
without loading causes an error (because the path
to the DLL is not static): Declaration has not
triggered an error, and has not triggered loading.

This does not prove the point, but does prove the exception.

This code is running in A2K2 as an A2K MDE.

API declarations that refer to Windows System DLL's are
referring to DLL's that are already loaded BEFORE startup.
(david)

Do you have any API calls in your application? There appears to be an
alleged problem with the loading of DLLs in A2K2, that problem may exist
in A2K as well, all API declarations load the DLL at app startup
regarless of whether you actually call the function delcared, this seems
to cause a random GPF/UAE/IPF/General exception or whatever they happen
to be calling it these days. All I can suggest at this stage is (if
possible) try it under WinXP and send the error report to Microsoft each
and every time.

Also a thought, do you also have a global db variable pointing to the
SQL server? I usually have gdbSQL and use that a lot in conjunction with
dbSQLPassThrough, if it's Jet getting it's knickers in a twist then a
passthru query will avoid it.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

Nov 12 '05 #18

P: n/a
david epsom dot com dot au wrote:
On the evidence I have seen, no version of Access loads
declared DLL's at startup.

Our application loads declared DLL's explicitly.
I have code to do the explicit loading. Use of the DLL
without loading causes an error (because the path
to the DLL is not static): Declaration has not
triggered an error, and has not triggered loading.

This does not prove the point, but does prove the exception.

This code is running in A2K2 as an A2K MDE.

API declarations that refer to Windows System DLL's are
referring to DLL's that are already loaded BEFORE startup.


Perhaps "app startup" was a bit premature, it occurs when a module is
loaded in the VBE (according to MS).

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 12 '05 #19

P: n/a
> Perhaps "app startup" was a bit premature, it occurs when a module
loaded in the VBE (according to MS).
Do you have a reference? Although there is no reason not
to believe this, I suspect a mis-understanding: There is
no reason for A2002 to be that different from A97/A2K.
(A2K3 obviously does have a few more differences).

A2K/A97 will crash if there is a bad reference, including
any problem with the VBA/Access/Office/JET/Windows DLL's:
these are loaded on demand, or in the background after
startup, or when a module is loaded. In A2K/A97 this is
different from the loading of 'API declarations'

(david)
"Trevor Best" <nospam@localhost> wrote in message
news:40***********************@auth.uk.news.easyne t.net... david epsom dot com dot au wrote:
On the evidence I have seen, no version of Access loads
declared DLL's at startup.

Our application loads declared DLL's explicitly.
I have code to do the explicit loading. Use of the DLL
without loading causes an error (because the path
to the DLL is not static): Declaration has not
triggered an error, and has not triggered loading.

This does not prove the point, but does prove the exception.

This code is running in A2K2 as an A2K MDE.

API declarations that refer to Windows System DLL's are
referring to DLL's that are already loaded BEFORE startup.


Perhaps "app startup" was a bit premature, it occurs when a module is
loaded in the VBE (according to MS).

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

Nov 12 '05 #20

P: n/a
david epsom dot com dot au wrote:
Perhaps "app startup" was a bit premature, it occurs when a module
loaded in the VBE (according to MS).

Do you have a reference? Although there is no reason not
to believe this, I suspect a mis-understanding: There is
no reason for A2002 to be that different from A97/A2K.
(A2K3 obviously does have a few more differences).

A2K/A97 will crash if there is a bad reference, including
any problem with the VBA/Access/Office/JET/Windows DLL's:
these are loaded on demand, or in the background after
startup, or when a module is loaded. In A2K/A97 this is
different from the loading of 'API declarations'


No public references that I know of, this was a mail from MS
<---
Subject: RE: access crash survey
Thank you Trevor. This may prove helpful. When a VB module is loaded in
the VBE dll, the external DLL references are actually set up before any
code runs, so it still might be possible that the problem could occur in
design when you go to save (it may recompile in the background and try
to form these references at that time for example).
--->

(see, sending the error report to MS in XP works :-)

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 12 '05 #21

P: n/a
Dunno. I THINK that refers to 'referenced' variables,
functions, and objects. You can see this happen when
you declare a variable in a VB procedure: the external
DLL references are set up at design time, and when
the module is loaded. In contrast, I think that API
declarations are only checked against the declaration:
I don't think the declared DLL is loaded at compile or
module load time.

(david)
"Trevor Best" <nospam@localhost> wrote in message
news:40***********************@auth.uk.news.easyne t.net...
david epsom dot com dot au wrote:
Perhaps "app startup" was a bit premature, it occurs when a module
loaded in the VBE (according to MS).

Do you have a reference? Although there is no reason not
to believe this, I suspect a mis-understanding: There is
no reason for A2002 to be that different from A97/A2K.
(A2K3 obviously does have a few more differences).

A2K/A97 will crash if there is a bad reference, including
any problem with the VBA/Access/Office/JET/Windows DLL's:
these are loaded on demand, or in the background after
startup, or when a module is loaded. In A2K/A97 this is
different from the loading of 'API declarations'


No public references that I know of, this was a mail from MS
<---
Subject: RE: access crash survey
Thank you Trevor. This may prove helpful. When a VB module is loaded in
the VBE dll, the external DLL references are actually set up before any
code runs, so it still might be possible that the problem could occur in
design when you go to save (it may recompile in the background and try
to form these references at that time for example).
--->

(see, sending the error report to MS in XP works :-)

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

Nov 12 '05 #22

This discussion thread is closed

Replies have been disabled for this discussion.