If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"invalid operation" in query
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! |
#2
|
|||
|
|||
"invalid operation" in query
And I forgot, sometimes when I run it, it shuts down with the "Access needs
to close" message. I'm totally stumped! "JoanneB" wrote: 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! |
#3
|
|||
|
|||
"invalid operation" in query
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 -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "JoanneB" wrote: And I forgot, sometimes when I run it, it shuts down with the "Access needs to close" message. I'm totally stumped! "JoanneB" wrote: 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! |
#4
|
|||
|
|||
"invalid operation" in query
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! |
#5
|
|||
|
|||
"invalid operation" in query
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "JoanneB" wrote: 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! |
#6
|
|||
|
|||
"invalid operation" in query
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... "Jerry Whittle" wrote: 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "JoanneB" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|