Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Can Access link to Cognos and Great Plains data sources?

Posted on 2015-01-13
3
Medium Priority
?
470 Views
Last Modified: 2015-01-28
Hi all

Can Access link to Cognos and Great Plains data sources?  
I'm reading a project plan somebody dumped in my lap to make design decisions, and need to verify this, with a '20 words or less...' of what to do if it's anything different then a standard Linked Table Manager process.  

Thanks in advance.
Jim
0
Comment
Question by:Jim Horn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points
ID: 40546667
I can't speak to Cognos but I have linked to Great Plains and Timberline.  SAP generally doesn't allow any direct connections.  You have to create an export that gets refreshed every day and then import that.

Since your application tables are in a separate database than those of the ERP, joins are expensive so keep that in mind as you are designing interfaces.  You will also probably not be allowed to update the ERP.
0
 
LVL 18

Accepted Solution

by:
Steve Endow earned 1000 total points
ID: 40549429
Hi Jim,

MS Access can link directly to the MS SQL Server databases used by Dynamics GP.  You can use either the same ODBC DSN that GP uses, or you can create a direct connection to SQL.

A few caveats:

1. You would have to setup a new dedicated SQL login or use a domain login, and you would have to grant access to the appropriate databases and tables in SQL.  I would recommend not using the DYNGRP role used by Dynamics GP, as that provides full access to the GP tables.  Better to create custom database roles and grant read only access only to the tables that you need.  At most, you could use the db_datareader role.

2. I personally do not recommend using access to query a live Dynamics GP database.  Standard MS Access queries (from the designer) are messy, inefficient, and implement table locking which can cause chaos in GP.  I had a client with over 30 GP users where GP would lock up for all users simultaneously--it took us two weeks to figure out that a salesperson was using Access to query the GP database directly, locking several tables for several minutes as it ran a horribly inefficient query.

If you absolutely must use Access, I recommend only querying a copy of your live data, either on a separate server, or on copies of the GP databases (just have IT restore a backup to a different db name).  I also recommend manually scripting any queries to ensure they are efficient, and also include the WITH (NOLOCK) table hint to prevent locks.

Thanks,

Steve Endow
Microsoft MVP - Dynamics GP
Dynamics GP Certified IT Professional
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 40575838
Guys - The project went away, so I have no means of verifying that the above statements are correct, so I'll spread the wealth with a 'thank you'.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question