CRM 2011 -- create Access database from CRM schema

Hi apollo7,

It won't be easy... nice timing BTW I might have something ready-to-use in terms of CRM aspect... how comfortable you are with using C# to create an Access DB Schema?

Can you use this code :
https://www.experts-exchange.com/questions/22918432/Create-table-in-a-MS-Access-database-using-C-and-either-ADODB-or-OLEDB-and-then-fill-it-with-data.html

Direct Link : http://www.freevbcode.com/ShowCode.asp?ID=3315


Regards,
Chinmay.

Chinmay,

I have been working on it (Access 2010) and not having much luck.  I have been using a console app to try to create the Access database, Access table and Access schema.

I will take a look at the link and get back today.

Thanks

To create a table:

string s = " [KEYCOLUMN] Int, [VALUECOLUMN] Text ";

//OleDbConnection cnn = bla bla bla ...
cnn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = cnn;
cmd.CommandText = "CREATE TABLE table1(" + s + ")";
cmd.ExecuteNonQuery();
cmd.Connection.Close();

Select allOpen in new window

Hi

I tried adding this to a C# Console App and received a few error messages. They are below:

Error      1      The name 'cnn' does not exist in the current context      
Error      2      The name 'cnn' does not exist in the current context
Error      3      The type or namespace name 'OleDbCommand' could not be found (are you missing a using directive or an assembly reference?)
Error      4      The type or namespace name 'OleDbCommand' could not be found (are you missing a using directive or an assembly reference?)      

Can you give me some additional direction on how this works and what I need to add?

Thanks

You will have to include  the following statements

using System.Data;
using System.Data.OleDb;


Also follow this article : http://mindstick.com/Blog/293/How%20to%20create%20table%20in%20MS%20Ac

Chinmay,

Thanks for the link and advice.  I am getting much closer to this working.  I do have a couple of questions.

First, I have been trying various types of projects to create this functionality (a console app and a class).  If you were coding this, what type of project would you select (I am thinking of using a windows form next with a button to run the code onClick).

Second, I have included the code I am running below and I am receiving 6 errors (same error message) - "Invalid token 'token' in class, struct, or interface member declaration".  I know that this generally means I have something in the wrong place, e.g. an = sign or ')'

The code is attached below.  Any help you can provide is greatly appreciated.

Thanks

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;


namespace CreateAccessTable
{
    class Class1
    {
        // Creating OLEDB connection string for Ms-Access 2007 database file

            OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\\MyDatabase.accdb;Persist Security Info=False;");

            // Open the connection

            myConnection.Open();

            // Create Oledb command to execute particular query 

            OleDbCommand myCommand = new OleDbCommand();

            myCommand.Connection = myConnection;

            // Query to create table with specified data columne

            myCommand.CommandText = "CREATE TABLE tblIdentityTesting([MyIdentityColumn] long, [Name] text)";

            myCommand.ExecuteNonQuery();

            MessageBox.Show("Table Created Successfully");

    }
}

Select allOpen in new window

Hi apollo7,

The code needs to be enclosed within a method in C#.
You can declare variables out side of the method and it is possible to do lot of other things but lets not go in there just yet.

And any given day I will always make a WinForm App or WPF App as it makes my life easy and it is way too simple to tweak stuff there than a console app.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;


namespace CreateAccessTable
{
    class Class1
    {
private void CreateTable()
{
        // Creating OLEDB connection string for Ms-Access 2007 database file

            OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\\MyDatabase.accdb;Persist Security Info=False;");

            // Open the connection

            myConnection.Open();

            // Create Oledb command to execute particular query 

            OleDbCommand myCommand = new OleDbCommand();

            myCommand.Connection = myConnection;

            // Query to create table with specified data columne

            myCommand.CommandText = "CREATE TABLE tblIdentityTesting([MyIdentityColumn] long, [Name] text)";

            myCommand.ExecuteNonQuery();

            MessageBox.Show("Table Created Successfully");
}
    }
}

Select allOpen in new window

Regards,
Chinmay.

Chinmay,

Thanks, I got it to work using a Windows Form - it created the table identified in the sample code you directed me to.

I am now going back to look at the first link you provided.  Now that I can create an Access table from my form, I would like it to now pull the schema from my Accounts entity using a reference to web services.

Any advice you have for this would be also appreciated.

Thanks

Hi apollo7,

How urgently you need this? A friend of mine has this library to extract CRM schema but it will take some time before I get it to you.

Are you working today?

Regards,
Chinmay.

Chinmay

I want to have something by Monday that works to some level.  I am working today and also tomorrow.  Anything you can get me before Monday would be great.

Thanks

