Updating Accpac views in VB .NET

[SMI168]

Hi there, I'm trying to update Accpac view from my VB .NET code but seem to be getting an error : Error HRESULT E_FAIL has been returned from a call to a COM component. This error appears on the ARCUSTOMER51header.Update() statement. I'm not sure what i'm doing wrong here. Please help. Below is my code :

Imports System
Imports System.IO
'Imports Microsoft.SqlServer.Management.Common
'Imports Microsoft.SqlServer.Management.Smo
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms
Imports ACCPAC.Advantage


Public Class Form1
Public session As Session = Nothing
Public mDBLinkCmpRW As DBLink = Nothing

Dim ARCUSTOMER51header As ACCPAC.Advantage.View
Dim ARCUSTOMER51headerFields As ACCPAC.Advantage.ViewFields

Dim ARCUSTOMER51detail As ACCPAC.Advantage.View
Dim ARCUSTOMER51detailFields As ACCPAC.Advantage.ViewFields

Dim ARCUSTSTAT52 As ACCPAC.Advantage.View
Dim ARCUSTSTAT52Fields As ACCPAC.Advantage.ViewFields

Dim ARCUSTCMT3 As ACCPAC.Advantage.View
Dim ARCUSTCMT3Fields As ACCPAC.Advantage.ViewFields
Public Sub InitializeDBLink()
mDBLinkCmpRW = session.OpenDBLink(DBLinkType.Company, DBLinkFlags.ReadWrite)
End Sub
Public Sub OpenCustomer()
'ARCUSTOMER1header.Compose(New ACCPAC.Advantage.View() {ARCUSTOMER1header})

ARCUSTOMER51header = mDBLinkCmpRW.OpenView("AR0024")
ARCUSTOMER51headerFields = ARCUSTOMER51header.Fields

ARCUSTOMER51detail = mDBLinkCmpRW.OpenView("AR0400")
ARCUSTOMER51detailFields = ARCUSTOMER51detail.Fields

ARCUSTSTAT52 = mDBLinkCmpRW.OpenView("AR0022")
ARCUSTSTAT52Fields = ARCUSTSTAT52.Fields

ARCUSTCMT3 = mDBLinkCmpRW.OpenView("AR0021")
ARCUSTCMT3Fields = ARCUSTCMT3.Fields

'Dim vh As ACCPAC.Advantage.View() = {ARCUSTOMER1header}
'Dim vd As ACCPAC.Advantage.View() = {ARCUSTOMER1detail}

'ARCUSTOMER1header.Compose(vh)
'ARCUSTOMER1detail.Compose(vd)
End Sub
Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click

'Create session to accpac...
session = New Session()

'Initialise session and login...
session.Init("", "XX", "XX0001", "55")
session.Open("USER1", "ACCPAC", "CLDDAT", System.DateTime.Now, 0)

InitializeDBLink()
OpenCustomer()


Dim temp As Boolean


temp = ARCUSTSTAT52.Exists
ARCUSTSTAT52.Init()

ARCUSTSTAT52Fields.FieldByName("IDCUST").SetValue("TEST519", False) ' Customer Number
ARCUSTSTAT52Fields.FieldByName("CNTYR").SetValue("2010", False) ' Year
ARCUSTSTAT52Fields.FieldByName("CNTPERD").SetValue("03", False) ' Period

ARCUSTSTAT52Fields.FieldByName("YTDACTIVE").SetValue("1", False) ' Enable YTD Calculations
temp = ARCUSTOMER51header.Exists
ARCUSTOMER51header.Init()
temp = ARCUSTSTAT52.Exists
ARCUSTSTAT52.Init()

ARCUSTSTAT52Fields.FieldByName("CNTYR").SetValue("2010", False) ' Year

ARCUSTSTAT52Fields.FieldByName("CNTPERD").SetValue("03", False) ' Period
temp = ARCUSTOMER51header.Exists
ARCUSTOMER51header.Init()
temp = ARCUSTSTAT52.Exists
ARCUSTSTAT52.Init()

