visual C#, MS-Access table alteration

ArunVashist
ArunVashist used Ask the Experts™
on
Hi Friends,

I have a windows application developed in VS 2008 using C# and MS-Access as backend. Now this application is running perfectly, but due to some updates I need to add new columns in existing database tables, but as this application is being used by many of our clients for about 1-2 month and contains sensitive data which few of our client are not willing to share, so we are not able to physical add these columns into tables. So, we want  to provide them some Patch/update which just add new columns in Database.

Please help.

thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
if you dont want to add that columns to the table, you can create new table with required columns and join it to the main table as one - one relationship.

Author

Commented:
Hi Yadtrt,

Thanks for the quick reply but i think I could't make my self clear in post. well to Add columns we can ask them for the database and add the columns and sent them back, but they are not providing us the access to database, So we need a way out to run Alter Table queries at client end.

1. we will create a list of columns need to add into tables
2. we will create Sql queries to alter table (to add new columns.)
3. Now i want to run these sql queries at client end.

please suggest me a way out.

Commented:
You can use DAO to add fields to the table.
Function AddFields()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    'Initialize
    Set db = CurrentDb() '<===== You may need to change this if you want to work on remote database 
 
    Set tdf = db.TableDefs("TableName")
    
    'Add a field to the table.
    tdf.Fields.Append tdf.CreateField("Field1", dbText, 50)
    Debug.Print "Field added."
    
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Commented:
also you can use ADO.net for altering the table, check this

http://msdn.microsoft.com/en-us/library/ms971485.aspx

Author

Commented:
Hi Yadtrt,

Its a Windows application written in C# using vS 2008 and data layer is using ADO.net, given sample code and link add almost nothing to solve this issue.
Senior Consultant - Deloitte
Commented:
Using ADOX you can do it
http://msdn.microsoft.com/en-us/library/ms677200(VS.85).aspx




using ADOX;   
  
// Skipped namespace and public class here...   
  
private void btnUpdate_Click(object sender, EventArgs e)   
 {   
   string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind.mdb;Persist Security Info=False";   
   CatalogClass cat = new CatalogClass();   
  
   cat.let_ActiveConnection(connString)   
  
   AddField(cat, "TargetTable", "Employee_Name_2", DataTypeEnum.adVarWChar, 50, string.Empty);   
}   
  
private void AddField(ADOX.CatalogClass cat, string tableName, string newFieldName, DataTypeEnum varType, int size, string defaultValue)   
 {   
   // #1   
   // cat.Tables[tableName].Columns.Append(newFieldName, varType, size);   
  
   ColumnClass col = new ColumnClass();   
   col.Name = newFieldName;   
   col.Type = varType;   
   col.DefinedSize = size;   
  
   // Make this field become nullable field.   
   col.Attributes = ColumnAttributesEnum.adColNullable;   
  
   // #2   
   cat.Tables[tableName].Columns.Append((object)col, DataTypeEnum.adInteger, 0);   
   // #3 - Assign default value after column appended.   
   if (!string.IsNullOrEmpty(defaultValue))   
   {   
      col.Properties["Default"].Value = defaultValue;   
   }   
}  

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial