Automate Import functionality

[ajwonder]

Hi All,

I need to migrate master data (for a start) from GP (Great Plains) to Accpac and if data change in GP, update the records in Accpac again.

I've created stored procs in GP, that render the information and insert the data in ODBC tables, that I then manually import from the respective Accpac Form (Customers -> File -> Import -> File type (ODBC) etc etc). This actually works very well, since insert/update is automatically taken care of...

The next step will be to automate the process, but when I record the macro, the procedure only record the open/close of the module and not the actual import or selection on the form.

Is this achievable? Any COMAPI secrets you've encountered?

Thanks in advance...

 

[Jay Converse]

Export/import doesn't work in macros, you have to write your own insert/update code.

 

[ettienne]

Easily done with VBA, though not automated in the sense that it runs automatically.
In VBA you can read data from tables and then add new/update existing records in Sage 300.
The File, Import process is not ideal for this.

Sage 300 Certified Consultant

 

[DjangMan]

Import/Export via automation is a bit of a black box. You're better of writing a COM process to read the information from your tables and create the GL batches as required. It will run faster and you'll have better control over the whole process.

Just for interest sake I came across some old import code. I needed to create a XML file and I have to populate the XML file with some of the file details like the path to the import file. The code is in Delphi but at least you can see what it looks like. The biggest problem is that you run this import process and hope that nothing goes wrong. If it does then you have to figure out where in the file the problem exists and decide if you want to manually restart the import or if you want to rerun your process.

(My vote is to write your own import.)

function TdmAccpac.RunAccpacImport(const aTemplatePath: string; var bMsg: string): boolean;
var
    IE: IImportExport;
    dbLink: IAccpacDBLink;
    AccpacSession: IAccpacSession;
    sResult: widestring;
    pbHandled: WordBool;
    errcnt: Integer;
    s: string;

begin

    pbHandled := False;
    result := False;
    try
        AccpacSession := CoAccpacSession.Create;
        AccpacSession.Init('','XY','XY0001',SYSTEM_MANAGER_VER);
        AccpacSession.Open(dmDB.GetSetting(5,'') , dmDB.GetSetting(6,''), dmDB.GetSetting(2,''), Now,0,'');
        DBLink := AccpacSession.OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE);
    except
        on e:exception do begin
            s := e.message;
            for errcnt := 0 to AccpacSession.Errors.Count - 1 do
                s := s + #10 + AccpacSession.Errors.Item(ErrCnt);

            AccpacSession.Errors.Clear;

            bMsg := 'The following message was reported when trying to connect to Accpac:'+#10+e.message;
            exit;
        end;
    end;

    IE := CoImportExport.Create;
    try
        IE.Open(dbLink);
        IE.SetView('CP0014', '', VIEW_ORDERED_HEADER, EmptyParam);
        IE.SetView('CP0008', 'CP0014', VIEW_DETAIL_ORDERED, EmptyParam);
        IE.SetView('CP0010', 'CP0014', VIEW_DETAIL_ORDERED, EmptyParam);
        IE.SetView('CP0053', 'CP0014', VIEW_DETAIL_ORDERED, EmptyParam);
        IE.SetView('CP0062', 'CP0010', VIEW_DETAIL_ORDERED, EmptyParam);
        IE.SetView('CP0201', 'CP0014', VIEW_DETAIL_ORDERED, EmptyParam);
        IE.SetViewEx('CP0122','CP0014',VIEW_DETAIL_ORDERED,EmptyParam, OPTIONAL_VIEW);
        IE.SetViewEx('CP0125','CP0008',VIEW_DETAIL_ORDERED,EmptyParam, OPTIONAL_VIEW);
        IE.SetViewEx('CP0126','CP0010',VIEW_DETAIL_ORDERED,EmptyParam, OPTIONAL_VIEW);
        IE.ImportAction := IMPORT_INSERT_AND_UPDATE;
        IE.VerifyOnPut := True;
        IE.ExecuteImportScript(aTemplatePath,pbHandled);
        IE.GetExecuteResult(sResult);
        IE.Close;

        if IE.ImpExpStatus <> IE_SUCCESS then begin
            for errcnt := 0 to AccpacSession.Errors.Count - 1 do
                s := s + AccpacSession.Errors.Item(ErrCnt) + #10;
            AccpacSession.Errors.Clear;

            bMsg := 'The following was reported during the import:'+#13+#10+s;
        end
        else begin
            result := True;
            MessageBox(frmMain.Handle, 'The import completed successfullly.', 'Import Complete', MB_ICONINFORMATION or MB_OK);
        end;

    except on
        e: exception do begin
            s := e.message + #10;
            for errcnt := 0 to AccpacSession.Errors.Count - 1 do
                s := s + AccpacSession.Errors.Item(ErrCnt) + #10;

            AccpacSession.Errors.Clear;

            bMsg := 'The following error was reported during the import:'+#10+s;
        end;
    end;
end;

 

[ajwonder]

Thanx DjangMan. I will definitely give it a bash and revert. This forum has always been the best! The support and suggestions are priceless...