jill taylor

jill taylor

  • NA
  • 1
  • 0

how to change properties on an access table programatically

Apr 9 2007 2:30 PM

I am using c#.net in the visual studio.

I dynamically create a access db named test.mdb and create a table called city.

However all the columns are required.  I found an example (below) to change the field to allow zero length.  It runs with no problems but when I open my access db, it still says required and allow zero lenght to NO. 

anyone have an idea on how to get this working

 

// create table

 

ADODB.Connection Cn = new ADODB.Connection();
catalogCls.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
                      "Data Source=test.mdb;Jet OLEDB:Engine Type=5");

//Open the connection

Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_dbname, "", "", 0);


ADOX.Table objTable = new ADOX.Table();

  objTable.Name = "City";

            objTable.Columns.Append("ID", ADOX.DataTypeEnum.adInteger,8);
            objTable.Columns.Append("Country", ADOX.DataTypeEnum.adVarWChar, 8);
            objTable.Columns.Append("State", ADOX.DataTypeEnum.adVarWChar, 8);
            objTable.Columns.Append("Code", ADOX.DataTypeEnum.adVarWChar, 8);
            objTable.Columns.Append("Name", ADOX.DataTypeEnum.adVarWChar, 128);
catalogCls.Tables.Append(objTable);
 Cn.Close();

 

 

 

So I use the following code to try to change the column property to all nulls

 

 // open connection
  ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
   conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="test.mdb, "", "", 0);           

  // Create a catalog
  ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.ActiveConnection = conn;
   // fetch the table
 ADOX.Table table = cat.Tables["city"];
                  // fetch the column           
   ADOX.Column col = table.Columns["state"];
       ADOX.Property prop = col.Properties["Jet OLEDB:Allow Zero Length"];
            prop.Value = true;

            if (column2.Length > 0)
            {
                ADOX.Column col2 = table.Columns[column2];
                ADOX.Property prop2 = col2.Properties["Jet OLEDB:Allow Zero Length"];
                prop2.Value = true;
            }
         
            // finish up
            col = null;
            cat = null;
            conn.Close();
            conn = null;