|
Order Free Video Professor
Microsoft Great Plains
Integration with Microsoft Access overview for developer
by: Andrew Karasev
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.
1. 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
2. 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.
3. 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.
4. 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
5. 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.
6. Modifier/VBA custom buttons on the
existing screens - alternative to Dexterity is you are
comfortable with VBA and ADO.
7. 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.
8. 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
About The Author
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.
akarasev@albaspectrum.com
Professional
Background Check
and Investigation |
