Runtime Error 3000 (1 Viewer)

pr2-eugin

Super Moderator
Local time
Today, 02:12
Joined
Nov 30, 2011
Messages
8,494
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
Code:
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
Code:
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
Code:
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

☠
Local time
Yesterday, 23:12
Joined
May 15, 2014
Messages
1,122
did you try viewing it in sql view and going to design view? that sometimes points it out xD
 

BlueIshDan

☠
Local time
Yesterday, 23:12
Joined
May 15, 2014
Messages
1,122
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

AWF VIP
Local time
Today, 02:12
Joined
Jan 22, 2010
Messages
26,374
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

Super Moderator
Local time
Today, 02:12
Joined
Nov 30, 2011
Messages
8,494
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. :confused:
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 :eek:. 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 :mad:

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

Have been here a while
Local time
Today, 03:12
Joined
Jun 17, 2012
Messages
7,732
...I am not sure which table could be corrupted, the involved table roughly has 37000+ records/63 columns across the two tables :eek:. 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

Super Moderator
Local time
Today, 02:12
Joined
Nov 30, 2011
Messages
8,494
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 ! :D
 

vbaInet

AWF VIP
Local time
Today, 02:12
Joined
Jan 22, 2010
Messages
26,374
Good to hear Paul :D

With regards the root cause, I would replace these three parts with a WHERE clause in an attempt to pinpoint the problem:
Code:
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

Super Moderator
Local time
Today, 02:12
Joined
Nov 30, 2011
Messages
8,494
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.
 

pr2-eugin

Super Moderator
Local time
Today, 02:12
Joined
Nov 30, 2011
Messages
8,494
Okay ! Successfully managed to muck up :D

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

AWF VIP
Local time
Today, 02:12
Joined
Jan 22, 2010
Messages
26,374
Did you try including one of the fields from the Attachment field?
 

pr2-eugin

Super Moderator
Local time
Today, 02:12
Joined
Nov 30, 2011
Messages
8,494
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 :confused:
 

vbaInet

AWF VIP
Local time
Today, 02:12
Joined
Jan 22, 2010
Messages
26,374
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?
 

Users who are viewing this thread

Top Bottom