cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal reports - Microsoft Access - 64bit OS

Former Member
0 Kudos

Hi,

I have a .NET 1.0/VS 2003 application with Crystal reports that uses a MS Access DB. I'm migrating it to .NET 4.0/VS2010. The 32-bit application works fine, but the 64-bit doesn't. To begin with, I found out that the OLE DB driver that I used doesn't (and wont) exist in 64-bit version, so I installed the recommended "Microsoft Access Database Engine 2010 Redistributable". Windows forms application succesfully connects to DB, but Crystal Reports doesn't - any attempt to display a report in the report viewer component fails with the message "Failed to load database information...". I attached the screenshot, along with CR debugging files.

Here is the code I use to connect Windows forms to the database (the old connection is commented out):


ConnectionString =

    @"Provider=Microsoft.ACE.OLEDB.12.0;Password="""";" +

    //@"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" +

    @"User ID=Admin;Data Source=" +

    DatabasePath + ";Mode=Share Deny None;" +

    @"Jet OLEDB:Database Password=""my_password""";

This is the code I use to connect CR to the database:


foreach (Table t in rpt.Database.Tables)

{

    TableLogOnInfo logOnInfo = new TableLogOnInfo();

    logOnInfo = t.LogOnInfo;

    ConnectionInfo connectionInfo = new ConnectionInfo();

    connectionInfo = logOnInfo.ConnectionInfo;

    connectionInfo.DatabaseName = DatabasePath ;

    connectionInfo.ServerName = DatabasePath ;

    connectionInfo.Password = "my_password";

    t.ApplyLogOnInfo(logOnInfo);

}

  .lyLogOnInfo(logOnInfo);

I must say that due to the lack of documentation I'm not sure if the second piece of code is correct (beyond that it works fine on 32-bit OS) and which DB driver it uses.

I'd appreciate any help (other than to change the DB, which is not possible at the moment).

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

The Crystal designer application is 32-bit, so it requires a 32-bit database connection.  If you only have the 64-bit client installed, that is what is causing the problem.

Also, see Abhilash's responses here: 

-Dell

Message was edited by: Dell Stinnett-Christy

Former Member
0 Kudos

Maybe I haven't been clear enough, I'm not talking about the VS IDE/CR designer, but about the final application (.exe). What I haven't found clearly explained in the CR documentation is weather CR works with 64-bit ACE (since I compile the 64-bit EXE, I suppose all the CR components are 64-bit too).

(I have a variable number od Access .mdb databases, so I connect to them programatically from the application.)

0 Kudos

Hi Marko,

CR only supports the 64 bit Access using ODBC or OLE DB only, JET or ACE is not supported.

Also, to get the 64 bit driver you need to install the 64 bit client which you can download from Microsoft.

MS does not allow having both the 32 and 64 bit ODBC/OLE DB Access drivers installed on the SAME PC.

Once you have that configured this works:

//Create a new Database Table to replace the reports current table.

CrystalDecisions.ReportAppServer.DataDefModel.Table boTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();

CrystalDecisions.ReportAppServer.DataDefModel.Table subboTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

//Get the Database Tables Collection for your report

CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;

boTables = rptClientDoc.DatabaseController.Database.Tables;

// Get the old connection info

oldConnInfos = rptClientDoc.DatabaseController.GetConnectionInfos(null);

boTable.ConnectionInfo = boConnectionInfo;

oldConnInfo = oldConnInfos[0];

# region DAO Access

if (oldConnInfo.Attributes["Database DLL"].ToString() == "crdb_dao.dll")

