Solved

Can Access link to Cognos and Great Plains data sources?

Posted on 2015-01-13
3
431 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 35

Assisted Solution

by:PatHartman
PatHartman earned 250 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 250 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 65

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

840 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