Alright.. that sounds urgent.. alright.. dont worry.. lemme give you something right now..

if you can figure it out using that code.. well n good

// Get the name of the entity
// Note: We have to convert this to lower because RetrieveEntityMetadata expects the schema name
string entityName ="contact";

// Get the metadata for the currently list's entity
// This metadata is used to create a "Property Descriptor Collection"
RetrieveEntityRequest mdRequest = new RetrieveEntityRequest ( )
{ EntityFilters = EntityFilters.All,
LogicalName = entityName,
RetrieveAsIfPublished = false
};

// Execute the request
RetrieveEntityResponse entityResponse = ( RetrieveEntityResponse ) this.ServiceProxy.Execute ( mdRequest );

EntityMetadata entityData = entityResponse.EntityMetadata;

Select allOpen in new window

Tty to work with this code, also use SDK.. btw are you comfortable with C# development?

Regards,
Chinmay.

Chinmay

Thanks - I will work with this code and see what I can do.  I am getting better with ASP.NET/C# everyday.  I have been writing plug-ins for about 2 years and have been working with the CRM 2011 SDK for about a year.

Thanks and I will post any problems after I make a thorough effort to work through this myself.

No .. dont spend too much of time... as I mentioned earlier I have the lib just too lazy to get ir right now :P Weekend mode :P I am goin to work tomorrow .. on something.. at that time I will get the lib.. its the same code just bit generic.

Ok, I wont spin my wheels all night :).  It is instructive to try to get something to work on my own but if it is just not happening, I will get some sleep and get the library tomorrow.

Thanks a lot.

No problem.. are you still there? any progress?

No real progress, added a class to my project and trying to connect the code you provided to the windows form created earlier.   Have not figured it out.

Hmm...did you connect to CRM Server?

Chinmay,

This is a process I am fighting with.  To connect my Access database to the CRM server, I am attempting to use the Organization webservice.

I am trying to test the connection using the sample code provided below:

class Test
{
    static void Main()
    {
        OrganizationServiceClient client = new OrganizationServiceClient();

        // Use the 'client' variable to call operations on the service.

        // Always close the client.
        client.Close();
    }
}

Select allOpen in new window

When I click on the url of the webservice, I get the following message:

"You have created a service.

To test this service, you will need to create a client and use it to call the service. You can do this using the svcutil.exe tool from the command line with the following syntax:

svcutil.exe http://10.101.200.200/Apollo/XRMServices/2011/Organization.svc?wsdl"

But if I run this from a command line, it just errors out. I am vpn'ed into the url above, do I need to be on the actual server to run svcutil.exe?

Thanks for any clarification on this.






=

check crm sdk for examples. let me know if you dont find it.

I have checked the SDK for examples and have located one that addresses the Organization Service (code below).  I am working through connecting to the CRM url using this code. I am vpn'ed into a CRM server but continue to receive errors when building or running start without debugging.

Any help I can get on how the CRM url can be passed to my Access table code and then create an Account schema would be great.  Also, if you have the library you discussed previously, that also would be excellent.

The code I am using to connect to the CRM server is below - any advice (including that I am using the wrong approach) would be very appreciated.

Thanks for your help.

using System;
using System.Collections.Generic;
using System.ServiceModel;

// These namespaces are found in the Microsoft.Xrm.Sdk.dll assembly
// found in the SDK\bin folder.
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Discovery;

namespace Microsoft.Crm.Sdk.Samples
{
    /// <summary>
    /// Demonstrates useful messages of the Discovery service.
    /// </summary>
    public class DiscoveryService
    {

        #region Class Level Members

        private DiscoveryServiceProxy _serviceProxy;

        #endregion Class Level Members

