"invalid operation" in query

[JoanneB]

I'm using Access 2003 with XP and seem to have the most recent service pack.
I'm running a series of queries which make a table, then append to it.
Several of the queries use the "unmatched" format, where it checks to see
that the records it selects aren't already in the destination table. Those
are failing and giving me a "invalid operation" error message.

In previous versions of this database, it worked fine. (The data is used for
mailings and I typically copy the old database before tweaking with the new
requirements.) The formats of the tables it uses haven't changed. I tried
creating a brand-new database and importing the queries. It worked for a
while, then started having the same error. I created a new database again and
recreated all the queries from scratch. I relinked to one table (our big
membership table in SQL) and re-imported the text files used for the
selection criteria. Again, it worked for a while, and then started displaying
the error.

I suspected a security upgrade caused the problem, but we've gone back to my
machine setup before that upgrade and the problem continues. Can anyone help?
I've used Access for years and never seen anything like this.

Thanks!

 

[JoanneB]

And I forgot, sometimes when I run it, it shuts down with the "Access needs
to close" message. I'm totally stumped!

 

[Jerry Whittle]

That the queries work for a while then start acting up makes it seem that
there's nothing wrong with the queries themselves. Although it could be a
case where you are getting bad data every once in a while such a nulls that
aren't handled properly. Do you have Set Warning turned off while running
this? If so you might want to turn it back on and see if you get any unusual
messages. It might be a case where the queries are trying to push bad data
forward.

2003 did tighten up a few things with queries. For example you really need
the parameters in parameter queries properly defined if you plan on using
them in a crosstab query later. There may be other things that worked fine in
the past, but not now.

Still almost every time that I've seen Access having to shut down was due to
database corruption or a problem with Office Professional.

1. Have you tried running the queries on a different computer?

2. Below are some resources for corruption issues. If the databases are out
on a network, it could be something wrong there such as a bad network card or
switch.

At times like this, nothing beats a good backup. In fact make a complete
backup of your database now and put it away for safe keeping.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[JoanneB]

Thanks for the suggestions. Oddly enough, I found the problem this morning. I
compared the query that worked, side by side with the one that didn't. The
only difference was that the original query both were based on was structured
differently in the different files.

For the one that worked, the query was made up of a table linked to another
table (all the membership table with any matching records in a smaller
related table). For the one that didn't work, the query came from a single
table (just the membership table). This made absolutely no sense, but when I
changed the base query for the one that didn't work to match the structure of
the other, it worked. In other words, making it more complicated worked. I'm
still shaking my head.

I'll keep your suggestions for future use, though. Can't have too many tools
in the tool box. Thanks for answering!

 

[Jerry Whittle]

Does the single table have lookup fields? They can cause all kinds of
problems. The more complicated query may actually join the lookup table
properly at SQL level instead of internally.

 

[JoanneB]

Oddly enough, no. It's a simple little table with a few fields that hold data
one department needs in relation to the members. The main member table is
read-only to those users, so they needed somewhere to note that particular
members don't want email or to be contacted with fundraising requests.

Since the data I wanted didn't involve email or fundraising, I thought that
table would be unnecessary and simply used the member table. Guess I was
wrong about that! I've used Access for the past 12 years and never seen
anything like this. It's gonna bother me...