views:

46

answers:

4

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?

A: 

Refer this tutorial for basic ado.net C# and access db you would need to create a command and then use a reader to loop through the rows.

Misnomer
A: 

If it is needed, here some sample for adding Columns in your DataTable

Junior Mayhé
+1  A: 

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.

Jay Riggs
@Jay Riggs, I get this error interop type Adox.CatalogClass cannot be embedded. Use the applicable interface instead.
iterationx
@iterationx - WOMM (Works on my machine!). I suspect you're using VS2010 - I'm using VS2008. I Googled and found that the general cause of the error is how VS2010/.NET 4 does interop. It's possible if you target an earlier version of the Fx your project will compile. If you want to fix it you should Google "cannot be embedded. Use the applicable interface instead." Here's a [blog post](http://mokosh.co.uk/post/2010/04/10/net-4-0-interop-type-cannot-be-embedded-use-the-applicable-interface-instead/) that describes the problem and describes a solution but I don't know if it will help you.
Jay Riggs
+3  A: 

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.

Stewbob