Catastrophic Error -2147418113

[HardCode]

I have this code below that I am running in a form's code page:

Private Sub UpdateConcept()
 On Error GoTo ErrorTrap

 Dim SQL            As String
     
    SQL = "UPDATE ConceptMaster SET "
    SQL = SQL & "ConceptName = '" & Trim(Me.txtName) & "', "
    SQL = SQL & "ConceptDesc = '" & Trim(Me.txtDescription) & "', "
    SQL = SQL & "ProductNum = " & Me.cboProducts.Column(0) & ", "
    SQL = SQL & "Headline = '" & Trim(Me.txtHeadline) & "', "
    SQL = SQL & "Message = '" & Trim(Me.txtMessage) & "', "
    SQL = SQL & "LegendText = '" & Trim(Me.txtLegend) & "', "
    SQL = SQL & "Study = '" & Me.cboStudy & "', "
    SQL = SQL & "ModifyDate = #" & Format(Now, "M/D/YYYY hh:mm:ss") & "#, "
    SQL = SQL & "ModifiedBy = '" & PUserName & "' "
    SQL = SQL & "WHERE ConceptNumber = " & Me.txtConceptNum & ";"
    
    [b]CurrentProject.Connection.Execute SQL[/b]

ExitPoint:
    Exit Sub
    
ErrorTrap:
    gErrorProcedure = ERR_SOURCE & "UpdateConcept()"
    DoCmd.OpenForm "GenericErrorForm"
 
End Sub

The line in bold ( ...Execute SQL) sometimes throws the error "Catastrophic Error". Since I am set to "Break on All Errors", I just hit F8 a few times and the statement works. This error only happens randomly. All of the values are valid, as it eventually works. However I couldn't distribute the app in this condition. Any ideas?

EDIT: The reason I am executing the SQL in this fashion, and not with DoCmd.RunSQL, is because we disable the warnings with "DoCmd.Setwarning False". That also disables any error from being caught by the error handler. Using CurrentProject.Connection.Execute doesn't pop up the "Are you sure you want to Insert X records ..." warnings, but still allows ADO errors to be trapping in your error handler.

Also, the DB is SQL Server 2000 with tables linked through Access.

 

[quest4]

You might want to check this out:
http://support.microsoft.com/default.aspx?scid=kb;en-us;159220
hth.

 

[HardCode]

I am not using any BLOB data types as stated in that KB article, but since I searched on "Catastrophic Failure, as opposed to Error this time, I found this article which may be more in line of what I am seeing happen, but it states it was fixed in MDAC 2.5. I am using 2.8

 

[ghudson]

I use the CurrentDb().Execute method. Does that help?

CurrentDb().Execute SQL

 

[wavp]

I have this code below that I am running in a form's code page:

Private Sub UpdateConcept()
 On Error GoTo ErrorTrap

 Dim SQL            As String
     
    SQL = "UPDATE ConceptMaster SET "
    SQL = SQL & "ConceptName = '" & Trim(Me.txtName) & "', "
    SQL = SQL & "ConceptDesc = '" & Trim(Me.txtDescription) & "', "
    SQL = SQL & "ProductNum = " & Me.cboProducts.Column(0) & ", "
    SQL = SQL & "Headline = '" & Trim(Me.txtHeadline) & "', "
    SQL = SQL & "Message = '" & Trim(Me.txtMessage) & "', "
    SQL = SQL & "LegendText = '" & Trim(Me.txtLegend) & "', "
    SQL = SQL & "Study = '" & Me.cboStudy & "', "
[B][COLOR=DarkOrange]SQL = SQL & "ModifyDate = '" & Format(Now, "M/D/YYYY hh:mm:ss") & "',"[/COLOR][/B]
    SQL = SQL & "ModifiedBy = '" & PUserName & "' "
    SQL = SQL & "WHERE ConceptNumber = " & Me.txtConceptNum & ";"
    
    [b]CurrentProject.Connection.Execute SQL[/b]

ExitPoint:
    Exit Sub
    
ErrorTrap:
    gErrorProcedure = ERR_SOURCE & "UpdateConcept()"
    DoCmd.OpenForm "GenericErrorForm"
 
End Sub

Trying removing # as shown above.

 

[HardCode]

I did replace the # with single quotes as I was experimenting. Same problem. I'll need to try CurrentDB.Execute.

 

[wavp]

Have a go with this code below:

Private Sub UpdateConcept()
 On Error GoTo ErrorTrap
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim SQL As String
    
    SQL = "UPDATE ConceptMaster SET "
    SQL = SQL & "ConceptName = '" & Trim(Me.txtName) & "',"
    SQL = SQL & "ConceptDesc = '" & Trim(Me.txtDescription) & "',"
    SQL = SQL & "ProductNum = " & Me.cboProducts.Column(0) & ","
    SQL = SQL & "Headline = '" & Trim(Me.txtHeadline) & "',"
    SQL = SQL & "Message = '" & Trim(Me.txtMessage) & "',"
    SQL = SQL & "LegendText = '" & Trim(Me.txtLegend) & "',"
    SQL = SQL & "Study = '" & Me.cboStudy & "',"
    SQL = SQL & "ModifyDate = '" & Format(Now, "M/D/YYYY hh:mm:ss") & "',"
    SQL = SQL & "ModifiedBy = '" & PUserName & "' "
    SQL = SQL & "WHERE ConceptNumber = " & Me.txtConceptNum & ";"
    
    'Run SQL statement
    cnn.Execute (SQL)
    
cnn.Close

ExitPoint:
    Exit Sub
    
ErrorTrap:
    gErrorProcedure = ERR_SOURCE & "UpdateConcept()"
    DoCmd.OpenForm "GenericErrorForm"
 
End Sub