How to manipulate a table from an Access database using .NET.

Question

I have a table called "Streets" in access db and I opened a connection with

OleDbConnection con = OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source=" + mdbFileName); 

How can I come to my table?

Answer 1

Add the ADOX COM link to your project and try the following code:

 using ADOX; //... private void Test() { string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<path to your .mdb>"; CatalogClass cat = new CatalogClass(); cat.let_ActiveConnection(connString); RenameField(cat, "YourTableName", "OriginalFieldName", "NewFieldName"); AddField(cat, "YourTableName", "YourNewFieldName", DataTypeEnum.adVarWChar, 50, ""); } // Rename a field. private void RenameField(CatalogClass cat, string tableName, string originalFieldName, string newFieldName) { cat.Tables[tableName].Columns[originalFieldName].Name = newFieldName; } // Add a field to a specified table. private void AddField(ADOX.CatalogClass cat, string tableName, string newFieldName, DataTypeEnum varType, int size, string defaultValue) { ColumnClass col = new ColumnClass(); col.Name = newFieldName; col.Type = varType; col.DefinedSize = size; col.Attributes = ColumnAttributesEnum.adColNullable; cat.Tables[tableName].Columns.Append((object)col, DataTypeEnum.adInteger, 0); if (!string.IsNullOrEmpty(defaultValue)) { col.Properties["Default"].Value = defaultValue; } } 

Details can be found in the following blogs:

Rename a field in MS Access Programmatically using ADOX (C # .NET)
Add a new field to MS Access Programmatically using ADOX (C # .Net)

Add data to the new field using standard ADO.net.

Answer 2

You need an "ALTER" table and use ExecuteNonQuery. Code below:

 Dim dbName As String = "<path>\mdbFileName.mdb" Dim tmpConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbName & ";Persist Security Info=False" Dim sqlText As String = "" Using cn As New OleDbConnection(tmpConString) cn.Open() sqlText = "ALTER TABLE Table1 ADD COLUMN fldNew TEXT(50)" Using cm As New OleDbCommand(sqlText, cn) cm.ExecuteNonQuery() End Using End Using 

This will add a new column named "fldNew" to your database. You can then use the standard UPDATE SQL command to add data to a new column.