MS Access Integration with Microsoft Dynamics Great Plains

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

Tim Foster 8,515 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Appreciate it.

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Harold Worby 1,331 on at

Like (0)

Report

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.

Was this reply helpful? Yes No