Action will reset the current code in break mode - bug?

  • Thread starter Thread starter Nekdo Od Nekje
  • Start date Start date
N

Nekdo Od Nekje

Opening a table or a more complicated query in a GUI I
find myself sometimes in the position when I can not close
the open rowset. Instead there is a message: "This action
will reset the current code in break mode. Do you want to
stop the running code?" Neither choise of Yes nor choise
of No close the rowset. Sometimes by queries I can change
the view of query to SQL or Design mode after using No.
Often the only action it helps is aborting the access task.

I have the last SPs of Office and of the Windows. What is
the cause for the problem?

NON
 
Are you opening the query from code? If so, what's the code? Access thinks
you have VB code running - but I cannot guess why it thinks that closing the
query window will halt it. This is usually an indication that you have not
compiled and saved your VB project - I bet your query is referencing
functions.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
No, there isn't any VB code in my access 2000 MDB file.
All tables I use in database are linked from another
access databases. In the linked databases there are tables
about stock and sales for the past and present years,
separated by years. In my principal database there is
actually a tree of queries, where the new query is made on
the base of priors. There are also a lot of Total and
Union queries. It seems to me that the controversial
message appeared when I added a new field into the all
select statements of one union query. Now I can run (open)
this particular union query but I can not close it when it
is viewed as an opened rowset. Then it appears the
message "This action will reset...". After choosing No I
can only change the view to SQL design view and then I can
close the SQL design window. What is even worse is the
fact that it happens nothing (!) when I try to open the
next query in which there is the same union query twice
(not joined : uniqry and uniqry1). Befor adding new fields
the last query worked excellent. Yes, I can make bypass
with the make tables or I can build a totaly new database
from the ground. I suppose the problem is somehow in
loading of the successive queries, where one (union) query
changed after last compiling?

I checked also the Knowledge base. I found the described
problem only in connection with DB2 database (MKBA
234248), which is obviosly not my case and in connection
with version 6.3.91.8 of the Vbe6.dll file (MKBA 304548).
My version of Vbe6.dll is 6.4.99.69 !

Thanks for the answer and with regards

NON
 
That is very strange, indeed. The only way to solve this is to look at the
SQL from the query that breaks as well as the SQL of all the queries that
feed into it. Are you perhaps calling the query recursively? (The query
references itself in the FROM clause.) It sounds like Access is trying to
"recompile" the query and save it, but the recompile isn't done when you try
to close the query.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
No, I'm not calling any query recursively!

To simplify my story: for each of the years 2003 and 2004
I had a table of financial input and output of our
wearhouse (tbl2003, tbl2004).

For each year I made first the total query, where I
sumarized the traffic grouped by dates (qry2003, qry2004)
as it is for qry2003 (identical for the other):

qry2003: SELECT Sum(Input - Output) AS Traffic FROM
tbl2003 GROUP BY Date

Then I unioned these queries in one union query (uniqry).

uniqry : SELECT Date, Traffic FROM qry2003
UNION SELECT Date, Traffic FROM qry2004

In another query named qryState I used query uniqry twice
(without any joins) to get the financial state of the
wearhouse for each day (I simply sumarized the triangle of
a matrix) as:

qryState: SELECT [uniqry].[Date], Sum([uniqry_1].
[Traffic]) as State FROM uniqry, uniqry AS uniqry_1 WHERE
(([uniqry_1].[Date])<=([uniqry].[Date])) GROUP BY [uniqry].
[Date]

As I said all worked fine. Then I decided to add a new
field for the brand in the first queries qry2003 and
qry2004 to group the traffic also by brand names. And I
added also a parameter to choose the desired brand (a new
criteria). And normaly, I distributed the new field also
through the other queries.

And then it hapened! I went through any query and they are
all syntaxic regular.

Now I can't close normaly the uniqry (message: This action
will reset the current code in break mode.) and it happens
nothing when I try to open the qryState!

NON
 
Nekdo Od Nekje said:
No, there isn't any VB code in my access 2000 MDB file.
Click to expand...

Are you saying you have no forms, no reports, no modules in this mdb?

There could be (likely is) code behind a form/report.
 
Hi,


