Solved

Catastrophic Failure on Access VB ADO UPDATE

Posted on 2008-06-12
11
1,600 Views
Last Modified: 2013-12-20
I have an Access database with tables linked to SQL Server.  I'm using Access as a front-end for forms with Visual Basic.

Using an ADODB connection, I am getting an error executing an UPDATE SQL statement.  The error is:
Run-time error '-2147418113 (8000ffff)':
Catastrophic failure

The weird thing is, if I hit "Continue" on the debugger, the code executes the UPDATE (verified in the DB) and continues on as expected.

Code is below.  Printing the contents of stSQL immediately before the error produces the following in the Immediate window:
UPDATE tblInvestment SET FundID = 1, ClassID = 7, SeriesID = 83, InitialLiquidityID = 1, MaintLiquidityID = 1, MaintLiquidityOffset = 0, LiquidityGate = 0.75, RedNoticeMths = 1, InvestmentDate = '8/1/2008', OFACInd = -1, FATFInd = False, ModDate = '6/12/2008 9:08:30 AM', ModUser = 'userone' WHERE InvestorID = 884

Any help will be much appreciated.
Dim dblocal As ADODB.Connection

    Dim rst As ADODB.Recordset

    Dim stSQL As String
 

    Set dblocal = CurrentProject.Connection

    Set rst = New ADODB.Recordset
 

                stSQL = ""

                stSQL = "SELECT InvestmentID FROM tblInvestment WHERE InvestorID = " & cboInvestor.Value & " AND FundID = " & cboFund.Value

                stSQL = stSQL & " AND ClassID = " & cboClass.Value & " AND SeriesID = " & cboSeries.Value

                rst.Open stSQL, dblocal, adOpenDynamic, adLockOptimistic

                

                If rst.BOF = False Or rst.EOF = False Then

                    If txtInvestmentID.Value > 0 Then 

                    Else 

                        txtInvestmentID.Value = rst.Fields("InvestmentID")

                    End If

                    

                    rst.Close

                    

                    stSQL = ""

                    stSQL = "UPDATE tblInvestment SET FundID = " & cboFund.Value & ", ClassID = " & cboClass.Value & ", SeriesID = " & cboSeries.Value

                    stSQL = stSQL & ", InitialLiquidityID = " & cboInitLiq.Value & ", MaintLiquidityID = " & cboMaintLiq.Value & ", MaintLiquidityOffset = "

                    stSQL = stSQL & txtLiqOffset & ", LiquidityGate = " & txtLiqGate.Value & ", RedNoticeMths = " & txtNoticeMnths.Value

                    stSQL = stSQL & ", InvestmentDate = '" & txtTransDate.Value & "', OFACInd = " & chkOFAC.Value & ", FATFInd = " & chkFATF.Value

                    stSQL = stSQL & ", ModDate = '" & txtModDate.Value & "', ModUser = '" & txtModUser.Value & "' WHERE InvestorID = " & cboInvestor.Value
 
 

                    dblocal.Execute stSQL '<----------  Catastrophic failure here
 
 

' Code continues with else condition, etc.

Open in new window

0
Comment
Question by:dskoln
  • 6
  • 5
11 Comments
 
LVL 10

Expert Comment

by:Clif
ID: 21769834
From the research I have done, I would suggest it is either bad/corrupt jet (OLEDB or ODBC) provider files or something in the way you are opening the connection object.

The file problem might be solved by reinstalling the files from here:
http://www.microsoft.com/downloads/results.aspx?productID=&freetext=jet+4.0+sp7&DisplayLang=en

Or you could let me take a look at how you're opening your connection object.

0
 

Author Comment

by:dskoln
ID: 21769977
Is this the connection object info you're looking for?

?CurrentProject.Connection.ConnectionString
Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Dev_DB.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\UserAppData\user\Microsoft\Access\System.mdw;Jet OLEDB:Registry Path=Software\Microsoft\Office\11.0\Access\Jet\4.0;Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False
0
 
LVL 10

Expert Comment

by:Clif
ID: 21770027
Yes, that would be it.

Let me do some reserch and see if there's an invalid flag.
0
 

Author Comment

by:dskoln
ID: 21770034
Great.  Thanks!
0
 
LVL 10

Expert Comment

by:Clif
ID: 21770644
Ok, I'm sorry to say this is the bext I could come up with...

http://bytes.com/forum/thread517173.html

Please check out the response by Allen Browne (right after the initial post).  Perhaps it can be of help.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:dskoln
ID: 21770724
I will try out some of the suggestions in that link.  

Since everything seems to work if I hit continue, is there a way to catch the error and re-execute that one line as a work-around?  Kind of like "On Error Resume", but just for the one dblocal.Execute command instead of the entire procedure.  Try...Catch is up the alley I'm thinking about, but VB6 doesn't seem to support it.
0
 
LVL 10

Expert Comment

by:Clif
ID: 21770806
Well, I suppose you could do something like this:

Err_Loop:
  dblocal.Execute stSQL
On Error Goto Err_Loop

At least that would be very simple.  I would certainly add a counter so it would not go into an endless loop, and test for Err.Number = 0 Or Err.Number = -2147418113 to make sure it's only the odd error that gets through.  Anything else you can think of to make sure that the kluge doesn't go off the deep end.
0
 

Author Comment

by:dskoln
ID: 21771061
Love the idea, but its not catching the error.  See updated code below, but still geting  the catastrophic failure.  

Immediate window shows ErrCounter = 0 at the time of the error, leading me to believe the GoTo Err_Loop didn't work.  

Tried "On Error GoTo Err_Loop"  in both the location below and directly after the "End If" at the end of the code with the same results.
Dim dblocal As ADODB.Connection

    Dim rst As ADODB.Recordset

    Dim stSQL As String

    Dim ErrCounter As Integer ' <------------- New Line

 

    Set dblocal = CurrentProject.Connection

    Set rst = New ADODB.Recordset
 

    ErrCounter = 0 ' <------------- New Line
 

 

                stSQL = ""

                stSQL = "SELECT InvestmentID FROM tblInvestment WHERE InvestorID = " & cboInvestor.Value & " AND FundID = " & cboFund.Value

                stSQL = stSQL & " AND ClassID = " & cboClass.Value & " AND SeriesID = " & cboSeries.Value

                rst.Open stSQL, dblocal, adOpenDynamic, adLockOptimistic

                

                If rst.BOF = False Or rst.EOF = False Then

                    If txtInvestmentID.Value > 0 Then 

                    Else 

                        txtInvestmentID.Value = rst.Fields("InvestmentID")

                    End If

                    

                    rst.Close

                    

                    stSQL = ""

                    stSQL = "UPDATE tblInvestment SET FundID = " & cboFund.Value & ", ClassID = " & cboClass.Value & ", SeriesID = " & cboSeries.Value

                    stSQL = stSQL & ", InitialLiquidityID = " & cboInitLiq.Value & ", MaintLiquidityID = " & cboMaintLiq.Value & ", MaintLiquidityOffset = "

                    stSQL = stSQL & txtLiqOffset & ", LiquidityGate = " & txtLiqGate.Value & ", RedNoticeMths = " & txtNoticeMnths.Value

                    stSQL = stSQL & ", InvestmentDate = '" & txtTransDate.Value & "', OFACInd = " & chkOFAC.Value & ", FATFInd = " & chkFATF.Value

                    stSQL = stSQL & ", ModDate = '" & txtModDate.Value & "', ModUser = '" & txtModUser.Value & "' WHERE InvestorID = " & cboInvestor.Value

 

 

                    On Error GoTo Err_Loop
 

Err_Loop:

                    If ErrCounter < 3 And (Err.Number = 0 Or Err.Number = -2147418113) Then 'Catch Catastrophic Failure

                        dblocal.Execute stSQL  ' <------------------- Catastrophic Failure still happens here, ErrCounter = 0

                        If ErrCounter > 0 Then

                            MsgBox ("Catastrophic Error Occured") 'Debugging, let me know error happened, entered loop

                        End If

                        ErrCounter = ErrCounter + 1

                    End If

 

 

' Code continues with else condition, etc.

Open in new window

0
 
LVL 10

Accepted Solution

by:
Clif earned 500 total points
ID: 21771357
Wow, it's a little more complex than I thought...

I am posting this at the risk of having the programming gods assail me with lightening bolts, but here goes...

(Replace your error trapping routine with the following...
    On Error Resume Next

    

Err_Loop:

    If ErrCounter < 3 And (Err.Number = 0 Or Err.Number = -2147418113) Then 'Catch Catastrophic Failure

        dblocal.Execute stSQL '(My test code used: Err.Raise -2147418113)

        If Err.Number = 0 Then

            On Error GoTo 0 '<--- Reset error trapping so a random 

                            '  error else where won't come back here

            GoTo Jump_Out

        ElseIf Err.Number = -2147418113 Then

            ErrCounter = ErrCounter + 1

            GoTo Err_Loop

        End If

    End If

Jump_Out:

Open in new window

0
 

Author Comment

by:dskoln
ID: 21771586
Its not pretty, but it works.  Thanks for all your help!!!
0
 
LVL 10

Expert Comment

by:Clif
ID: 21771618
Trust me, I hated writing it as much as you hate using it.  :)

Thanks for the points.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now