Almost every integration that we do between Sage Accpac ERP and 3rd Party Product, on our setup screen we have to display the list of all Accpac companies so that our connector knows which Accpac companies data is getting integrated with the 3rd party Product.
If you are also writing something like this and need to get the list of Accpac companies programmatically and are looking for a solution then this blog would be helpful to you.
Accpac ERP stores the list of all the companies in a file “ORGS.ISAM” located under “< Accpac Installation folder >: Program FilesAccpacSite” folder. You must read this file and store this information in some array or recordset etc. Now the question is that would come to your mind is how to read the file? Well, below code snippet should answer your question.
Script:
Declare following variables and functions at the module level.
‘Org type (database catagory)
Global Const ORG_TYPE_SYSTEM = 0
Global Const ORG_TYPE_COMPANY = 1
‘Security levels
Global Const ORG_SECLVL_NONE = 0
Global Const ORG_SECLVL_DB = 1
Global Const ORG_SECLVL_USER = 2
‘Status
Global Const ORG_ACTIVE = 0
Global Const ORG_ = 1
‘Errors
Global Const ORG_ERROR_SIZE = 1001
Global Const SIZEOF_ORGID = 6
Global Const SIZEOF_ORGDESC = 30
Global Const SIZEOF_DB = 260
Global Const SIZEOF_USERID = 8
Global Const SIZEOF_PASSWORD = 8
Type ORGRecord
wSizeOrg As Integer
sOrgId As String * SIZEOF_ORGID
sDesc As String * SIZEOF_ORGDESC
wOrgType As Integer
sSystemOrgID As String * SIZEOF_ORGID
wDriverID As Integer
sDatabase As String * SIZEOF_DB
wSecLevel As Integer
sSignonID As String * SIZEOF_USERID
sSignonPW As String * SIZEOF_PASSWORD
fSecEnabled As Integer
wStatus As Integer
End Type
Declare Function orgOpen Lib “a4wapi.dll” (reserved As Long, ByRef lHandle As Long) As Long
Declare Function orgClose Lib “a4wapi.dll” (ByVal lHandle As Long) As Long
Declare Function orgGet Lib “a4wapi.dll” (ByVal lHandle As Long, ByRef cmpList As ORGRecord) As Long
Declare Function orgGetGE Lib “a4wapi.dll” (ByVal lHandle As Long, ByRef cmpList As ORGRecord) As Long
Declare Function orgGetNext Lib “a4wapi.dll” (ByVal lHandle As Long, ByRef cmpList As ORGRecord) As Long
‘The below Function is for Getting all companies from Accpac.
Public Function GetCompanyList() As ADODB.Recordset
‘Use this code to get the ORGS records:
Dim CompanyDef As ORGRecord
Dim rotoHandle As Long
Dim lngStatus As Long
Dim strOrgType As String
Dim strDriver As String
‘Set the recordset
Set rsCompanies = New ADODB.Recordset
‘Create new fields in the recordset
With rsCompanies
.Fields.Append “COMPANYID”, adVarChar, 60, adFldIsNullable
.Fields.Append “COMPANYNAME”, adVarChar, 60, adFldIsNullable
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.Open
End With
‘Open ORGS.ISM
lngStatus = orgOpen(0, rotoHandle)
CompanyDef.wSizeOrg = Len(CompanyDef)
CompanyDef.sOrgId = VBA.String(SIZEOF_ORGID, ” “)
orgGetGE rotoHandle, CompanyDef
‘Get rest of the entries
Do ‘orgGetNext(rotoHandle, CompanyDef) = 0 ‘lngStatus = 0
If CompanyDef.wOrgType = 1 Then
rsCompanies.AddNew
rsCompanies.Fields(“COMPANYID”).Value = CompanyDef.sOrgId
rsCompanies.Fields(“COMPANYNAME”).Value = CompanyDef.sDesc
rsCompanies.Update
End If
Loop While orgGetNext(rotoHandle, CompanyDef) = 0
‘Close ORGS.ISM
orgClose (rotoHandle)
Set GetCompanyList = rsCompanies
End Function
About Us
Greytrix is a one stop solution provider for Sage ERP and Sage CRM needs. We provide complete end-to-end assistance for your technical consultations, product customizations, data migration, system integrations, third party add-on development and implementation expertise.
Greytrix has some unique solutions of Sage 300 integration with Sage CRM, Salesforce.com and Magento eCommerce along with Sage 300 Migration from Sage 50 US, Sage 50 CA, Sage PRO, QuickBooks, Sage Business Vision and Sage Business Works. We also offer best-in-class Sage 300 customization and development services to Sage business partners, end users, and Sage PSG worldwide.
For more details on Sage 300 Services, please contact us at accpac@greytrix.com. We will be glad to assist you.