I cannot reproduce the described behaviour in a new database. Can you
check if you can, so it may point out that this is a problem with your
actual database ( can't repro in a new db) or if this is a problem with your
installation (can repro in a new db).

Here is what I did.

New database (point mdb).

Create a table table1, three fields, f1, f2, f3.

Create a query query1: SELECT f1, SUM(f2) as s2 FROM table1 GROUP BY f1

Create a query query2: SELECT f1, SUM(f2) as s2 FROM table1 GROUP BY f1
( the same thing as query1)


Create a query query3: SELECT f1, s2 FROM query1 UNION ALL SELECT f1, s2
FRM query2

Edit query1 to change it to: SELECT f1, SUM(f2) as s2, f3 FROM table1
GROUP BY f1, f3
Save and close the query1 (was editing it).
Edit query2 to the same. Save and close

No problem occurred


Edit query3 to change it to: SELECT f1, s2, f3 FROM query1 UNION ALL
SELECT f1, s2, f3 FRM query2

Save and close.
No problem occurred.


(are you using Jet, or MS SQL Server ? is there any other application
reading/using the data from the same database at the moment you make the
modifications ? )



Hoping it may help,
Vanderghast, Access MVP



Nekdo Od Nekje said:
No, I'm not calling any query recursively!

To simplify my story: for each of the years 2003 and 2004
I had a table of financial input and output of our
wearhouse (tbl2003, tbl2004).

For each year I made first the total query, where I
sumarized the traffic grouped by dates (qry2003, qry2004)
as it is for qry2003 (identical for the other):

qry2003: SELECT Sum(Input - Output) AS Traffic FROM
tbl2003 GROUP BY Date

Then I unioned these queries in one union query (uniqry).

uniqry : SELECT Date, Traffic FROM qry2003
UNION SELECT Date, Traffic FROM qry2004

In another query named qryState I used query uniqry twice
(without any joins) to get the financial state of the
wearhouse for each day (I simply sumarized the triangle of
a matrix) as:

qryState: SELECT [uniqry].[Date], Sum([uniqry_1].
[Traffic]) as State FROM uniqry, uniqry AS uniqry_1 WHERE
(([uniqry_1].[Date])<=([uniqry].[Date])) GROUP BY [uniqry].
[Date]

As I said all worked fine. Then I decided to add a new
field for the brand in the first queries qry2003 and
qry2004 to group the traffic also by brand names. And I
added also a parameter to choose the desired brand (a new
criteria). And normaly, I distributed the new field also
through the other queries.

And then it hapened! I went through any query and they are
all syntaxic regular.

Now I can't close normaly the uniqry (message: This action
will reset the current code in break mode.) and it happens
nothing when I try to open the qryState!

NON


-----Original Message-----
That is very strange, indeed. The only way to solve this is to look at the
SQL from the query that breaks as well as the SQL of all the queries that
feed into it. Are you perhaps calling the query recursively? (The query
references itself in the FROM clause.) It sounds like Access is trying to
"recompile" the query and save it, but the recompile isn't done when you try
to close the query.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)



.
Click to expand...
Click to expand...
 
Yes, I'm using Jet in this case. And no other notable
aplication was active. Except perhaps Trend Micro
antivirus program. I read about some problems in
connection MDB - Trend Micro in one of the newest
Knowledge Base Reports
(http://support.microsoft.com/default.aspx?scid=kb;EN-
US;827425)?

NON
-----Original Message-----
Hi,


I cannot reproduce the described behaviour in a new database. Can you
check if you can, so it may point out that this is a problem with your
actual database ( can't repro in a new db) or if this is a problem with your
installation (can repro in a new db).

Here is what I did.

New database (point mdb).

Create a table table1, three fields, f1, f2, f3.

Create a query query1: SELECT f1, SUM(f2) as s2 FROM table1 GROUP BY f1

Create a query query2: SELECT f1, SUM(f2) as s2 FROM table1 GROUP BY f1
( the same thing as query1)


Create a query query3: SELECT f1, s2 FROM query1 UNION ALL SELECT f1, s2
FRM query2

Edit query1 to change it to: SELECT f1, SUM(f2) as s2, f3 FROM table1
GROUP BY f1, f3
Save and close the query1 (was editing it).
Edit query2 to the same. Save and close

No problem occurred


Edit query3 to change it to: SELECT f1, s2, f3 FROM query1 UNION ALL
SELECT f1, s2, f3 FRM query2

Save and close.
No problem occurred.


(are you using Jet, or MS SQL Server ? is there any other application
reading/using the data from the same database at the moment you make the
modifications ? )



Hoping it may help,
Vanderghast, Access MVP



No, I'm not calling any query recursively!

To simplify my story: for each of the years 2003 and 2004
I had a table of financial input and output of our
wearhouse (tbl2003, tbl2004).

For each year I made first the total query, where I
sumarized the traffic grouped by dates (qry2003, qry2004)
as it is for qry2003 (identical for the other):

qry2003: SELECT Sum(Input - Output) AS Traffic FROM
tbl2003 GROUP BY Date

Then I unioned these queries in one union query (uniqry).

uniqry : SELECT Date, Traffic FROM qry2003
UNION SELECT Date, Traffic FROM qry2004

In another query named qryState I used query uniqry twice
(without any joins) to get the financial state of the
wearhouse for each day (I simply sumarized the triangle of
a matrix) as:

qryState: SELECT [uniqry].[Date], Sum([uniqry_1].
[Traffic]) as State FROM uniqry, uniqry AS uniqry_1 WHERE
(([uniqry_1].[Date])<=([uniqry].[Date])) GROUP BY [uniqry].
[Date]

As I said all worked fine. Then I decided to add a new
field for the brand in the first queries qry2003 and
qry2004 to group the traffic also by brand names. And I
added also a parameter to choose the desired brand (a new
criteria). And normaly, I distributed the new field also
through the other queries.

And then it hapened! I went through any query and they are
all syntaxic regular.

Now I can't close normaly the uniqry (message: This action
will reset the current code in break mode.) and it happens
nothing when I try to open the qryState!