        #region How-To Sample Code
        /// <summary>
        /// Demonstrates the RetrieveOrganization and RetrieveOrganizations messages
        /// of the Discovery service.
        /// </summary>
        /// <param name="serverConfig">Contains server connection information.</param>
        /// <param name="promptforDelete">When True, the user will be prompted to delete all
        /// created entities.</param>
        public void Run(ServerConnection.Configuration serverConfig, bool promptforDelete)
        {
            try
            {
                // Connect to the Discovery service. 
                // The using statement assures that the service proxy will be properly disposed.
                using (_serviceProxy = new DiscoveryServiceProxy(serverConfig.DiscoveryUri,
                                                                    serverConfig.HomeRealmUri,
                                                                    serverConfig.Credentials,
                                                                    serverConfig.DeviceCredentials))
                {
                    // You can choose to use the interface instead of the proxy.
                    IDiscoveryService service = _serviceProxy;

                    #region RetrieveOrganizations Message

                    // Retrieve details about all organizations discoverable via the
                    // Discovery service.
                    RetrieveOrganizationsRequest orgsRequest =
                        new RetrieveOrganizationsRequest()
                        {
                            AccessType = EndpointAccessType.Default,
                            Release = OrganizationRelease.Current
                        };
                    RetrieveOrganizationsResponse organizations =
                        (RetrieveOrganizationsResponse)service.Execute(orgsRequest);

                    // Print each organization's friendly name, unique name and URLs
                    // for each of its endpoints.
                    Console.WriteLine();
                    Console.WriteLine("Retrieving details of each organization:");
                    foreach (OrganizationDetail organization in organizations.Details)
                    {
                        Console.WriteLine("Organization Name: {0}", organization.FriendlyName);
                        Console.WriteLine("Unique Name: {0}", organization.UniqueName);
                        Console.WriteLine("Endpoints:");
                        foreach (var endpoint in organization.Endpoints)
                        {
                            Console.WriteLine("  Name: {0}", endpoint.Key);
                            Console.WriteLine("  URL: {0}", endpoint.Value);
                        }
                    }
                    Console.WriteLine("End of listing");
                    Console.WriteLine();

                    #endregion RetrieveOrganizations Message

                    #region RetrieveOrganization Message

                    // Retrieve details about a single organization discoverable via the Discovery service.
                    //
                    RetrieveOrganizationRequest orgRequest =
                        new RetrieveOrganizationRequest()
                        {
                            UniqueName = organizations.Details[organizations.Details.Count - 1].UniqueName,
                            AccessType = EndpointAccessType.Default,
                            Release = OrganizationRelease.Current
                        };
                    RetrieveOrganizationResponse org =
                        (RetrieveOrganizationResponse)service.Execute(orgRequest);

                    // Print the organization's friendly name, unique name and URLs
                    // for each of its endpoints.
                    Console.WriteLine();
                    Console.WriteLine("Retrieving details of specific organization:");
                    Console.WriteLine("Organization Name: {0}", org.Detail.FriendlyName);
                    Console.WriteLine("Unique Name: {0}", org.Detail.UniqueName);
                    Console.WriteLine("Endpoints:");
                    foreach (KeyValuePair<EndpointType, string> endpoint in org.Detail.Endpoints)
                    {
                        Console.WriteLine("  Name: {0}", endpoint.Key);
                        Console.WriteLine("  URL: {0}", endpoint.Value);
                    }
                    Console.WriteLine("End of listing");
                    Console.WriteLine();

                    #endregion RetrieveOrganization Message

                }
            }

            // Catch any service fault exceptions that Microsoft Dynamics CRM throws.
            catch (FaultException<Microsoft.Xrm.Sdk.DiscoveryServiceFault>)
            {
                // You can handle an exception here or pass it back to the calling method.
                throw;
            }
        }

        #endregion How-To Sample Code

        #region Main method

        /// <summary>
        /// Standard Main() method used by most SDK samples.
        /// </summary>
        /// <param name="args"></param>
        static public void Main(string[] args)
        {
            try
            {
                // Obtain the target organization's Web address and client logon 
                // credentials from the user.
                ServerConnection serverConnect = new ServerConnection();
                ServerConnection.Configuration config = serverConnect.GetServerConfiguration();

                DiscoveryService app = new DiscoveryService();
                app.Run(config, true);
            }
            catch (FaultException<Microsoft.Xrm.Sdk.DiscoveryServiceFault> ex)
            {
                Console.WriteLine("The application terminated with an error.");
                Console.WriteLine("Timestamp: {0}", ex.Detail.Timestamp);
                Console.WriteLine("Code: {0}", ex.Detail.ErrorCode);
                Console.WriteLine("Message: {0}", ex.Detail.Message);
                Console.WriteLine("Inner Fault: {0}",
                    null == ex.Detail.InnerFault ? "Has Inner Fault" : "No Inner Fault");
            }
            catch (System.TimeoutException ex)
            {
                Console.WriteLine("The application terminated with an error.");
                Console.WriteLine("Message: {0}", ex.Message);
                Console.WriteLine("Stack Trace: {0}", ex.StackTrace);
                Console.WriteLine("Inner Fault: {0}",
                    null == ex.InnerException.Message ? "Has Inner Fault" : "No Inner Fault");
            }
            catch (System.Exception ex)
            {
                Console.WriteLine("The application terminated with an error.");
                Console.WriteLine(ex.Message);

                // Display the details of the inner exception.
                if (ex.InnerException != null)
                {
                    Console.WriteLine(ex.InnerException.Message);

                    FaultException<Microsoft.Xrm.Sdk.DiscoveryServiceFault> fe = ex.InnerException
                        as FaultException<Microsoft.Xrm.Sdk.DiscoveryServiceFault>;
                    if (fe != null)
                    {
                        Console.WriteLine("Timestamp: {0}", fe.Detail.Timestamp);
                        Console.WriteLine("Code: {0}", fe.Detail.ErrorCode);
                        Console.WriteLine("Message: {0}", fe.Detail.Message);
                        Console.WriteLine("Inner Fault: {0}",
                            null == fe.Detail.InnerFault ? "Has Inner Fault" : "No Inner Fault");
                    }
                }
            }
            // Additional exceptions to catch: SecurityTokenValidationException, ExpiredSecurityTokenException,
            // SecurityAccessDeniedException, MessageSecurityException, and SecurityNegotiationException.

            finally
            {
                Console.WriteLine("Press <Enter> to exit.");
                Console.ReadLine();
            }
        }
        #endregion Main method
    }
}

