dskoln
asked on
Catastrophic Failure on Access VB ADO UPDATE
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.
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.
ASKER
Is this the connection object info you're looking for?
?CurrentProject.Connection .Connectio nString
Provider=Microsoft.Jet.OLE DB.4.0;Use r ID=Admin;Data Source=C:\Dev_DB.mdb;Mode= Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\UserAppData\us er\Microso ft\Access\ System.mdw ;Jet OLEDB:Registry Path=Software\Microsoft\Of fice\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
?CurrentProject.Connection
Provider=Microsoft.Jet.OLE
Yes, that would be it.
Let me do some reserch and see if there's an invalid flag.
Let me do some reserch and see if there's an invalid flag.
ASKER
Great. Thanks!
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.
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.
ASKER
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.
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.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Its not pretty, but it works. Thanks for all your help!!!
Trust me, I hated writing it as much as you hate using it. :)
Thanks for the points.
Thanks for the points.
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.