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


    Linux Vs. Windows

    This article will not attempt to advocate the use of... Read More

    netstat - Linux Command to Display Networking Information

    In linux, one of great commands for finding out information... Read More

    How To Make Good Use of Spreadsheets

    Most computer users use spreadsheets software such as Microsoft Excel... Read More

    Free Software for Newbies and Web Developers

    Here is some free software tools to help you build... Read More

    Most Dangerous Types of Spyware are on the Rise: How to Choose the Weapon

    Bad News - the Threat is Bigger than it SeemedHow... Read More

    Microsoft Small Business Manager eCommerce ? Overview

    Microsoft Business Solutions Small Business Manager is scaled down Great... Read More

    International Support - Microsoft Great Plains VAR/Partner Selection ? Overview for V.P. IT

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

    Fast Car Knoppix

    Small can be beautiful! Working with Knoppix for the past... Read More

    Microsoft Great Plains e-Commerce ? additional considerations for programmer

    Microsoft Great Plains, designed back in 1990th as database transferable... Read More

    Cisco Certification: Introduction To ISDN, Part IV

    In part III of this ISDN primer, we learned that... Read More

    Microsoft CRM Customization: Integration with Third Party SQL Application/Database

    Microsoft CRM ? Client Relationship Management package from Microsoft Business... Read More

    Create ASP in Minutes

    DBxtra goes ASPGetting to the information hidden within corporate databases... Read More

    How To Create A Data Capture Procedure Checklist For Your Small Business CRM Software

    Fortunately one of the most common reasons cited for the... Read More

    The Top 5 Wrong Reasons For Not Hiring Testers

    Considering whether or not your software company should hire a... Read More

    eStore Advantage ? Extending Microsoft eConnect for MBS Great Plains

    eStore Advantage allows front-office applications to communicate with back-office business... Read More

    Microsoft Office Selecting Secrets

    Stop the Runaway MouseWhat's the "runaway mouse?" You've seen it...you... Read More

    The Tools and Features of Adobe Photoshop

    Adobe Photoshop is one of the world's leading graphics editing... Read More

    Microsoft Great Plains: carpet, textile, fabric, felt distributor ? implementation overview

    In this small article we will show you the possible... Read More

    Crystal Reports For Microsoft Navision - Overview For Programmer/IT Specialist

    Let us give you - developer some hints in the... Read More

    Windows Screensavers Explained

    In this article you will find some background information about... Read More

    When is a Software Engineer Not a Software Engineer?

    The title of "software engineer" has got to be among... Read More

    Adware and Spyware Blockers

    The most important things you can do for your computer... Read More

    SQL scripts for Project Accounting: Microsoft Great Plains series ? overview for developer

    Microsoft Business Solutions Great Plains has Project Accounting module where... Read More

    Microsoft Great Plains Nationwide Remote Support

    ERP Consulting industry is on the way to serve clients... Read More

    Great Plains Dexterity Customization Options ? Overview For Developers

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

    Computer Phones ? Facts and Fallacies

    The stakes are high when considering security, privacy, and savings,... Read More

    OEComplete ? A Personal Information Manager

    OEComplete is a utility for managing the personal information of... Read More

    Microsoft CRM Custom Design & Development: SDK, C#, SQL, Exchange, Integration, Crystal Reports

    Microsoft CRM is new player on the CRM software... Read More

    Mapping Software: Putting Data Visualization on the Map

    Mapping Software Improves Data VisualizationFrom the outset, it is important... Read More

    OS 101: Suggestions for Choosing an Operating System

    An operating system (abbreviated OS) is essentially the path through... Read More

    How to Evaluate Staffing Software

    If you are in the market for new staffing software,... Read More

    Industry Experts Answer the One Million-dollar Question - Why Genie Backup Manager?

    Many reasons made GBM a unanimous choice for experts, one... Read More

    Snort for Network IDS

    What is Snort?Snort is an open source network intrusion detection... Read More