Software Information

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.


    MORE RESOURCES:

    BBC News

    Apple unveils software updates, new laptop
    San Francisco Chronicle,  USA - 10 hours ago
    The presentation included updates to the iLife and iWork software suites, a new 17-inch MacBook Pro with an 8-hour battery and a new digital rights ...
    Video: Steve Jobs Weight Loss Due to Hormone Imbalance AssociatedPress
    Macworld goes for the soft sell with budget-minded software USA Today
    Apple's Macworld Surprise Is the Software PC Magazine
    Macworld - The Associated Press
    all 2,649 news articles


    SAP Wants Oracle to Reveal Software, Support Profit Margins
    PC World - 1 hour ago
    SAP wants Oracle to reveal profit-margin information for JD Edwards and PeopleSoft software and support, according to a joint discovery statement filed this ...


    ABC News

    ITunes embraces 3-tier pricing, will remove anti-copy software
    Los Angeles Times, CA - 12 hours ago
    Apple also updated much of its software. The latest version of iPhoto scans for individual faces so computer users can label photos of friends or family, ...
    Apple removes protection software from songs TourDates.Co.Uk
    Apple changes iTunes prices; removes copy protection USA Today
    Apple changes iTunes pricing CNNMoney.com
    Bloomberg - Shiny Shiny
    all 1,147 news articles


    HEB Mexico adds price optimization software
    Bizjournals.com, NC - 2 hours ago
    HEB’s Mexico subsidiary will begin using a price optimization software program currently in use by its parent company. Supermercados Internacionales HEB ...


    Earthtimes (press release)

    IBM Lotus Delivers New "Social" Notes and Free Symphony Software ...
    CNNMoney.com - 21 hours ago
    At Macworld, IBM (NYSE: IBM) today announced the availability of Lotus Notes 8.5 collaboration software with social computing features for all Mac OS X ...
    Macworld: IBM finalizing free Symphony office suite for Macs Computerworld
    Lotus Notes 8.5 ships, free IBM Symphony suite coming MacNN
    IBM unveils Lotus Notes 8.5 VNUNet.com
    eWeek - Computer Business Review
    all 45 news articles


    CBC.ca

    Indian Software Giant Admits Financial Wrongdoing
    Washington Post, United States - 4 hours ago
    The revelation shook India's stock market and sent shock waves across the country's booming software industry, while television commentators quickly dubbed ...
    Satyam Chairman Resigns After Falsifying Accounts Bloomberg
    Chairman of 'India's Enron' Resigns After Falsifying Financial Data FOXNews
    Collateral Damage Feared From 'India's Enron' CNNMoney.com
    Reuters India - Livemint
    all 1,299 news articles


    Plymouth software firm Paisley sold to Thomson Reuters
    Bizjournals.com, NC - 1 hour ago
    Paisley, a maker of risk-management and fraud investigation software, has been sold to Thomson Reuters’ tax and accounting business for an undisclosed ...


    Times Online

    Apple Unveils Software, MacBook At Macworld
    CNNMoney.com - 23 hours ago
    The first hour of the speech was dominated by updates to Apple's software line, which remains an important element for the company, but more as a driver to ...
    Apple rolls out 17-inch MacBook Pro, new software, iTunes Store tweaks DVICE
    Apple unveils raft of new Mac software Times Online
    Apple updates iTunes, iLife, unveils 17-inch MacBook Bizjournals.com
    CNN International - Sydney Morning Herald
    all 276 news articles


    New York Times

    Data Analysts Captivated by R’s Power
    New York Times, United States - 3 hours ago
    R first appeared in 1996, when the statistics professors Robert Gentleman, left, and Ross Ihaka released the code as a free software package. ...


    Save Mart Supermarkets Deploys Integrated Retalix Supply Chain ...
    MSNBC - 17 hours ago
    That's why we turned to Retalix -- for the experience of their personnel and the rich grocery functionality of the company's software. ...
    Save Mart Supermarkets deploys Retalix suite to enhance business ... Trading Markets (press release)
    all 8 news articles

    Software - Google News

    home | site map
    © 2006