Can Access link to Cognos and Great Plains data sources?

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.
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
Steve EndowConnect With a Mentor Microsoft MVP - Dynamics GPCommented:
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.


Steve Endow
Microsoft MVP - Dynamics GP
Dynamics GP Certified IT Professional
PatHartmanConnect With a Mentor Commented:
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.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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'.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.