{

    // Engine

    CrystalDecisions.CrystalReports.Engine.ReportObjects crReportObjects;

    CrystalDecisions.CrystalReports.Engine.SubreportObject crSubreportObject;

    CrystalDecisions.CrystalReports.Engine.ReportDocument crSubreportDocument;

    CrystalDecisions.CrystalReports.Engine.Database crDatabase;

    CrystalDecisions.CrystalReports.Engine.Tables crTables;

    CrystalDecisions.Shared.TableLogOnInfo tLogonInfo;

    btnSQLStatement.Text = "";

    try

    {

        foreach (CrystalDecisions.CrystalReports.Engine.Table rptTable in rpt.Database.Tables)

        {

            tLogonInfo = rptTable.LogOnInfo;

            tLogonInfo.ConnectionInfo.ServerName = @"D:\Atest\482607\Latest\dsTimesheet.xml";

            tLogonInfo.ConnectionInfo.DatabaseName = newDataFile; // D:\Atest\199019\ot_tmp88.mdb

            tLogonInfo.ConnectionInfo.UserID = "";

            tLogonInfo.ConnectionInfo.Password = "";

            tLogonInfo.TableName = rptTable.Name;

            dtStart = DateTime.Now;

            try

            {

                rptTable.ApplyLogOnInfo(tLogonInfo);

            }

            catch (Exception ex)

            {

                MessageBox.Show("ERROR: " + ex.Message);

                //return;

            }

            difference = DateTime.Now.Subtract(dtStart);

            //rptTable.Location = rptTable.Name;

            btnSQLStatement.Text += /*rptTable.Name.ToString() +*/ " Set in " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + ":" + difference.Milliseconds.ToString() + "\n";

        }

    }

    catch (Exception ex)

    {

        MessageBox.Show("ERROR: " + ex.Message);

        //return;

    }

    // check for subreports

...

Note you need to use RAS to get the old connection info.

Don

Former Member
0 Kudos

Comment on "CR only supports the 64 bit Access using ODBC or OLE DB only, JET or ACE is not supported." - aren't ODBC and OLE DB just interfaces to JET/ACE? In this example Microsoft explains OLE DB using JET as an example. Moreover, after installing 64-bit ACE I managed to connect CR to the database by means of an ODBC connection referring to the Access driver. One remaining issue is the fact that my DB is password-protected (not on a per user basis) and I don't know how to integrate the database password in the ODBC connection string.

Additionally, when I retrieve TableLogOnInfo from a table on the report, I find my database path in all the following properties:

  • Attributes[1].Value
  • Attributes[4].Value
  • DatabaseName
  • LogonProperties[0].Value
  • ServerName

Am I supposed to overwrite them all while constructing a DSN-less ODBC connection string?

0 Kudos

Hi Marko,

ODBC may be using the JET in the backend but CR is connecting through the ODBC layer.

Yes overwrite the connection properties.

Test it using a DSN first, Server Name is the DSN name, then build the same connection info as your DSNless one.

Don

Answers (1)

Answers (1)

Former Member
0 Kudos

Unfortunately, the 64-bit application using ODBC doesn't work. The 32-bit app with the same exact connection string works fine.

This is how I changed the code to use ODBC:


      if (!lib.CR_ODBC)

      {

        foreach (Table t in rpt.Database.Tables)

        {

          TableLogOnInfo logOnInfo = new TableLogOnInfo();

          logOnInfo = t.LogOnInfo;

          ConnectionInfo connectionInfo = new ConnectionInfo();

          connectionInfo = logOnInfo.ConnectionInfo;

          connectionInfo.DatabaseName = lib.LokacijaBaze;

          connectionInfo.ServerName = lib.LokacijaBaze;

          //connectionInfo.UserID = "";

          connectionInfo.Password = "*****";

          t.ApplyLogOnInfo(logOnInfo);

        }

      }

      else

      {

        foreach (Table t in rpt.Database.Tables)

        {

          TableLogOnInfo logOnInfo = new TableLogOnInfo();

          logOnInfo = t.LogOnInfo;

          ConnectionInfo connectionInfo = new ConnectionInfo();

          connectionInfo = logOnInfo.ConnectionInfo;

          string MyConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" + lib.LokacijaBaze + ";Uid=Admin;Pwd=;";

          DbConnectionAttributes logonProperties = new DbConnectionAttributes();

          logonProperties.Collection.Set(DbConnectionAttributes.CONNINFO_CONNECTION_STRING, MyConnectionString);

          logonProperties.Collection.Set(DbConnectionAttributes.CONNINFO_DATABASE_DLL, DbConnectionAttributes.DATABASE_DLL_CRDB_ODBC);

          connectionInfo.Attributes.Collection.Set("QE_LogonProperties", logonProperties);

          connectionInfo.Attributes.Collection.Set(DbConnectionAttributes.CONNINFO_CONNECTION_STRING, MyConnectionString);

          connectionInfo.Attributes.Collection.Set(DbConnectionAttributes.CONNINFO_DATABASE_DLL, DbConnectionAttributes.DATABASE_DLL_CRDB_ODBC);

          connectionInfo.Attributes.Collection.Set("QE_DatabaseType", "ODBC (RDO)");

          connectionInfo.Attributes.Collection.Set(DbConnectionAttributes.QE_DATABASE_NAME, "");

          connectionInfo.Attributes.Collection.Set(DbConnectionAttributes.QE_SERVER_DESCRIPTION, "");

          connectionInfo.AllowCustomConnection = true;

          connectionInfo.DatabaseName = "";

          connectionInfo.ServerName = "";

          connectionInfo.Password = "*****";

          t.ApplyLogOnInfo(logOnInfo);

        }

      }}

          connectionInfo.AllowCustomConnection = true;

          connectionInfo.DatabaseName = "";

          connectionInfo.ServerName = "";

          connectionInfo.Password = "******";

          t.ApplyLogOnInfo(logOnInfo);

        }

      }

I installed the app on Windows 7, Windows 8.1 and Windows 10, used Process monitor and CR debugging and the only thing I found interesting is a call to PETestDBlogonCredentialsW which returns an error code 717, at the moment CR returns the error to the user. There's almost no mention of this procedure on the net.

Example of files in %TEMP% dir with the 32-bit app displaying the CR report:


JETC56A.tmp

temp_7844824b-03af-410c-9042-27ec4d6741eb 3160_2236_{27BB8BE0-4523-4066-A1BE-F8F5C72ABA95}.rpt

temp_7844824b-03af-410c-9042-27ec4d6741eb.rpt

~cpe3160_1432_{947C2812-32A4-444C-8657-5C2431DD90B9}.tmp

~cpe3160_1432_{CDDC4503-1527-4DFC-838F-57E441BB6957}.tmp

~DFF57DAAAFA49DD59E.TMP

Example of files in %TEMP% dir with the 64-bit app failing on the CR report:


temp_45e6a8ee-67e2-4cae-81a5-c0b30095a504 2088_1924_{6A1E4C48-79BE-4868-9473-A27DB83D5FBC}.rpt

temp_45e6a8ee-67e2-4cae-81a5-c0b30095a504.rpt

~DF8073F035A3067531.TMP

I also tried to install older versions of CR runtime (from 16 down to 13), tried to install 32 and 64 bit versions together, but nothing helped. I read the info to look for ACCESS DENIED message in Process monitor, but nothing there, just bunch of reading of registry keys and the like.

Any other ideas to make the 64 bit CR work?

0 Kudos

As I noted:

"Also, to get the 64 bit driver you need to install the 64 bit client which you can download from Microsoft."

You cannot have both 32 and 64 bit JET client on the same PC.

Former Member
0 Kudos

Yes I do have 64 bit ACE driver installed (not JET, that one exists only in 32 bit version).

0 Kudos

I don't know then, it works for me....

Clean PC, Installed Office 2013 64 bit version, installed VS 2015 ( should not matter what version )

ran the code above and no errors....

Don

Former Member
0 Kudos

I created a new test 64-bit application where data is added to a report in designer from ODBC data source, this one works fine.

In my original application data (tables) is added to reports in designer from a different data source (ADO), so I suppose the code above has difficulty in changing the data source to ODBC. Also, I ran the ODBC tracing, in this case there's nothing in the log, so I suppose no ODBC calls are being made.

0 Kudos

Ah, in that case you need to use the ReplaceConnection() method to update the report from the Jet to ODBC. Search, lots of samples, KBA's and Documents on how to...

Don

Former Member
0 Kudos

There are numerous examples, but I haven't found a sigle one that works.

Here's what I tried next:


    DatabaseController databaseController = rpt.ReportClientDocument.DatabaseController;

    CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos connectionInfos = databaseController.GetConnectionInfos(null);

    CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnectionInfo = connectionInfos[0];

    //CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnectionInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

    CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnectionInfo = oldConnectionInfo.Clone(true);

    CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag QELogonProperties = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();

    QELogonProperties.Add(DbConnectionAttributes.CONNINFO_CONNECTION_STRING, MyConnectionString);

    QELogonProperties.Add(DbConnectionAttributes.CONNINFO_DATABASE_DLL, DbConnectionAttributes.DATABASE_DLL_CRDB_ODBC);

    QELogonProperties.Add("UseDSNProperties", false);

    QELogonProperties.Add("Trusted_Connection", false);

    CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag QEProperties = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();

    QEProperties.Add("QE_LogonProperties", QELogonProperties);

    QEProperties.Add(DbConnectionAttributes.CONNINFO_CONNECTION_STRING, MyConnectionString);

    QEProperties.Add(DbConnectionAttributes.CONNINFO_DATABASE_DLL, DbConnectionAttributes.DATABASE_DLL_CRDB_ODBC);

    QEProperties.Add("QE_DatabaseType", "ODBC (RDO)");

    QEProperties.Add(DbConnectionAttributes.QE_DATABASE_NAME, "");

    QEProperties.Add(DbConnectionAttributes.QE_SERVER_DESCRIPTION, "");

    QEProperties.Add(DbConnectionAttributes.CONNINFO_SSO_ENABLED, false);

    newConnectionInfo.Attributes = QEProperties;

    newConnectionInfo.UserName = "Admin";

    newConnectionInfo.Password = "";

    newConnectionInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

    databaseController.ReplaceConnection(oldConnectionInfo, newConnectionInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);

As a result, on my DEV computer there's no error and the report is empty, on production computers there's an exception "System.ArgumentException: The parameter is incorrect.  at CrystalDecision..." I tried both creating and cloning newConnectionInfo - the result is the same.

0 Kudos

Database name should not be empty

Don

Former Member
0 Kudos

Do you refer to DbConnectionAttributes.QE_DATABASE_NAME? So, if it can't be left empty, what is to be entered in this attribute for a DNS-less ODBC connection?

0 Kudos

Hi Marko,

The table name from the report info:

// get the DB name from the report

CrystalDecisions.CrystalReports.Engine.Database crDatabase;

CrystalDecisions.CrystalReports.Engine.Tables crTables;

crDatabase = rpt.Database;

crTables = crDatabase.Tables;

int dbx = 0;

foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)

{

    if (crDatabase.Tables.Count != 0)

    {

        CrystalDecisions.Shared.NameValuePair2 nvp2 = (NameValuePair2)rpt.Database.Tables[dbx].LogOnInfo.ConnectionInfo.Attributes.Collection[1];

        btnSQLStatement.Text += "\nRPT Data Source Info: \n" + " Server Name: " + rpt.Database.Tables[dbx].LogOnInfo.ConnectionInfo.ServerName.ToString() + "\n Database Name: " + nvp2.Value.ToString() + "\n Table Name: " + rpt.Database.Tables[dbx].Name.ToString();

        if (rpt.Database.Tables[dbx].LogOnInfo.ConnectionInfo.UserID != null)

            btnSQLStatement.Text += "\n User ID: " + rpt.Database.Tables[dbx].LogOnInfo.ConnectionInfo.UserID.ToString() + "\r\n";

        dbx++;

    }

    else

        btnSQLStatement.Text += "No Data source or not found\r\n";

}

Don

Former Member
0 Kudos

Setting QE_DATABASE_NAME to table name doesn't solve the problem.

The error is "Failed to load database information" and in the CR trace I see the error code 717.

0 Kudos

If you update the report to use ODBC, and make sure you create the DSN in both 32 and 64 bit ODBC Administrator, then just set the Logon info, DSN for the server name and then Table name and no user name or password does that work?

Former Member
0 Kudos

Yes it does, it's just not feasible to do for all the reports because it would require all the reports.

0 Kudos

CR only supports Access using OLE DB or ODBC now. So since it works using ODBC then only option now is to update your reports or possibly using ReplaceConnection() to update them in code.

rptClientDoc.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);

Don