Runtime Error 3000

[pr2-eugin]

I have been using a runtime SQL query in my application, well for about a year or so. I had no problems up until today, somehow the query that normally works has ceased to work. It keeps throwing up

Runtime Error 3000 - Reserved Error (-3087); there is no message for this error

I am a bit puzzled. I have not done any design/code changes on this form (where the SQL is called) for over 5 months. I have just recently ventured into creating a new form with attachments. Changed the Data Structure of the 'Agent' table (which is used in this query) to include one attachment field. Would that make any problem. Either way, this is the Query I am using

SELECT 
Sum(IIf([subQry_outcomeDetail].[PolicyPeriod]='Monthly' AND [subQry_outcomeDetail].[policyStatus]='Cancelled',[subQry_outcomeDetail].[Amount],0)) AS mRev, 
Sum(IIf([subQry_outcomeDetail].[PolicyPeriod]='Quarterly' AND [subQry_outcomeDetail].[policyStatus]='Cancelled',[subQry_outcomeDetail].[Amount],0)) AS qRev, 
Sum(IIf([subQry_outcomeDetail].[PolicyPeriod]='Annually' AND [subQry_outcomeDetail].[policyStatus]='Cancelled',[subQry_outcomeDetail].[Amount],0)) AS aRev, 
[mRev]+[qRev]+[aRev] AS immRev, 
([mRev]*12)+([qRev]*4)+[aRev] AS annRev 
FROM (Transactions LEFT JOIN subQry_outcomeDetail ON Transactions.TRAN_ID = subQry_outcomeDetail.prospectID) INNER JOIN Agents ON Transactions.[Agent ID] = Agents.A_ID 
WHERE Transactions.tranDate Between #07/11/2014# And #07/14/2014#

The SubQuery is

SELECT tbl_Clients.prospectID, tbl_PolicyDetails.InitiatingAgent, tbl_PolicyDetails.policyStatus, 
tbl_PolicyDetails.policyStarted, tbl_PolicyDetails.CancelledDate, tbl_PolicyDetails.FK_clientID, 
tbl_PolicyDetails.policyRefID, tbl_PolicyDetails.PolicyCode, tbl_PolicyDetails.PolicyPeriod, tbl_PolicyDetails.Amount
FROM (tbl_Clients INNER JOIN tbl_PolicyDetails ON tbl_Clients.clientID = tbl_PolicyDetails.FK_clientID) 
INNER JOIN subQry_ClaimsReport ON (tbl_PolicyDetails.FK_clientID = subQry_ClaimsReport.FK_clientID) AND (tbl_PolicyDetails.policyStarted = subQry_ClaimsReport.MaxOfpolicyStarted);

The other subQuery (subQry_ClaimsReport) is

SELECT tbl_PolicyDetails.FK_clientID, Max(tbl_PolicyDetails.policyStarted) AS MaxOfpolicyStarted
FROM tbl_PolicyDetails
GROUP BY tbl_PolicyDetails.FK_clientID;

I am pretty confident there is nothing wrong with the Query as it has served its purpose for a year !

I have Compacted & Repaired, Decompiled ! Still no joy. Not much info on the web too. Any ideas?

 

[BlueIshDan]

did you try viewing it in sql view and going to design view? that sometimes points it out xD

 

[BlueIshDan]

ehow.com

Runtime error 3000 may pop up when you're using Microsoft Access software. The error occurs when the database program is working, but comes across a corrupt file. Frustratingly, you'll likely see the error accompanied by the unhelpful message "There Is No Message for This Error." Thankfully, you do have options for solving this problem.

Read more : http://www.ehow.com/info_12217838_runtime-error-3000.html

Check your latests records for anything that looks like mumbo-jumbo(a corrupt record).

 

[vbaInet]

It might be to do with your innRev or annRev fields. You might need one the CV functions for this. Take both of them out and re-run the query.

I would have actually expected this sort of error on an ADO recordset. But I don't remember.

 

[pr2-eugin]

did you try viewing it in sql view and going to design view? that sometimes points it out xD

Hi Dan, yes I did try that, still the same error. That was my first point of check, since it failed I was a bit unsure.

