Query magically erased

[drunkenfighter]

Hi,

I have a pretty serious problem which is completely repeatable:
Resizing a subdatasheet column that is based on a query, and saving the
query when closing the datasheet erases the query definition (seemingly
only if the query is defined in SQL mode). Meaning the query definition
turns into "SELECT ;"

I've seen this problem on many forums with no answers.

Here's how to repeat.
Create two tables:
Item (Id, Name)
Part (Id, Name, ItemId)

and a query:
PartOrdered defined in SQL as: "SELECT Id, Name, ItemId FROM Part ORDER
BY Name"

Put some data in Item, then Insert->Subdatasheet and choose the query
"PartOrdered". Expand the subdatasheet and resize one of the columns
then close the window and save changes. Then try and open PartOrdered.
The error message (Error 3066) "Query must have at least one
destination field." pops up and the SQL for the query is now "SELECT ;"

The problem occurs for me on any query defined as SQL rather than in
the design mode. This seems pretty serious to me, how to fix it? I'm
using Access 2002 SP3.

Hopefully someone tries this or knows how to solve the problem!
Thanks in advance!

/tim

 

[Chris2]

Hi,

I have a pretty serious problem which is completely repeatable:
Resizing a subdatasheet column that is based on a query, and saving the
query when closing the datasheet erases the query definition (seemingly
only if the query is defined in SQL mode). Meaning the query definition
turns into "SELECT ;"

I've seen this problem on many forums with no answers.

Here's how to repeat.
Create two tables:
Item (Id, Name)
Part (Id, Name, ItemId)

and a query:
PartOrdered defined in SQL as: "SELECT Id, Name, ItemId FROM Part ORDER
BY Name"

Put some data in Item, then Insert->Subdatasheet and choose the query
"PartOrdered". Expand the subdatasheet and resize one of the columns
then close the window and save changes. Then try and open PartOrdered.
The error message (Error 3066) "Query must have at least one
destination field." pops up and the SQL for the query is now "SELECT ;"

The problem occurs for me on any query defined as SQL rather than in
the design mode. This seems pretty serious to me, how to fix it? I'm
using Access 2002 SP3.

Hopefully someone tries this or knows how to solve the problem!
Thanks in advance!

/tim

tim,

I'm not sure how effective it will be, but try here (it's toll free,
fortunately):

http://support.microsoft.com/gp/contactbug


Sincerely,

Chris O.

 

[drunkenfighter]

Thanks Chris,

though it would be nice to know if others have had the same problem of
queries mysteriously being deleted. It seems like a huge "bug" to me if
it is a bug.

Did you try it out yourself?

Thanks again,
/tim

 

[Guest]

Hi,

I have a pretty serious problem which is completely repeatable:
Resizing a subdatasheet column that is based on a query, and saving the
query when closing the datasheet erases the query definition (seemingly
only if the query is defined in SQL mode). Meaning the query definition
turns into "SELECT ;"

I've seen this problem on many forums with no answers.

Here's how to repeat.
Create two tables:
Item (Id, Name)
Part (Id, Name, ItemId)

and a query:
PartOrdered defined in SQL as: "SELECT Id, Name, ItemId FROM Part ORDER
BY Name"

Put some data in Item, then Insert->Subdatasheet and choose the query
"PartOrdered". Expand the subdatasheet and resize one of the columns
then close the window and save changes. Then try and open PartOrdered.
The error message (Error 3066) "Query must have at least one
destination field." pops up and the SQL for the query is now "SELECT ;"

The problem occurs for me on any query defined as SQL rather than in
the design mode. This seems pretty serious to me, how to fix it? I'm
using Access 2002 SP3.

Hopefully someone tries this or knows how to solve the problem!
Thanks in advance!

/tim

Hi, Tim -

I'm relieved to see that someone else has experienced something similar to
what *I* went through!

I, too, have had queries "magically" disappear. But, mine disappeared in a
different fashion. They disappeared when I was executing a query (or, trying
to, that is) in an mdb file sitting on a shared server on our company's
network. One query had complex field names in the SELECT line of the query.
Perhaps that was too difficult for the program to process in a client/server
mode. I don't know. But I *do* know that once I moved the .mdb to my PC's
hard drive and ran it from there, I had no further problems (so far).

Maybe it *is* better to be lucky than good. In any case, I'll stick with
running the app locally until I can figure out what the *real* cause of the
problem was.

Turning to your situation, I would recommend prefacing each of your filed
name in the SELECT line with the name of their parent table. Like,
"SELECT Part.Id, Part.Name, Part.ItemId FROM Part etc........"

Maybe Access is getting a touch confused.


Good Luck,
Chuck