SAGE ACCPAC 5.6 PO Macro,Import Purchase Orders from SQL tables

[LucienJarrett]

Greetings,

I was asked by my boss to see what i can do to automate the import of purchase orders using macros. The data comes from a third party application and has been formatted for import into ACCPAC. I have been able to import successfully via ODBC. What i want to do now is to be able to do it successfully via macros.

See below what i have managed to do so far. When i run this nothing seems to happen and i don't see where in the code it tries to connect to the data tables.

Dim Session As New AccpacCOMAPI.AccpacSession

Dim UserName As String
Dim Password As String
Dim Database As String


UserName = "ADMIN"
Password = "ADMIN"
Database = "GASDB"

'Initialise Session
Set Session = AccpacCOMAPI.AccpacSession
Session.Init "", "XY", "XY1100", "56A"
Session.Open UserName, Password, Database, Date, 0, ""


'Views
Dim POPOR1header As AccpacCOMAPI.AccpacView
Dim POPOR1detail1 As AccpacCOMAPI.AccpacView
Dim POPOR1detail1Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1headerFields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail2 As AccpacCOMAPI.AccpacView
Dim POPOR1detail2Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail3 As AccpacCOMAPI.AccpacView
Dim POPOR1detail3Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail4 As AccpacCOMAPI.AccpacView
Dim POPOR1detail4Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail5 As AccpacCOMAPI.AccpacView
Dim POPOR1detail5Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail6 As AccpacCOMAPI.AccpacView
Dim POPOR1detail6Fields As AccpacCOMAPI.AccpacViewFields

Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)


mDBLinkCmpRW.OpenView "PO0620", POPOR1header
mDBLinkCmpRW.OpenView "PO0630", POPOR1detail1
mDBLinkCmpRW.OpenView "PO0610", POPOR1detail2
mDBLinkCmpRW.OpenView "PO0632", POPOR1detail3
mDBLinkCmpRW.OpenView "PO0619", POPOR1detail4
mDBLinkCmpRW.OpenView "PO0623", POPOR1detail5
mDBLinkCmpRW.OpenView "PO0633", POPOR1detail6


Set POPOR1headerFields = POPOR1header.Fields
Set POPOR1detail1Fields = POPOR1detail1.Fields
Set POPOR1detail2Fields = POPOR1detail2.Fields
Set POPOR1detail3Fields = POPOR1detail3.Fields
Set POPOR1detail4Fields = POPOR1detail4.Fields
Set POPOR1detail5Fields = POPOR1detail5.Fields
Set POPOR1detail6Fields = POPOR1detail6.Fields


POPOR1header.Compose Array(POPOR1detail2, POPOR1detail1, POPOR1detail3, POPOR1detail4, POPOR1detail5)

POPOR1detail1.Compose Array(POPOR1header, POPOR1detail2, POPOR1detail4, Nothing, Nothing, POPOR1detail6)

POPOR1detail2.Compose Array(POPOR1header, POPOR1detail1)

POPOR1detail3.Compose Array(POPOR1header, POPOR1detail4)

POPOR1detail4.Compose Array(POPOR1header, POPOR1detail2, POPOR1detail1, POPOR1detail3)

POPOR1detail5.Compose Array(POPOR1header)

POPOR1detail6.Compose Array(POPOR1detail1)


POPOR1header.Order = 1
POPOR1header.Order = 0

POPOR1headerFields("PORHSEQ").PutWithoutVerification ("0") ' Purchase Order Sequence Key

POPOR1header.Init
POPOR1header.Order = 1
'temp = POPOR1detail1.Exists
POPOR1detail1.RecordClear
POPOR1detail3.Init
POPOR1detail2.Init
'temp = POPOR1header.Exists




Exit Sub

ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long

If Errors Is Nothing Then
MsgBox Err.Description
Else
lCount = Errors.Count

If lCount = 0 Then
MsgBox Err.Description
Else
For lIndex = 0 To lCount - 1
MsgBox Errors.Item(lIndex)
Next
Errors.Clear
End If
Resume Next

End If

End Sub

Thank you in advance for any assistance.

 

[ettienne]

Jip, that macro will do nothing.
You need to create an import template and then use the import object...

 

[LucienJarrett]

Hello ettienne,

I have the templates as SQL Tables. Can this still work in a macro import setup? I have updated the tables and imported data successfully before using the import feature in the PO module.

Thank you for your quick response.

 

[ettienne]

Erm sorry... by template I mean Accpac import script.
When you import you will see a Save Script button... that creates the import script. You feed this script to the AccpacImportExport object.
I never use Accpac imports and have never automated one using a macro, because that is the most problematic way if doing imports in my opinion.
However knock yourself out

 

[LucienJarrett]

I think i may have misused the word automate. What i really should have said is we want to have a solution where a designated user(s) can do the imports themselves instead of hounding us to do it.

Is this what you are talking about?

<?xml version="1.0" encoding="utf-8"?>
<AccpacImpExp Action="16" ProgramName="PO1210" Type="IMPORT" Version="532" HasTitleRecord="1"><VIEW ID="PO0620" Type="3" Action="0" SelectedIDs="1 15 16 17 18 19 21 35 37 38 138 151 349 "><TABLE>LI_PO</TABLE></VIEW><VIEW ID="PO0630" Type="9" Action="0" Parent="PO0620" SelectedIDs="1 2 3 9 16 17 18 19 20 21 22 26 43 "><TABLE>Purchase_Order_Lines</TABLE></VIEW><VIEW ID="PO0610" Type="9" Action="0" Parent="PO0620" SelectedIDs="1 2 "><TABLE>Purchase_Order_Comments</TABLE></VIEW><VIEW ID="PO0632" Type="9" Action="0" Parent="PO0620" SelectedIDs="1 2 "><TABLE>Purchase_Order_Requisitions</TABLE></VIEW><VIEW ID="PO0623" Type="8" Action="4096" Parent="PO0620" SelectedIDs="1 2 3 "><TABLE>Purchase_Order_Hdr_Opt__Fields</TABLE></VIEW><VIEW ID="PO0633" Type="8" Action="4096" Parent="PO0630" AllFieldSelected="1"><TABLE>Purchase_Order_Det__Opt__Fields</TABLE></VIEW><DBNAME>DSN=RockySoft_Oasis;UID=dbadmin;PWD=ACCPAC#1;APP=;WSID=CCSQL0003;DATABASE=Rockysoft;</DBNAME><DBTYPE>ODBC</DBTYPE></AccpacImpExp>

 

[ettienne]

The user can open the PO screen and then click File, Import...

 

[LucienJarrett]

I will let the bos know. Thanks for your help.