?
Solved

Can Access link to Cognos and Great Plains data sources?

Posted on 2015-01-13
3
Medium Priority
?
474 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
3 Comments
 
LVL 40

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 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