Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / MS Access Integration ...
Microsoft Dynamics GP (Archived)

MS Access Integration with Microsoft Dynamics Great Plains

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello Dynamics GP Community,

I would like to transfer / migrate invoices stored in a custom MS Access application into the Microsoft Dynamics Great Plains (GP) tables automatically.

I did a short search on the internet and there are apparently multiple ways to accomplish this such as Great Plains Integration Manager, eConnect and possibly even the Web Service API?

http://www.streetdirectory.com/travel_guide/2125/computers_and_the_internet/microsoft_great_plains_and_microsoft_access_integration.html


https://msdn.microsoft.com/en-us/library/cc508699.aspx

I am curious which method / API is optimal for my situation? The version of Great Plains is 2010 and the data resides in a MS Access .accdb file.

Thank you for any advice.

*This post is locked for comments

  • Suggested answer
    Harold Worby Profile Picture
    Harold Worby 1,325 on at
    RE: MS Access Integration with Microsoft Dynamics Great Plains

    If it is a one time thing for the initial load then IM is definitely the fastest and least amount of work. For the other options you would need to develop a custom application.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: MS Access Integration with Microsoft Dynamics Great Plains

    Hi Beat,

    I decided I will complete the MS Access solution first and then tackle the Dynamics GP API Solution.

    This discussion was important for me because I was considering creating ODBC links to some of the Dynamics GP tables directly and by-passing an API but based on this conversation I have ascertained that the API is always the way to go even for something as simple as changing a Customer Address.

    Consequently, I will build all the tables and fields in MS Access and when the solution is complete I will see which data fields need to be migrated to GP via API and which are unique to the custom Access app.  At this point I will post back and report back which API solution I actually went with.

    I appreciate all the comments and feedback.  It was very helpful in formulating the above strategy.  I anticipate I will report back in 1-3 months.

    Thank you.

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: MS Access Integration with Microsoft Dynamics Great Plains

    You're Welcome.. :-)

    Please mark any of the replies as being the solution to your question if that closed the thread, so it shows as 'Answered' in the forum.

    good luck with your project.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: MS Access Integration with Microsoft Dynamics Great Plains

    Thank you Beat for the suggestions.  I will investigate those solutions today.

    Appreciate it.

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: MS Access Integration with Microsoft Dynamics Great Plains

    Hi,

    You scenarios looks more as a candidate for SmartConnect from eOne Solutions or Scribe Insight. Both offer automation with multiple type of data sources (Access, XML Excel, SQL tables, CSV files, etc). The already provide full scheduled automation and are using the standard eConnect library to create documents in GP.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: MS Access Integration with Microsoft Dynamics Great Plains

    Hi Leslie,

    Thank you for your response.

    I do not think we own the Integration Manager but if necessary we can purchase it.

    In terms of Technical Expertise I am a MS Access / VBA expert but my knowledge of GP is limited.

    It sounds like you are recommending to do this with Integration Manager Leslie.  I just spoke to a colleague who built what I am trying to accomplish in MS Access and he too used Integration Manager however he did not automate it.  

    Another colleague who uses MS Access with SQL Server Backend told me he uses e-connect stored procedures to perform the migration.

    Leslie you mentioned combining Integration Manager and e-connect adapter as a solution but Tim segregated those two options.  I am little bit confused how the two fit together?

    More background about what I am trying to accomplish.

    We will built a Custom MS Access Application with a MS Access front-end and MS Access backend.  Everyday 5-15 invoices will be generated in the MS Access application and they will have to be pushed into Dynamics GP overnight.  We may also push 5 new customers / week on average into Dynamics GP and potentially modify existing Customer records such as Company Name, Telephone number etc...

    I am not certain if we will manage SalesLineItems in Dynamics as this can be done in the Custom MS Access application.  I believe all I really need to push into Dynamics from the custom application is the Customer Data and the Accounts Receivable (Invoices).

    I do have an ODBC connection setup for all the Dynamics Tables and can modify / add records through MS Access via ODBC.

    Thank you for any comments, feedback or advice.

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: MS Access Integration with Microsoft Dynamics Great Plains

    Hi,

    You can automate Integration Manager using a free Microsoft product called AutoDim. You can then use Microsoft scheduler to execute the program. This will give you full error checking. If you use the eConnect adapter it is very fast. What is your volume and level of technical expertise? More importantly - do you own the Integration Manager module?

    Kind regards,

    Leslie

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: MS Access Integration with Microsoft Dynamics Great Plains

    Thank you Tim.

    Full automation is the objective with Batch migration happening daily at midnight.

    I have done something exactly like this with Simply Accounting using a third party product but it's not automated.  A user has to manually set it up and click buttons.

    The solution I am seeking would be a nightly fully automated task done in VBA using the appropriate API calls as I don't think understanding the business logic and table structure is a sound investment.

    Thank you.

  • Almas Mahfooz Profile Picture
    Almas Mahfooz 11,003 User Group Leader on at
    RE: MS Access Integration with Microsoft Dynamics Great Plains

    Are you familiar with Dynamics GP tables and Dynamics GP itself? If No, you should get hire someone to do the job.

  • Suggested answer
    Tim Foster Profile Picture
    Tim Foster 8,515 on at
    RE: MS Access Integration with Microsoft Dynamics Great Plains

    Your question as to which method is "optimal" depends on which criteria  you are setting as most important.

    Low initial investment = Integration Manager = No automation and poor error handling.

    Potential automation, better error handling = eConnect or Web Service = potentially higher initial investment.

    All of the solutions you mention will work.  There are third party products that can make things easier: SmartConnect for eOne or Scribe Insight.

    Tim

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans