How to Insert Data into Microsoft Access Database Using C#

To insert data to the Microsoft Access database, the code snippet below will do the job.

using System.Data.OleDb;

string fileName = "YourDatabaseFilename";
string password = "TheDatabasePassword";
string tableName = "TableName";
string columnName = "NewColumn";

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + fileName +

                                            ".accdb; Jet OLEDB:Database Password =" + password + "; Mode=12";

OleDbConnection con = new OleDbConnection(connectionString);

con.Open();

string query = 

"INSERT INTO " + tableName + "(" + columnName +

                         ") VALUES('" + info + "')";

OleDbCommand Command = new OleDbCommand(query, con);

Command.ExecuteNonQuery();

con.Close();

How to Add Column in Microsoft Access Database File using C#

In adding column in Microsoft Access database file using C#, the code snippet below might help you.


using System.Data.OleDb;

string fileName = "YourDatabaseFilename";
string password = "TheDatabasePassword";
string tableName = "TableName";
string columnName = "NewColumn";
string dataType = "Number";    
// it could be "LongText", "ShortText", kindly refer to the data types accepted by MS Access
string connectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + fileName +
                                           ".accdb; Jet OLEDB:Database Password =" + password + "; Mode=12";

OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
string query = "ALTER TABLE NewTable ADD COLUMN " + columnName + " " + dataType;
OleDbCommand Command = new OleDbCommand(query, con);
Command.ExecuteNonQuery();
con.Close();

How to Add a New Table with Primay Key on a Microsoft Access Database File Using C#

Presented below is the code snippet for adding a new table on a Microsoft Access database file. It will also automatically generate a column named ID as the primary key with autoincrementing integer value.

using System.Data.OleDb;

string fileName = "YourDatabaseFilename";

string password = "TheDatabasePassword";

string tableName = "NewTableName";
string connectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + fileName +
                                           ".accdb; Jet OLEDB:Database Password =" + password + "; Mode=12";

OleDbConnection con = new OleDbConnection(connectionString);

con.Open();

string query = "CREATE TABLE " + tableName+
                         "(ID int identity, " + "CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))" 

OleDbCommand command = new OleDbCommand(query, con);

command.ExecuteNonQuery();

con.Close();

Pages you may also want to visit:


Creating a Microsoft Access Database File

Adding or Changing Password on a Microsoft Access Database File

How to Add or Change the Password on a Microsoft Access Database File Using C#

Code snippet for adding or changing the password on a Microsoft Access database file:

using System.Data.OleDb;

string fileName = "YourDatabaseFilename";

string newPassword = "ThisIsTheNewPassword";

string oldPassword = null;
string connectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + fileName +
                                           ".accdb; Jet OLEDB:Database Password =" + oldpassword + "; Mode=12";

OleDbConnection con = new OleDbConnection(connectionString);

string query = string.Format("ALTER DATABASE PASSWORD [{0}] [{1}]", newPassword, oldPassword);

OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, con);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

Pages you may also want to visit:

Adding a Table on a Microsoft Access Database File

Creating a Microsoft Access Database File

How to Programmatically Create a Microsoft Access Database File Using C#

You might be wondering what to do when you want to create a database but you do not want manually setting it up and you want just the code to do it for you. Well, you are in the right blog. Without further ado, let me share to you this code snippet.

First, you muss add Microsoft ADO Ext. 6.0 (or whatever version you have) for DDL and Security as a reference.

stringfileName = "YourDatabaseFilename";

ADOX.Catalog cat = new ADOX.Catalog();

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

try

{

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ".accdb; Jet OLEDB:Engine Type=5");

}

catch (Exception ex)

{

//For Windows Forms Applications
//MessageBox.Show("Database file is not created.");

//For Console Applications

// Console.WriteLine("Database file is not created.");

 }

cat = null;




Pages you may also want to visit:


Creating a Microsoft Access Database File
Adding a Table on a Microsoft Access Database File (with primary key)