ADODB.Recordset is returned as null from C# dll

Question

I have created a C# dll to be used in MS Access. All works well on my dev machine. The C# dll has methods with return type as ADODB.Recordset. I have bound the returned recordset with MS Access forms and it works great on my development machine.

The problem is when I install the dll using an Installer on a different machine and run MS Access application. There is no error, but it always returns a null recordset. I have checked the references in MS Access and it is referencing my custom dll and Microsoft ActiveX Data Objects 2.1 Library and there is no error in calling the C# methods. The other methods which return arrays and string are working fine, only methods with ADODB.Recordset as the return type have this problem.

My Development Machine: Windows Vista Service Pack Service Pack 2 Testing Machine: Windows 7 Professional Edition

The code for converting datatable to ADOD.Recordset in C# dll is as follows,

private Recordset ConvertToRecordset(DataTable inTable)
{
    ADODB.Recordset result = new ADODB.Recordset();
    result.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

    ADODB.Fields resultFields = result.Fields;
    System.Data.DataColumnCollection inColumns = inTable.Columns;

    foreach (DataColumn inColumn in inColumns)
    {
        resultFields.Append(inColumn.ColumnName
              , TranslateType(inColumn.DataType)
              , inColumn.MaxLength
              , inColumn.AllowDBNull ? ADODB.FieldAttributeEnum.adFldIsNullable :
                                         ADODB.FieldAttributeEnum.adFldUnspecified
              , null);
    }

    result.Open(System.Reflection.Missing.Value
              , System.Reflection.Missing.Value
              , ADODB.CursorTypeEnum.adOpenStatic
              , ADODB.LockTypeEnum.adLockOptimistic, 0);

    foreach (DataRow dr in inTable.Rows)
    {
        result.AddNew(System.Reflection.Missing.Value,
                      System.Reflection.Missing.Value);

        for (int columnIndex = 0; columnIndex < inColumns.Count; columnIndex++)
        {
            resultFields[columnIndex].Value = dr[columnIndex];
        }
    }

    return result;
}

private DataTypeEnum TranslateType(Type columnType)
{
    switch (columnType.UnderlyingSystemType.ToString())
    {
        case "System.Boolean":
            return ADODB.DataTypeEnum.adBoolean;

        case "System.Byte":
            return ADODB.DataTypeEnum.adUnsignedTinyInt;

        case "System.Char":
            return ADODB.DataTypeEnum.adChar;

        case "System.DateTime":
            return ADODB.DataTypeEnum.adDate;

        case "System.Decimal":
            return ADODB.DataTypeEnum.adCurrency;

        case "System.Double":
            return ADODB.DataTypeEnum.adDouble;

        case "System.Int16":
            return ADODB.DataTypeEnum.adSmallInt;

        case "System.Int32":
            return ADODB.DataTypeEnum.adInteger;

        case "System.Int64":
            return ADODB.DataTypeEnum.adBigInt;

        case "System.SByte":
            return ADODB.DataTypeEnum.adTinyInt;

        case "System.Single":
            return ADODB.DataTypeEnum.adSingle;

        case "System.UInt16":
            return ADODB.DataTypeEnum.adUnsignedSmallInt;

        case "System.UInt32":
            return ADODB.DataTypeEnum.adUnsignedInt;

        case "System.UInt64":
            return ADODB.DataTypeEnum.adUnsignedBigInt;

        case "System.String":
        default:
            return ADODB.DataTypeEnum.adVarChar;
    }
}

I pass a DataTable to the above mentioned method like this and return the result:

ADODB.Recordset instanceRS = ConvertToRecordset(instancesDT);
return instanceRS;

One more time: the dataset returned on my development machine is fine and all the records are populated, but on testing machine it is always null. I have read somewhere that Microsoft ActiveX Data Objects 2.1 Library for Windows 7 has to do something with it, but I can't find anything more specific. Is this right? How can I fix this?

Answer 1

Is this an ActiveX control? If so are you sure your install program is registering the activex dll?

You need to register the activex dll on the local machine.

(For example a google search shows this)