ARCUSTSTAT52Fields.FieldByName("CNTYR").SetValue("2010", False) ' Year
ARCUSTSTAT52Fields.FieldByName("CNTPERD").SetValue("03", False) ' Period

ARCUSTOMER51headerFields.FieldByName("IDCUST").SetValue("TEST519", False) ' Customer Number
temp = ARCUSTOMER51header.Exists

ARCUSTOMER51headerFields.FieldByName("NAMECUST").SetValue("This is a test customer 05", False) ' Customer Name

ARCUSTOMER51headerFields.FieldByName("IDGRP").SetValue("TEST", False) ' Group Code
ARCUSTOMER51headerFields.FieldByName("TEXTSNAM").SetValue("Test Short 05", False) ' Short Name
ARCUSTOMER51headerFields.FieldByName("TEXTSTRE1").SetValue("Line 1 05", False) ' Address Line 1
ARCUSTOMER51headerFields.FieldByName("TEXTSTRE2").SetValue("Line 2", False) ' Address Line 2
ARCUSTOMER51headerFields.FieldByName("TEXTSTRE3").SetValue("Line 3", False) ' Address Line 3
ARCUSTOMER51headerFields.FieldByName("TEXTSTRE4").SetValue("Line 4", False) ' Address Line 4
ARCUSTOMER51headerFields.FieldByName("NAMECITY").SetValue("City", False) ' City
ARCUSTOMER51headerFields.FieldByName("CODECTRY").SetValue("Country", False) ' Country
ARCUSTOMER51headerFields.FieldByName("CODESTTE").SetValue("Province", False) ' State/Prov.
ARCUSTOMER51headerFields.FieldByName("TEXTPHON1").SetValue("Telephone", False) ' Phone Number
ARCUSTOMER51headerFields.FieldByName("CODEPSTL").SetValue("Postal", False) ' Zip/Postal Code
ARCUSTOMER51headerFields.FieldByName("TEXTPHON2").SetValue("Fax", False) ' Fax Number
ARCUSTOMER51headerFields.FieldByName("EMAIL2").SetValue("E-Mail", False) ' E-mail
ARCUSTOMER51headerFields.FieldByName("WEBSITE").SetValue("Web", False) ' Web Site
ARCUSTOMER51headerFields.FieldByName("NAMECTAC").SetValue("Test", False) ' Contact Name
ARCUSTOMER51headerFields.FieldByName("CTACPHONE").SetValue("Contact Tel", False) ' Contact's Phone
ARCUSTOMER51headerFields.FieldByName("CTACFAX").SetValue("Contact Fax", False) ' Contact's Fax
ARCUSTOMER51headerFields.FieldByName("EMAIL1").SetValue("Contact E-mail", False) ' Contact's E-mail

ARCUSTOMER51headerFields.FieldByName("PAYMCODE").SetValue("EFT", False) ' Payment Code
ARCUSTOMER51headerFields.FieldByName("CODEDAB").SetValue("CR123", False) ' Credit Bureau Number
ARCUSTOMER51headerFields.FieldByName("CODEDABRTG").SetValue("1", False) ' Credit Bureau Rating
ARCUSTOMER51headerFields.FieldByName("DATEDAB").SetValue(DateTime.Now, False) ' Credit Bureau Date

ARCUSTOMER51headerFields.FieldByName("CODETAXGRP").SetValue("VAT", False) ' Tax Group
ARCUSTOMER51headerFields.FieldByName("CODESLSP1").SetValue("DEVRON", False) ' Salesperson code


ARCUSTOMER51header.Update()

'Else
' MessageBox.Show("Customer record Not Updated", "Click OK to continue", MessageBoxButtons.OK, _
' MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
'End If

Try