Check your latests records for anything that looks like mumbo-jumbo(a corrupt record).

I will have a look into this now, I am not sure which table could be corrupted, the involved table roughly has 37000+ records/63 columns across the two tables . So figuring out the bummed up record is going to be a pain. Well its not going to find itself out. :banghead:

It might be to do with your innRev or annRev fields. You might need one the CV functions for this. Take both of them out and re-run the query.

vbaInet, I did that too. I tried removing the immRev and annRev, then also the same. Again just one column still the same error

I am at work now, will try to get into the DB and see if there is something I have mucked up will keep you guys posted ! Cheers for the responses.

 

[JHB]

...I am not sure which table could be corrupted, the involved table roughly has 37000+ records/63 columns across the two tables . So figuring out the bummed up record is going to be a pain.

Do you have some pointers which indicate when a record gets add to the database, (if yes I would delete the "newest" one), or did you get all 37000 records into the database yesterday?
Do you make backup of your data, (then you could run an unmatched query)?

 

[pr2-eugin]

Do you have some pointers which indicate when a record gets add to the database, (if yes I would delete the "newest" one), or did you get all 37000 records into the database yesterday?
Do you make backup of your data, (then you could run an unmatched query)?

Well this is not the end of the world ! As I am only working on my backup copy of the actual MASTER version. So its not all dull and gloom. I always can work back a few days. Start afresh !

Having said that, that is what exactly I did. I replaced my dummy backend with the actual master which did no have an attachment field, or any of the table data structure changes I made a few days ago. Then imported all the objects to a new front end. Everything is back to normal now. Phew ! Now to start investigating the root issue of the problem ! Thanks for all the help !

 

[vbaInet]

Good to hear Paul

With regards the root cause, I would replace these three parts with a WHERE clause in an attempt to pinpoint the problem:

Sum(IIf([subQry_outcomeDetail].[PolicyPeriod]='Monthly' AND [subQry_outcomeDetail].[policyStatus]='Cancelled',[subQry_outcomeDetail].[Amount],0)) AS mRev, 
Sum(IIf([subQry_outcomeDetail].[PolicyPeriod]='Quarterly' AND [subQry_outcomeDetail].[policyStatus]='Cancelled',[subQry_outcomeDetail].[Amount],0)) AS qRev, 
Sum(IIf([subQry_outcomeDetail].[PolicyPeriod]='Annually' AND [subQry_outcomeDetail].[policyStatus]='Cancelled',[subQry_outcomeDetail].[Amount],0)) AS aRev,

I know you'll lose the non matching records but let's see if there's something in the Sum() that could be causing it. And still don't include the other two fields from before.

 

[pr2-eugin]

Sadly since I did a replace and stuff, the error does not seem to be throwing up any more.

I stupidly enough (with the joy of getting it working again) deleted the corrupt file. So I have to wait until I muck up again I think.

 

[vbaInet]

I stupidly enough (with the joy of getting it working again) deleted the corrupt file.

I don't blame you

 

[pr2-eugin]

Okay ! Successfully managed to muck up

I added the attachment field to the Agent table, then not much change closed the front end back end, compacted and repaired (both). Then opened the front end, relinked the back end. Now I try to execute - boom ! Hit with the reserved error. If I attempt to deleted the Agent (attachment) table in the query. I get no error, but the right data.

So I am thinking the problem is something to do with attachments ? Not sure what or why though.

PS: I did try to isolate the problem, by only using the three fields and a WHERE condition. Still no luck.

 

[vbaInet]

Did you try including one of the fields from the Attachment field?

 

[pr2-eugin]

Did you try including one of the fields from the Attachment field?

Should I ? Because I had this code even before introducing the Attachment field in the table, and after inserting I never modified the code. So it should not affect in anyway; but only it does.

Also, this might be a blessing in disguise, as I copied this code from an old template query, so I actually do not need the Agent table to be joined in this specific query anyway. Removing the join and the Agent table has seemed to fix the issue still giving me the actual/right data.

Good it works even with attachment, bad I do not know the reason why this query will throw such error

 

[vbaInet]

It's all for testing purposes. You can try and include any of the fields from the Attachment field to see if it wants something from that field to be included.

What version of Access is it anyway?