locked
Is there any way to access my cloud based Dynamics CRM database through Access 2007? RRS feed

  • Question

  • Is there a way to link Access 2007 to our tables in Dynamics CRM 4.0? Perhaps via a ODBC? I have some custom reports from Access that I would like to run on customers that exist in our CRM.  Any help would be great!

    Wednesday, December 15, 2010 12:18 AM

Answers

  • ODBC to SQL is the easiest way I've linked Access to CRM tables/views.  From the "cloud", this gets a little tricky, since that "cloud" may not belong to the same domain as the computer connecting, therefore SQL will not authenticate your access with Kerberos (windows integrated).  This means you'll have to create a SQL user, and then grant rights to the appropriate databases/tables/views.  Then there's the matter of exposing SQL to the client computer.  You'll want a VPN, or some other encrypted channel.  I would not recommend, in any way, exposing SQL directly to the Internet without stringent limitations and a signed waiver.

    There is, however, a "hackier" option that I just thought of (and haven't tried, so I don't even know if it would work): export a dynamic spreadsheet from CRM to Excel.  Use that spreadsheet as a datasource in Access.  I think you'll want to play with the data to see whether or not updates from CRM are available in real-time to Access.


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com Please follow the forum guidelines when inquiring of the dedicated CRM community for assistance.
    Friday, December 24, 2010 3:27 AM
    Moderator

All replies

  • I haven't tried it, but you should be able to create Linked tables to the CRM SQL Server tables.

    I would suggest:

    • Using Integrated authentication when creating the ODBC data source
    • Using the Filtered views, eg FilteredContact, FilteredAccount

    so that access to data is restricted via the same mechanisms with which CRM does so via the CRM Web Application.  That is, users of the Access Report will see only data to which they are entitled, via their Roles and Sharing privileges.


    --pogo (pat)
    Wednesday, December 15, 2010 12:46 AM
  • ODBC to SQL is the easiest way I've linked Access to CRM tables/views.  From the "cloud", this gets a little tricky, since that "cloud" may not belong to the same domain as the computer connecting, therefore SQL will not authenticate your access with Kerberos (windows integrated).  This means you'll have to create a SQL user, and then grant rights to the appropriate databases/tables/views.  Then there's the matter of exposing SQL to the client computer.  You'll want a VPN, or some other encrypted channel.  I would not recommend, in any way, exposing SQL directly to the Internet without stringent limitations and a signed waiver.

    There is, however, a "hackier" option that I just thought of (and haven't tried, so I don't even know if it would work): export a dynamic spreadsheet from CRM to Excel.  Use that spreadsheet as a datasource in Access.  I think you'll want to play with the data to see whether or not updates from CRM are available in real-time to Access.


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com Please follow the forum guidelines when inquiring of the dedicated CRM community for assistance.
    Friday, December 24, 2010 3:27 AM
    Moderator