'Display a message that the record was added...
MessageBox.Show("Customer record Updated", "Click OK to continue", MessageBoxButtons.OK, _
MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
Catch SqlExceptionErr As SqlException
MessageBox.Show("ACCPAR ERROR", "ACCPAC IE Customers", MessageBoxButtons.OK, _
MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
End Try


DisposeConnection()

End Sub
Public Sub DisposeConnection()
mDBLinkCmpRW.Dispose()
session.Dispose()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub
End Class

 

[Bluejay07]

Hi SMI168,

It seems like you are trying to duplicate the instance from a macro (Macros add a lot of additional information that is not required, such as 'temp'). Please keep in mind that the majority of Sage Accpac code is written in VB6 and not VB.net.

In addition, you will need to pay attention to the version of Accpac that is or will be used. Some of the view have changes depending if you are using 5.3, 5.4, 5.5 or 5.6.

FYI, I'm not sure if you are aware, version 6.0 will be 100% internet based and the code is based in part on the Google Web Toolkit and is called the Accpac Web Toolkit.

From your code, it looks like you are using code to insert a customer. If you wish to update a record, I believe you first need to access the desired record (eg. Browse / Fetch) then you can perform an update.

I have some code written a while back, written in COMAPI, that may help. The project it belongs to was never completed, so I cannot guarantee its performance.

   Private Function Update_CustomerDetails(ByVal pCustNum As String) As Boolean
      '// Save the edited customer details.

      'Dim ARCUS As AccpacCOMAPI.AccpacView
      Dim ARCUSO As AccpacCOMAPI.AccpacView
      Dim ARCUSCompose() As Object = {ARCUSO}
      Dim ARCUSOCompose() As Object = {ARCUS}
      Dim l_strARVersion As String

      Try
         '// Check for required information.
         If StillNeed_RequiredInfo() Then
            Exit Function
         End If

         '// Get the Accpac AR version number.
         l_strARVersion = Trim(AP_ModuleVersion("AR"))

         '// Set and compose the views.
         Select Case l_strARVersion
            Case "53A", "54A"
               ENIGDBLINK.OpenView("AR0024", ARCUS)
               ENIGDBLINK.OpenView("AR0400", ARCUSO)
               ARCUS.Compose(ARCUSCompose)
               ARCUSO.Compose(ARCUSOCompose)
            Case Else
               ENIGDBLINK.OpenView("AR0024", ARCUS)
         End Select

         '// Customer information.
         With ARCUS
            .Init()
            .Browse("IDCUST = " & pCustNum, True)
            If .Fetch Then

               .Fields.Item("IDCUST").PutWithoutVerification(Trim(txtCustNum.Text))   '// Customer Number.
               .Read()

               '// Customer Information.
               .Fields.Item("IDCUST").Value = Trim(txtCustNum.Text)                    '// Customer Number.
               .Fields.Item("TEXTSNAM").Value = Trim(txtShortName.Text)                '// Short Name.
               .Fields.Item("NAMECUST").Value = Trim(txtCustName.Text)                 '// Customer Name.
               .Fields.Item("TEXTSTRE1").Value = Trim(txtAddr1.Text)                   '// Address Line 1.
               .Fields.Item("TEXTSTRE2").Value = Trim(txtAddr2.Text)                   '// Address Line 2.
               .Fields.Item("TEXTSTRE3").Value = Trim(txtAddr3.Text)                   '// Address Line 3.
               .Fields.Item("TEXTSTRE4").Value = Trim(txtAddr4.Text)                   '// Address Line 4.
               .Fields.Item("NAMECITY").Value = Trim(txtCity.Text)                     '// City.
               .Fields.Item("CODESTTE").Value = Trim(txtProv.Text)                     '// Province / State.
               .Fields.Item("CODECTRY").Value = Trim(txtCountry.Text)                  '// Country.
               .Fields.Item("CODEPSTL").Value = Trim(txtCode.Text)                     '// Postal/Zip Code.
               .Fields.Item("TEXTPHON1").Value = Trim(txtPhone.Text)                   '// Phone Number (Text Box).
               .Fields.Item("TEXTPHON2").Value = Trim(txtFax.Text)                     '// Fax Number   (Text Box).
               .Fields.Item("NAMECTAC").Value = Trim(txtContact.Text)                  '// Contact Name.
               .Fields.Item("EMAIL1").Value = Trim(txtContactEmail.Text)               '// Contact's Email.
               .Fields.Item("EMAIL2").Value = Trim(txtCustomerEmail.Text)              '// Customer's Email.

               '// Customer tax information.
               .Fields.Item("IDGRP").Value = Trim(txtGroupCode.Text)                   '// Group Code.
               .Fields.Item("IDACCTSET").Value = Trim(txtAccountSet.Text)              '// Account Set.
               '.Fields("CODECURN").value = Trim(lblCurrency.Caption)                 '// Currency Code.
               .Fields.Item("PRICLIST").Value = Trim(txtPriceList.Text)                '// Customer Price List.
               .Fields.Item("CODETERM").Value = Trim(txtTermsCode.Text)                '// Terms Code.
               .Fields.Item("CODETAXGRP").Value = Trim(txtTaxGroup.Text)               '// Tax Group.
               .Fields.Item("CUSTTYPE").Value = cboCustType.SelectedIndex              '// Customer Discount Type.

               'If CSCOMPANY.Multicurrency Then
               '   If Trim(pzeRateType.Text) <> "" Then
               '      .Fields("IDRATETYPE").Value = Trim(pzeRateType.Text)            '// Rate Type.
               '   End If
               'End If

               dgvTax.CommitEdit(DataGridViewDataErrorContexts.Commit)

               If dgvTax.Rows(0).Cells(1).Value <> "" Then
                  .Fields.Item("TAXSTTS1").Value = dgvTax.Rows(0).Cells(1).Value       '// Tax Class Code 1.
               End If

               If dgvTax.Rows(1).Cells(1).Value <> "" Then
                  .Fields.Item("TAXSTTS2").Value = dgvTax.Rows(1).Cells(1).Value       '// Tax Class Code 2.
               End If

               If dgvTax.Rows(2).Cells(1).Value <> "" Then
                  .Fields.Item("TAXSTTS3").Value = dgvTax.Rows(2).Cells(1).Value       '// Tax Class Code 3.
               End If

               If dgvTax.Rows(3).Cells(1).Value <> "" Then
                  .Fields.Item("TAXSTTS4").Value = dgvTax.Rows(3).Cells(1).Value       '// Tax Class Code 4.
               End If

               If dgvTax.Rows(4).Cells(1).Value <> "" Then
                  .Fields.Item("TAXSTTS5").Value = dgvTax.Rows(4).Cells(1).Value       '// Tax Class Code 5.
               End If

               .Fields.Item("IDTAXREGI1").Value = dgvTax.Rows(0).Cells(3).Value        '// Tax Registration Number 1.
               .Fields.Item("IDTAXREGI2").Value = dgvTax.Rows(1).Cells(3).Value        '// Tax Registration Number 2.
               .Fields.Item("IDTAXREGI3").Value = dgvTax.Rows(2).Cells(3).Value        '// Tax Registration Number 3.
               .Fields.Item("IDTAXREGI4").Value = dgvTax.Rows(3).Cells(3).Value        '// Tax Registration Number 4.
               .Fields.Item("IDTAXREGI5").Value = dgvTax.Rows(4).Cells(3).Value        '// Tax Registration Number 5.

               .Update()

            Else
               MessageBox.Show("Customer Number does not exist." & ControlChars.CrLf & _
                "Please use Customer Finder.", "Cannot Find Customer", MessageBoxButtons.OK, _
                MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
            End If

         End With

         Update_CustomerDetails = True

         SaveToolStripMenuItem.Enabled = False
         tsbSave.Enabled = False


         MessageBox.Show("Save and update successful.", "Save successful", MessageBoxButtons.OK, _
          MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

      Catch ex As Exception
         ErrorACCPAC(Err.Number, Err.Description, "frmCustomer.Update_CustomerDetails")
      End Try
   End Function

I'm not sure if you are aware, however, a Sage Accpac forum also exists.

http://www.tek-tips.com/threadminder.cfm?pid=631

I hope some of this information has been helpful.