NON


-----Original Message-----
That is very strange, indeed. The only way to solve
Click to expand...
this
is to look at the
SQL from the query that breaks as well as the SQL of
Click to expand...
all
the queries that
feed into it. Are you perhaps calling the query recursively? (The query
references itself in the FROM clause.) It sounds like Access is trying to
"recompile" the query and save it, but the recompile isn't done when you try
to close the query.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
No, there isn't any VB code in my access 2000 MDB file.
All tables I use in database are linked from another
access databases. In the linked databases there are tables
about stock and sales for the past and present years,
separated by years. In my principal database there is
actually a tree of queries, where the new query is
Click to expand...
made
on
the base of priors. There are also a lot of Total and
Union queries. It seems to me that the controversial
message appeared when I added a new field into the all
select statements of one union query. Now I can run (open)
this particular union query but I can not close it
Click to expand...
when
it
is viewed as an opened rowset. Then it appears the
message "This action will reset...". After choosing No I
can only change the view to SQL design view and then
Click to expand...
I
can
close the SQL design window. What is even worse is the
fact that it happens nothing (!) when I try to open the
next query in which there is the same union query twice
(not joined : uniqry and uniqry1). Befor adding new fields
the last query worked excellent. Yes, I can make bypass
with the make tables or I can build a totaly new database
from the ground. I suppose the problem is somehow in
loading of the successive queries, where one (union) query
changed after last compiling?

I checked also the Knowledge base. I found the described
problem only in connection with DB2 database (MKBA
234248), which is obviosly not my case and in connection
with version 6.3.91.8 of the Vbe6.dll file (MKBA 304548).
My version of Vbe6.dll is 6.4.99.69 !

Thanks for the answer and with regards

NON


-----Original Message-----
Are you opening the query from code? If so, what's the
code? Access thinks
you have VB code running - but I cannot guess why it
thinks that closing the
query window will halt it. This is usually an indication
that you have not
compiled and saved your VB project - I bet your
Click to expand...
query
is
referencing
functions.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"Nekdo Od Nekje"
Click to expand...
wrote in message
Opening a table or a more complicated query in a
Click to expand...
GUI
I
find myself sometimes in the position when I can not
close
the open rowset. Instead there is a message: "This
action
will reset the current code in break mode. Do you want
to
stop the running code?" Neither choise of Yes nor choise
of No close the rowset. Sometimes by queries I can
change
the view of query to SQL or Design mode after
Click to expand...
using
No.
Often the only action it helps is aborting the access
task.

I have the last SPs of Office and of the Windows. What
is
the cause for the problem?

NON





.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Are the tables linked from SQL Server? I found a couple of obscure KB
articles that list this error message in certain cases with linked tables.
If it isn't your anti-virus software, then the only other possibility is one
or more of the queries you're using is corrupted.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Nekdo Od Nekje said:
No, I'm not calling any query recursively!

To simplify my story: for each of the years 2003 and 2004
I had a table of financial input and output of our
wearhouse (tbl2003, tbl2004).

For each year I made first the total query, where I
sumarized the traffic grouped by dates (qry2003, qry2004)
as it is for qry2003 (identical for the other):

qry2003: SELECT Sum(Input - Output) AS Traffic FROM
tbl2003 GROUP BY Date

Then I unioned these queries in one union query (uniqry).

uniqry : SELECT Date, Traffic FROM qry2003
UNION SELECT Date, Traffic FROM qry2004

In another query named qryState I used query uniqry twice
(without any joins) to get the financial state of the
wearhouse for each day (I simply sumarized the triangle of
a matrix) as:

qryState: SELECT [uniqry].[Date], Sum([uniqry_1].
[Traffic]) as State FROM uniqry, uniqry AS uniqry_1 WHERE
(([uniqry_1].[Date])<=([uniqry].[Date])) GROUP BY [uniqry].
[Date]

As I said all worked fine. Then I decided to add a new
field for the brand in the first queries qry2003 and
qry2004 to group the traffic also by brand names. And I
added also a parameter to choose the desired brand (a new
criteria). And normaly, I distributed the new field also
through the other queries.

And then it hapened! I went through any query and they are
all syntaxic regular.

Now I can't close normaly the uniqry (message: This action
will reset the current code in break mode.) and it happens
nothing when I try to open the qryState!

NON


-----Original Message-----
That is very strange, indeed. The only way to solve this is to look at the
SQL from the query that breaks as well as the SQL of all the queries that
feed into it. Are you perhaps calling the query recursively? (The query
references itself in the FROM clause.) It sounds like Access is trying to
"recompile" the query and save it, but the recompile isn't done when you try
to close the query.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)



.
Click to expand...
Click to expand...
 
Back
Top