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! [email protected]

    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 ( ), he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer.

    In The News:

    Synopsys eyes software security firms
    The Hindu
    Synopsys Inc., a Nasdaq-listed firm and a maker of electronic design automation software products, is eyeing acquisitions in the software security space as it plans to repatriate up to $850 million of overseas earnings to take advantage of the proposed ...

    The Register

    Don't fear the software shopkeeper: T&Cs banning bad reviews aren't legal in America
    The Register
    DerbyCon Security vendors are inserting language into their products' terms and conditions that attempt to silence critics, folks attending this year's DerbyCon conference were told on Friday. More and more infosec software makers now include legal ...

    A how-to software guide for federal managers
    What: "Managing custom software development in government when you're not a software engineer," a blog post from 18F's Kaitlin Devine. Why: Custom software development is becoming increasingly aligned with the mission of many government programs.

    Seeking Alpha

    Micro Focus International - The Biggest Software Company You've Never Heard Of
    Seeking Alpha
    MFGP is now a $19bn EV enterprise software business following its acquisition of Hewlett Packard Enterprise's Software division. Trading at an EV of 4.3x TTM revenue and 13.4x TTM EBITDA, the company has pro-forma combined TTM revenues of $4.4bn ...


    IRS software isn't compliant with the MEGABYTE Act, report says
    Despite efforts to improve its software license management, the IRS still doesn't have policies, guidance or tools to be in compliance with federal law, a watchdog found. The Treasury Inspector General for Tax Administration said in a Sept. 18 report ...

    The Sun

    What is Greyball and why is the Uber software so controversial? All you need to know
    The Sun
    TRANSPORT for London has ruled that taxi app Uber will not be able to renew its operating licence after it expires at the end of the month. Among the reasons behind the decision was Uber's use of its Greyball software. Here's what it does and why it's ...
    What is the Greyball software Uber used to avoid the law?Metro
    What is Greyball? Inside the covert software that helped to get Uber banned in
    Greyball: What is the creepy feature that got Uber banned in London?The Independent

    all 900 news articles »


    The college-search software program I want to destroy | Newsday
    Still, as applications begin, I get to see how this process has changed since I applied in the mid-1980s.
    Daniel W. Drezner: Useful software I would like to destroy | The ...Topeka Capital Journal

    all 1 news articles »


    FalconStor Software stock to be delisted Monday from Nasdaq ...
    FalconStor Software Inc.'s stock will be delisted from the Nasdaq Capital Market on Monday, the company disclosed in a government filing.Shares of ...

    and more »

    The Denver Post

    4 things to know about Apple's iOS 11 software update
    The Denver Post
    SUNNYVALE, Calif. — Holding off on upgrading your trusty old iPhone? You won't need a spiffy iPhone 8 in order to get new maps, photos and other features with a free software update Apple began rolling out last week. The iOS 11 update brings a variety ...

    and more »

    Times LIVE

    We'll publish Gupta 'kickback' findings‚ software giant promises
    Times LIVE
    The findings of an investigation into whether software giant SAP paid kickbacks to Gupta-owned companies will be published at the end of October. The German company chaired by Hasso Plattner‚ who owns Fancourt golf estate in George‚ Western Cape‚ ...

    and more »
    Google News

    Microsoft Business Solutions VAR/Partner Selection ? Overview for IT Director/Manager/Controller

    Microsoft Great Plains and Microsoft CRM become more and more... Read More

    Review on QuarkXpress 6.0

    After almost two decades of existence, Quark has become the... Read More

    Microsoft Great Plains 8.0 Brazilian Version ? Overview For International Consultant

    Microsoft Great Plains has substantial mid-market share in the USA... Read More

    A Case Study on Selecting Contract Management Software

    Professional services firm cuts costs and improves productivity with integrated... Read More

    Introduction To ISDN, Part III: PAP

    Introduction To ISDN, Part III: Configuring PPP PAP AuthenticationNow we... Read More

    Things You Can Do With Windows XP!

    Did you ever give a thought to the number of... Read More

    Microsoft Great Plains version 8.5: Upgrade, Customization, VBA, Crystal Reports - Highlights

    Microsoft Great Plains is one of the Microsoft Business Solutions... Read More

    Groove Network. Are you in it?

    If you are in a business that passes documents around... Read More

    Recent Studies Show that 9 out of 10 PCs Are Infected with Spyware

    Spyware and malware are large problems for Internet users today... Read More

    Lotus Domino: Reports and Connectors ? Crystal Reports, XML, ODBC/JDBC, OLE

    Lotus Notes Domino is very efficient in electronic document workflow... Read More

    A Guide To Purchasing Professional XP Icons Online And Enhancing Your Applications

    Icons are used everywhere; right from software applications, to internet... Read More

    OEComplete ? A Personal Information Manager

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

    Software Tools To Help Your Business Sell More

    Sales are all about leverage, because there is only so... Read More

    The Dirt on Screensavers

    Remember back in the days where screensavers were the coolest... Read More

    10 Steps To Secure And Manage Your Passwords

    Passwords protect your most sensitive personal, financial and business information.... Read More

    Manufacturing Solutions for Microsoft Great Plains ? Overview for Consultant

    Microsoft Business Solutions Great Plains has full-featured manufacturing set of... Read More

    Exactly What Does FTP Stand for and What Can it do for Me?

    FTP stands for "file transfer protocol". FTP is basically a... Read More

    Photoshop Files and Formats

    People often ask me: What image file formats will Photoshop... Read More

    Bill of Lading ? Custom Reporting for Microsoft Great Plains ? Overview for Consultant

    Bill of Lading is required report for Logistics and Freight... Read More

    Linux ? Keyboard Or Mouse

    Just stress testing one of the latest Linux distributions. Been... Read More

    Implementing Microsoft CRM: setup and configuration ? notes for IT specialist

    Microsoft Business Solutions CRM is web-based CRM application, deploying all... Read More

    Microsoft CRM or Siebel

    Siebel is traditional CRM market leader, however and mostly due... Read More

    Where to Find Free Fleet Maintenance Software

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

    Microsoft Blues

    With the advent of 'Service Pack 2' for Windows XP... 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