Question Queries suddenly stopped working

[Leelers]

I've built a functioning database that has worked for months. Last night, while an employee was using it, three queries suddenly stopped working. I checked and all of the data still exists in the table, but queries which were fine before are suddenly failing to retrieve the data. Furthermore, one of the forms no longer displays the data fields. If I look at the form in design view, the fields are there, but when I switch to form view, the fields don't show up.

A trojan recently infected my computer, and the Access database is on a shared network. Is it possible that this trojan is altering the way Access performs? Or is there some other explanation? I feel like the employee who was using it last couldn't have done anything to mess up my database this badly.

 

[Rabbie]

Have you run "Compact and Repair" on this DB. If that doesn't work then create a new empty database and copy the items over from your existing DB

 

[Leelers]

No, compact and repair failed to fix the problem. I tried to import it all into a new database and the problem persists. Will I have to rebuild my entire database?

 

[MSAccessRookie]

No, compact and repair failed to fix the problem. I tried to import it all into a new database and the problem persists. Will I have to rebuild my entire database?

Can you identify the particular data item(s) that was/were being entered at the time and check to see if there is anything unusual about them? If not, then can you make a copy of the database, and remove all data items that were added on the day that it failed. The total number of data items will probably not be too high, and if that works, you can add items back until it does not. Perhaps that way you can figure out why a particular item causes it to cease working.

 

[Leelers]

No items that anyone entered should have caused my query to stop working. The employees were working with a simple data-entry form to record the outcome of phone calls. To create the problem that I'm having, someone would have had to directly changed the query or the tables, which no one did.

Here is the code for one of the queries that stopped working:

SELECT [tracking file 2].CNYU_ID, [tracking file 2].StudentFirstName, [tracking file 2].StudentLastName, [tracking file 2].ROUND, [tracking file 2].[Means of Consent], [tracking file 2].CallDate, [tracking file 2].CallTime, [tracking file 2].[Call Outcome], [tracking file 2].[Detailed Callback/Refusal Notes/Completion Notes], [tracking file 2].[Completed By:], [tracking file 2].[Date on which Target consent card sent], [tracking file 2].[Parent Date of Consent], [tracking file 2].[Child Date of Consent], [tracking file 2].[Child Consent Status], [tracking file 2].[Parent Consent Status], [tracking file 2].[Search needed]
FROM [tracking file 2]
WHERE ((([tracking file 2].ROUND)=99) AND (([tracking file 2].CNYU_ID) Not In (SELECT [tracking file 2].CNYU_ID FROM [tracking file 2] WHERE [tracking file 2].ROUND = 5)))
ORDER BY [tracking file 2].CNYU_ID, [tracking file 2].StudentFirstName, [tracking file 2].StudentLastName, [tracking file 2].[Child Consent Status], [tracking file 2].[Parent Consent Status], [tracking file 2].[Search needed];

This query used to return 200-some results, and now returns 0. I broke it into smaller units, ie, having it just return where round=99. That worked, but it seems that the addition of the Not In query stops returning results.

 

[MSAccessRookie]

No items that anyone entered should have caused my query to stop working. The employees were working with a simple data-entry form to record the outcome of phone calls. To create the problem that I'm having, someone would have had to directly changed the query or the tables, which no one did.

Here is the code for one of the queries that stopped working:

SELECT [tracking file 2].CNYU_ID, [tracking file 2].StudentFirstName, [tracking file 2].StudentLastName, [tracking file 2].ROUND, [tracking file 2].[Means of Consent], [tracking file 2].CallDate, [tracking file 2].CallTime, [tracking file 2].[Call Outcome], [tracking file 2].[Detailed Callback/Refusal Notes/Completion Notes], [tracking file 2].[Completed By:], [tracking file 2].[Date on which Target consent card sent], [tracking file 2].[Parent Date of Consent], [tracking file 2].[Child Date of Consent], [tracking file 2].[Child Consent Status], [tracking file 2].[Parent Consent Status], [tracking file 2].[Search needed]
FROM [tracking file 2]
WHERE ((([tracking file 2].ROUND)=99) AND (([tracking file 2].CNYU_ID) Not In (SELECT [tracking file 2].CNYU_ID FROM [tracking file 2] WHERE [tracking file 2].ROUND = 5)))
ORDER BY [tracking file 2].CNYU_ID, [tracking file 2].StudentFirstName, [tracking file 2].StudentLastName, [tracking file 2].[Child Consent Status], [tracking file 2].[Parent Consent Status], [tracking file 2].[Search needed];

This query used to return 200-some results, and now returns 0. I broke it into smaller units, ie, having it just return where round=99. That worked, but it seems that the addition of the Not In query stops returning results.

The query is a little complex, with a subquery referring to the same table as the main query. Perhaps using an alias for each the two instances instead of the Table Name might eliminate any potential for confusion.

In addition, I notice that you have a data column called ROUND, which is a reserved work representing the built-in function Round(). This should probably be changed as a matter of good form.

 

[ASherbuck]

This is a long shot, do you perhaps have Warnings set to false in the coding anywhere? If they are not set back to true then if a query is run and it fails to append records it looks as if it simply doesn't do anything.

Try making a blank form, put a button on it and in the code for the button put:

DoCmd.SetWarnings True

Then view the form click the button and try the query, you should at least get some feedback from access.

 

[Leelers]

I'm not sure what you mean by changing "round." Can you explain that more? I am not a programmer, just a self-taught Access user.

On a whim, I just changed the "not in" clause to a "not exists" clause, and it works again. Why would "not in" work before but not now?

 

[gemma-the-husky]

to me this does sound like some sort of data corruption.

is the front end/back end split?

copy the backend (or the database if its not split), before attempting further recovery

i would examine all data tables for rogue/odd looking data, manually, and try to delete it.

 

[Leelers]

My table is not split, and all of the data looks completely normal when viewed in the tables.

 

[Leelers]

Edit: Replacing "not in" with "not exists" doesn't work. It isn't returning the same results as my "not in" query. I don't understand why this is happening. I have a second database with an identical query and it works fine.

 

[gemma-the-husky]

WHERE ((([tracking file 2].ROUND)=99) AND (([tracking file 2].CNYU_ID) Not In (SELECT [tracking file 2].CNYU_ID FROM [tracking file 2] WHERE [tracking file 2].ROUND = 5)))

i think i would play around with some general design queries, to make sure this bit is definitely resolving correctlly

 

[Leelers]

Yes, I did play with that part. It correctly pulls up round 99, but the "Not In" is failing. But it wasn't failing before. When I play with it, it seems that I can't get any "not in" clause to work anymore.

 

[Leelers]

The database suddenly started working again. I didn't do anything, but it came back today as I was in the middle of fooling around with queries. I have no idea what caused it to disappear or come back.