ADOX Multiple-step OLE DB operation generated errors

Question

I have to make a program that turns off all Unicode compression and all "allow zero length" in an access database (.mdb) .

The method for turning off Allow Zero Length works very well. However, the method for turning off Unicode compression does not work at all and returns the following exception:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Any clue on how to solve this ?

private void TurnOffUnicodeCompressionInField(ADOX.CatalogClass catalogClass, String tableName, String field)
{           
    ADOX.Column column = catalogClass.Tables[tableName].Columns[field];
    ADOX.Property prop = column.Properties["Jet OLEDB:Compressed UNICODE Strings"];
    prop.Value = true;
}

private void TurnOffAllowZeroLengthInAllFields(ADOX.CatalogClass catalogClass, String tableName)
{
    foreach (ADOX.Column column in catalogClass.Tables[tableName].Columns)
        column.Properties["Jet OLEDB:Allow Zero Length"].Value = false; 
}

private void MyButton_Click(object sender, EventArgs e)
{
    String filePath = "";
    OpenFileDialog ofd = new OpenFileDialog();
    DialogResult result = ofd.ShowDialog();

    if (result == DialogResult.OK)
    {
         filePath = ofd.FileName; 
         ADOX.CatalogClass catDatabase = new ADOX.CatalogClass();
         catDatabase.let_ActiveConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath);

        // SoftwareTable 
        TurnOffAllowZeroLengthInAllFields(catDatabase,"Software"); 
        TurnOffUnicodeCompressionInField(catDatabase, "Software", "Description");
        TurnOffUnicodeCompressionInField(catDatabase, "Software", "Name");
    }                      
}

This problem also occurs running in Access. Is the SQL suggested in you previous post not suitable? — Fionnuala, Jul 27 '10 at 10:29
I have to use ADOX to remove Allow Zero Length so why not also remove UNICODE compression using ADOX ? too bad it doesn't work as expected..any ideas ? — MadSeb, Jul 27 '10 at 15:13
ADOX is a non-native data abstraction layer and won't necessarily be able to manipulate all the properties of any particular database engine. Jet/ACE is a particularly bad case in that its native data interface layer, DAO, was crippled during the MS ADO-everywhere campaign in the time frame of the Jet 4 release. The unfortunate result is that for nearly everything, you can use DAO, but for a few things, the only support is in ADO. And, yes, it's idiotic and it really sucks. I don't know if the Access team is planning to correct this in the ACE or if some of it has already been fixed. — David-W-Fenton, Aug 05 '10 at 20:09
My guess is that there are two possibilities here. The first is that Access doesn't allow that change once there's data in the table. The second is that there's a string that would be truncated if that change were made. Which it iswould need testing with an empty table, but I suspect it's the first myself. — , Jul 25 '11 at 13:01

Joshua Drake · Accepted Answer · 2011-08-12T18:59:43.700

You should check your strings for characters that do not have appropriate UNICODE values, these can often be introduced when text is copied and pasted from an application like MS Word. Specifically the "smart quotes" often cause issues.

Also take a look at the following thread (although it is in C++) Discussion on ADOX Property Usage in C++.

Are you able to loop through the properties and display their current values?

ADOX Multiple-step OLE DB operation generated errors

1 Answers1