I have a table named "Streets" in an access db, and I have opened a connection with
OleDbConnection con = OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source=" + mdbFileName);
How can I get at my table?
I have a table named "Streets" in an access db, and I have opened a connection with
OleDbConnection con = OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source=" + mdbFileName);
How can I get at my table?
If it is needed, here some sample for adding Columns in your DataTable
Add an ADOX COM reference to your project and try out 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 blog posts:
Rename Field in MS Access Programmatically using ADOX (C#.NET)
Add New Field in MS Access Programmatically using ADOX (C#.Net)
Add data to your new field by using standard ADO.net.
You need to 'ALTER' the table and use the 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 called 'fldNew' to your database. Then you can use a standard UPDATE SQL command to add the data into your new column.