?
Solved

Can Access link to Cognos and Great Plains data sources?

Posted on 2015-01-13
3
Medium Priority
?
457 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 38

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

770 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