Using Actuate to Update the Database

[JLCabrera]

I'm trying to update my database with Actuate 5 using the Textual Query Editor. I can get it to work with a select statement but when I change to an update statement I get the following error.

Status No: 1 :
Basic Error: 1001
Module: afcerror.bas
Line: 137

User error.

Any ideas? I'm not dead set on using this method if there is a better way to do this.

 

[KimberlyBarkman]

Try updating on the start method of the report section instead of the Query Editor. I've never updated, but do insert and delete in several reports. Here's an example with two delete statements.
Sub Start( )

Dim conn As AcDBConnection
Dim DeleteText As String
Dim CommitText As String
Dim BUSN_DT_STRING As String
Dim Delete As AcDBStatement
Dim Commit As AcDBStatement
Dim sBOOK As String
Dim VBKWhereClause As String
Dim SummaryWhereClause As String

'*** Get the connection
Set conn = GetConnection()

'*********************************
'****DATABASE EXECUTION STATEMENTS
'*********************************

'*** Create and Execute the DeleteText Statement
IF pRegressionTest = "Y" then
DeleteText = "DELETE FROM ACTUATE.MIS_PL_TPKG_SUM_GTMP"
DeleteText = DeleteText & chr(10) & " WHERE BUSN_DT <= '" & BUSN_DT_STRING & "' "
ELSE
IF pOVERWRITE_BOOK = "Y" and pBOOK <> "" THEN
DeleteText = "DELETE FROM INFINITY_MGR.MIS_PL_TPKG_SUM_GTMP"
DeleteText = DeleteText & chr(10) & " --if overwrite_book = 'Y' and pBOOK <> '' delete all data for that book list "
DeleteText = DeleteText & chr(10) & " WHERE BUSN_DT = '" & BUSN_DT_STRING & "' "
DeleteText = DeleteText & chr(10) & " and deal_grp_id in "
DeleteText = DeleteText & chr(10) & " (select distinct v.deal_grp_id from "
DeleteText = DeleteText & chr(10) & " infinity_mgr.vbk v "
DeleteText = DeleteText & chr(10) & " where "
DeleteText = DeleteText & chr(10) & " " & VBKWhereClause & " ) "

ELSE
DeleteText = " BEGIN "
DeleteText = DeleteText & chr(10) & " INFINITY_MGR.PKG_TRUNCATE_GTMP_BETA.TRUNC_MIS_PL_TPKG_SUM_GTMP; "
DeleteText = DeleteText & chr(10) & " END; "
END IF
END IF

Set Delete = conn.Prepare( DeleteText )
Delete.Execute()


'*** Create and Execute the CommitText Statement
CommitText = "COMMIT"
Set Commit = conn.Prepare( CommitText )
Commit.Execute()

'*** Create and Execute the DeleteText Statement

IF pRegressionTest = "Y" THEN
DeleteText = "DELETE FROM ACTUATE.MIS_PL_SUMMARY_GTMP"
DeleteText = DeleteText & chr(10) & " WHERE BUSN_DT <= '" & BUSN_DT_STRING & "' "
ELSE
IF pOVERWRITE_BOOK = "Y" and pBOOK <> "" THEN
DeleteText = "DELETE FROM INFINITY_MGR.MIS_PL_SUMMARY_GTMP"
DeleteText = DeleteText & chr(10) & " WHERE BUSN_DT = '" & BUSN_DT_STRING & "' "
DeleteText = DeleteText & chr(10) & " " & SummaryWhereClause
ELSE
DeleteText = " BEGIN "
DeleteText = DeleteText & chr(10) & " INFINITY_MGR.PKG_TRUNCATE_GTMP_BETA.TRUNC_MIS_PL_SUMMARY_GTMP; "
DeleteText = DeleteText & chr(10) & " END; "
END IF

END IF

Set Delete = conn.Prepare( DeleteText )
Delete.Execute()

'*** Create and Execute the CommitText Statement
CommitText = "COMMIT"
Set Commit = conn.Prepare( CommitText )
Commit.Execute()

End Sub