creating Access db with ADOX

Question

I'm trying to create an Access database consisting of two tables. I'm getting System.Runtime.InteropServices.COMException on line 88 inside the following method. The exception occurs when I try to append a table to the Catalog object. Could someone please explain what's wrong and how to fix this?

public bool CreateNewAccessDatabase(string fileName)
    {
        bool result = false;

        ADOX.Catalog cat = new ADOX.Catalog();
        ADOX.Table provTable = new ADOX.Table();
        ADOX.Key provKey = new ADOX.Key();
        ADOX.Table locTable = new ADOX.Table();
        ADOX.Key locKey = new ADOX.Key();
        ADOX.Column provCol = new Column();
        ADOX.Column locCol = new Column();


        //Create the Province table and it's fields. 
        provTable.Name = "Provinces";
        provCol.Name = "id";
        provCol.Type = ADOX.DataTypeEnum.adInteger;
        provTable.Columns.Append(provCol);            
        provTable.Columns.Append("name", ADOX.DataTypeEnum.adVarWChar, 4);

        provKey.Name = "Primary Key";
        provKey.Columns.Append("id");
        provKey.Type = KeyTypeEnum.adKeyPrimary;

        //Create the Locations table and it's fields
        locTable.Name = "Locations";
        locCol.Name = "id";
        locCol.Type = ADOX.DataTypeEnum.adInteger;
        locTable.Columns.Append(locCol);
        locTable.Columns.Append("name", ADOX.DataTypeEnum.adVarWChar, 50);
        locTable.Columns.Append("price", ADOX.DataTypeEnum.adVarWChar, 8);

        locKey.Name = "Primary Key";
        locKey.Columns.Append("id");
        locKey.Type = KeyTypeEnum.adKeyPrimary;

        try
        {
            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + "; Jet OLEDB:Engine Type=5");

            // Must create database file before applying autonumber to column
            provCol.ParentCatalog = cat;
            provCol.Properties["AutoIncrement"].Value = true;

            locCol.ParentCatalog = cat;
            locCol.Properties["AutoIncrement"].Value = true;

            cat.Tables.Append(provTable);  // <<< Exception triggered here
            cat.Tables.Append(locTable);

            //Now Close the database
            ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
            if (con != null)
                con.Close();

            result = true;
        }
        catch (Exception ex)
        {
            Debug.WriteLine(ex.StackTrace);
            result = false;
        }
        cat = null;
        return result;
    }

score 2 · Accepted Answer · answered Jan 09 '15 at 18:51

2

You are declaring the text fields as ADOX.DataTypeEnum.adVarChar but all Access Text fields are capable of storing Unicode so you need to declare them as ADOX.DataTypeEnum.adVarWChar.

answered Jan 09 '15 at 18:51

Gord Thompson

116,920
32
215
418

OK. I changed the datatype as requested, however I still get the same exception. – Alan Jan 09 '15 at 22:21
@Alan You missed the first one: `provTable.Columns.Append("name", ADOX.DataTypeEnum.adVarChar, 4);` – Gord Thompson Jan 09 '15 at 22:34

creating Access db with ADOX

1 Answers1