Microsoft Great Plains Integration with Microsoft Access ? Overview for Developer


Microsoft Business Solutions stakes on Microsoft Great Plains as main Accounting/ERP application for US market. At the same time it seems to be staking on Navision in Europe and has Axapta as high end large corporation market competitor to Oracle, PeopleSoft, SAP, IBM. This article is brief review of Microsoft Great Plains integration with Microsoft Access. This is also applicable to Small Business Manager (which is based on the same technology ? Great Plains Dexterity dictionary - DYNAMICS.DIC and runtime DYNAMICS.EXE) and Great Plains Standard on MSDE or MS SQL Server.

If you are developer who is asked: how do we implement Great Plains integration/interface with your MS Access-based system ? read this and you will have the clues on where to look further.

  • Great Plains Integration Manager - this is rather end-user tool - it is very intuitive, it validates 100% of business logic, brings in/updates master records (accounts, employees, customers, vendors. etc.) brings in transactions into work tables. The limitation of Integration Manager - it does use GP windows behind the scenes without showing them - so it is relatively slow - you can bring 100 records - but when you are talking about thousands - it is not a good option. By the way you can program Integration Manager with VBA. Microsoft Access is ODBC compliant and so you can do direct Integration Manager query to MS Access
  • eConnect ? it is type of Software Development Kit with samples in VB.Net. Obviously the development environment should be Visual Studio.Net. eConnect will allow you to integrate master records - such as new customers, vendors, employees, etc., plus you can bring transactions into so called Great Plains work tables (eConnect doesn't allow you to bring open or historical records - you need to post work records in Great Plains, the same limitation applies to Integration Manager above) eConnect is rather for ongoing integration. It was initially created for eCommerce application integration to Great Plains.
  • SQL Stored Procedures. Obviously you have unlimited control and possibilities with SQL queries. You need to know Great Plains tables structure and data flow. Launch Great Plains and go to Tools->Resource Description->Tables. Find the table in the proper series. If you are looking for the customers ? it should be RM00101 ? customer master file. If you need historical Sales Order Processing documents ? they are in SOP30200 ? Sales History Header file, etc. Do not change existing tables - do not create new fields, etc. Also you need to realize that each GP table has DEX_ROW_ID - identity column. Sometimes it is good idea to use inbound/outbound XML in the parameters - then you can deploy web service as a middle party between two systems.
  • Data Transformation Services (DTS) ? Good tool for importing your third party data into staging tables in GP - then you can pull them in using either stored procs of Integration Manager. You can also deploy this tool for EDI export/import. You can have DTS working with Linked Server - SQL Server Construction for linking to Microsoft Access
  • Great Plains Dexterity Custom Screens. Sometimes users prefer to have seamlessly integrated into GP interface custom screens - for parameters settings and initiating integration. Dexterity is a good option, however remember - it is always better to create new custom screen versus customizing existing one - due to the future upgrade issues. Also - Dexterity is in phasing our by Microsoft Business Solutions.
  • Modifier/VBA custom buttons on the existing screens - alternative to Dexterity is you are comfortable with VBA and ADO.
  • SQL Linked Servers ? you can do direct SQL queries to other ODBC compliant platform via SQL Linked Server (including Microsoft Access) - you may need to familiarize yourself with OPENROWSET command in Transact SQL. This is also good option if you need cross-platform Crystal Report - pulling data from SQL Server and third party databases on the same report.
  • Warning - do not place existing GP tables into Replication! - you will have upgrade issues.

    • Happy integrating! if you want us to do the job - give us a call 1-866-528-0577! help@albaspectrum.com

    Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies ? USA nationwide Great Plains, Microsoft CRM customization company, based in Chicago, California, Texas, New York, Florida, Georgia, Minnesota, Oregon, Arizona, Canada, UK, Australia and having locations in multiple states and internationally (www.albaspectrum.com ), he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer.

    In The News:

    pen paper and inkwell


    cat break through


    Software Upgrades Arent Always the Best Move

    When my daughter was getting into AOL instant messaging (AIM)... Read More

    Is Software Tester a Most Infamous Person in a Software Project Team?

    The fact that a software tester is a most infamous... Read More

    Microsoft Great Plains: Annual Enhancement Program ? How To Be Re-Enrolled

    Microsoft Business Solutions Great Plains is mid and even corporate... Read More

    Will Adobe Manage to Replace Industry Work Horse Quark Express by Giving Adobe InDesign for Free?

    And kill the best layout software in the process of... Read More

    Where to Find Free Fleet Maintenance Software

    Costs of fleet maintenance software can vary widely. It is... Read More

    Cross-Platform Custom Software Development & Integration ? IT Strategy for Large Corporation

    Microsoft Business Solutions products: Great Plains, MS CRM, Navision, Axapta,... Read More

    Protect Your Computer...and Your Business!

    We all take the computer for granted. I mean, all... Read More

    Cisco Certification: Introduction To ISDN, Part III

    Configuring PPP PAP AuthenticationNow we know how the ISDN link... Read More

    Groupware: What Works the Way Businesses Do?

    GroupwareThe internet is full of 1.5 million to 7 million... Read More

    S is for Spying, Surveillance -- and for Software as Well

    Words we choose to describe things and phenomena often show... Read More

    Microsoft CRM Integration & Customization: SharePoint Document Gateway

    MS CRM is very close to document workflow automation, including... Read More

    Great Plains Dexterity: Customizations & Source Code Programming

    Great Plains Software Dynamics, Dynamics C/S+, eEnterprise were written on... Read More

    Microsoft Business Solutions Customization Options - Overview for Programmer

    Several years ago Microsoft purchased Great Plains Software, then Navision... Read More

    Microsoft CRM Customization ? Programming Closed Email Activity

    Microsoft CRM is CRM answer from Microsoft and attempt to... Read More

    Microsoft Axapta, Navision or Great Plains: ERP Selection for Large Corporation

    If you would like to pick something from Microsoft, or... Read More

    How To Avoid Getting Hooked By Pfishing

    "Pfishing", sometimes spelled "Phishing", is a word that's used to... Read More

    What is a Document Manager without Version History?

    Document Manager and Version HistoryIn previous articles I have discussed... Read More

    Assertion in Java

    Assertion facility is added in J2SE 1.4. In order to... Read More

    Microsoft CRM in Latin America: Implementation, Customization, Support ? Overview for Consultant

    Microsoft Business Solutions CRM is present several years on the... Read More

    Builders Beware

    Which Type of Shop Can Rely On A Home Built... Read More

    Great Plains Dexterity Programming ? Overview For Developer

    Looks like Microsoft Great Plains becomes more and more popular,... Read More

    My Experience - Making a Vision into Reality

    Disclaimer: All the thoughts expressed are my views only! Your... Read More

    Ukraine: a Prospective Player on the World Software Outsourcing Market

    The destruction of the Soviet Union about 15 years ago,... Read More

    Selecting Corporate ERP: Microsoft Business Solutions Great Plains ? Estimation Parameters

    As we could imagine, if you are reading this article... Read More

    Microsoft CRM: Data Conversion ? Import from Act!

    Best Software Act! is very popular CRM for small and... Read More

    Microsoft Great Plains on Ctree or Pervasive SQL ? What to Do ? Tips for IT Manager

    As you probably know, when Microsoft purchased Great Plains Software... Read More

    Microsoft Great Plains eCommerce ? Stored Procedures Approach

    Since Version 8.0 Microsoft Business Solutions Great Plains & Great... Read More

    Separate Anti-Keylogging Protection: Who Needs it Most?

    If there still are few unprotected computers left, I haven't... Read More

    Two Reasons to Use Timesheet Software

    This short paper will expand on two key reasons to... Read More

    Linux Dual Boot-How To Format

    Just the thought of a duel-boot scares many people away,... Read More

    Internet Faxing Service Review

    The Internet is reshaping every form of communications medium, and... Read More

    Microsoft Navision and Crystal Reports - An Overview

    Microsoft Business Solutions ? Navision is an integrated solution for... Read More

    Microsoft Great Plains customization ? Freight Forwarding/Transportation industry example

    Microsoft Business Solutions Great Plains version 8.5, 8.0, 7.5, Great... Read More