Select allOpen in new window

Hi apollo7,

Go to this URL :
http://optionsetmaanger.codeplex.com/

Here you get an app that uses CRM Organization service. Now connect to your CRM instance and just check if it is working as expected. After that we will remove whatever extra function it has or we could just create another form on top of it and leave other form as it is.

In this app itself you will see how he is getting the CRM Schema for various entities.
Once you get the schema you will use it to create table. Let me know if you run into any problems.

Sorry I should have provided this URL earlier I was in that impression it is another app.

Regards,
Chinmay.

So where are we?

Chinmay,

I had to travel for the last 4 hours and just got to the hotel.  I will check out http://optionsetmaanger.codeplex.com/ now and let you know what my results are.  Based on your comments, it looks very promising.

Thanks, be back shortly.

No problem. Just thought I will check in as my day begins I might not be available. I will get the developer of this app to help you if you run into some major issue.

Thanks, I appreciate all your help.  I will let you know if I run into any major problems.

Have a good day :)

Hi Apollo7,

Let me know if you come across any trouble using http://optionsetmaanger.codeplex.com/

Regards,
Rikin.

Thanks, I will let you know if I have any issues.

I have a question: when I run the app against my CRM Online account and click Discover, it starts to run and then gives me the following error.  

There is an error in XML document (5, 5)

System.InvalidOperationException: . ---> There is an error in XML document (5, 5) System.Security.Cryptography.CryptographicException: The parameter is incorrect.

Does this indicate something I am entering incorrectly?

Thanks

Hi Apollo7,

Try the following:

Open your user folder (C:\Users\<current user>\LiveDeviceID\) and delete the LiveDevice.xml and try again.

If doesn't work, delete the file again, reboot and try again.

Additional question: if I try to connect to an On- Premise version of CRM, I get past Discover and can select an Organization, but whatever I select, I receive this error:

Metadata contains a reference that cannot be resolved:
http://10.101.200.200/Apollo/XRMServices/2011/Organization.svc"

Is this something I can correct?  Is there something about what I am entering as the credentials causing this? Could it be related to how the On Premise CRM installation is setup?

Thanks

Thanks, trying your suggestion above.

That worked! Deleting the LiveDevice.xml file allows me to progress and select an organization in my online account.  

Will continue testing and let you know if I have any other questions.

Thanks

I am able to connect and use the application for my CRM Online account.  For my On Premise CRM installation, I am still getting the error below:  

Metadata contains a reference that cannot be resolved:
http://10.101.200.200/Apollo/XRMServices/2011/Organization.svc"

Is there something else I can change/delete that will help with this message?

Thanks again.

Can you specify what values you fill in the login dialog?

Yes, they are:

http://10.101.200.200/Apollo

apollo7
password1

Note: I am connected to the on-premise server via a vpn.  Not if that could cause a problem?

Thanks

Alright.

Can you try with below format and let me know the results-
Server: http://10.101.200.200
Domain: Apollo (your current domain name)
Username and password as it is...

Rikin

That worked, thanks again.

Chinmay or Rikin,

Now that I know that the tool works in my environment, how can I complete my original need which to use C# to dynamically create a table in a Microsoft Access database pulling from the CRM 2011 account schema?

I assume I would use parts of the the source code from http://optionsetmaanger.codeplex.com/ to retrieve the schema and I am using code from the link Chinmay provided (http://mindstick.com/Blog/293/How%20to%20create%20table%20in%20MS%20Ac) to write a table to Access 2010 (which works in my environment)

I just need to know where I would link the two sets of code together to retrieve the account schema and then write an account table to Microsoft Access

Thanks

Chinmay,

Thanks for the quick response!  I will give this a try.

Really appreciate your help.

Thanks, this did the trick!  Also very helpful in terms of learning about the web services.