How to Insert Data into Access Database Programmatically

Using Microsoft Access, create a Database called MyAccessDatabase and create a Table within it called MyTable with two fields: ID of type AutoNumber and MyName of type Text.




Then, close the database. Open Visual Studio 2005 and create a new Data Source by clicking on the Data Menu then in the dropdown menuitem, select Add New Datasource... Navigate to the Access database that you created earlier MyAccessDatabase.mdb and add it.

To see the new database, click on the View Menu and select Server Explorer.



Then, select the database MyAccessDatabase from the Server Explorer. The connection string will be displayed in the Properties Grid on the bottom right.


Copy the Connection String. It looks something like this:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\Users\Paul\Documents\Visual Studio 2005\Projects\DataBasePractice\DataBasePractice\MyAccessDatabase.mdb"

Use the Connection String above in your code:

OleDbConnection accessConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Paul\Downloads\Downloaded MCTS\70-526\MyAccessDatabase\MyAccessDatabase.mdb");

private void createTableInAccessToolStripMenuItem_Click(object sender,EventArgs e)
{
OleDbCommand AccessCommand = new OleDbCommand();
AccessCommand.Connection = accessConnection;
AccessCommand.CommandType = CommandType.Text;
AccessCommand.CommandText =
//"CREATE TABLE MyTable ([ID][int] NOT NULL,[Name][nvarchar](10)NULL)";

"INSERT INTO MyTable(ID,MyName) VALUES ('1', 'Adrian')";
AccessCommand.Connection.Open();
AccessCommand.ExecuteNonQuery();
AccessCommand.Connection.Close();
}

The above code will insert a new row (record) with ID = 1 and MyName = Adrian

Open MS Access to verify:



Note:
Note the commented out code (in green):

//"CREATE TABLE MyTable ([ID][int] NOT NULL,[Name][nvarchar](10)NULL)";

It will produce an error if executed for an MS Access Database. This is because the syntax for creating an Access Table is different from the syntax for creating an MS SQL 2005 Table. The command above is for SQL Table.