Objective
To develop a Windows application for performing insert, search, update, and delete operations & navigation of M.S.Access 2007 records using ODBC connection.
Introduction
- Create a table in the M.S.Access 2007 file and populate it.
- In our application we use a 'stud.accdb' (M.S.Access 2007) file, which consists of a 'student' table.
- (Note: 'stud.accdb' is placed in 'prash_access07.zip' along with the source code)
Creating and Configuring ODBC Data Source (DSN)
Go to Start Menu -> Control Panel -> Administrative Tools -> Data Sources (ODBC).
![Control Panel]()
Click on the 'Add' button -> Select 'Microsoft Access Driver (*.mdb, *.accdb)' ->click on the 'Finish' button.
![Add button]()
Give a name to your Data Source
Click on the 'Select' button and select your M.S.Access 2007 file (*.accdb) -> OK -> OK.
![Select button]()
Your Data Source Name will be specified in the 'ODBC Data Source Administrator' window ->Click on the 'OK' button.
Thus, your Data Source (DSN) is configured.
Design
![Design]()
Design the form as above with a DataGridView, 3 Labels, 3 TextBoxes, and 10 buttons.
Introduction to Code
As we want to use ODBC Connection include the namespace.
using System.Data.ODBC;
For accessing records from the M.S.Access-2003 file, we use the 'Jet' driver.
But for accessing records from the M.S.Access-2003 file we use the 'Ace' driver.
In this application, we will search a record by taking input from the InputBox. For this, we have to add a reference to Microsoft.VisualBasic.
Adding a Reference
Goto Project Menu ->Add Reference -> select 'Microsoft.VisualBasic' from the .NET tab.
In order to use this we have to include the namespace.
Using Microsoft.VisualBasic
ODBC connection string
Syntax
OdbcConnection con = new OdbcConnection("dsn=<Data Source Name>");
Ex
OdbcConnection con = new OdbcConnection("dsn=myaccess07dsn ");
You just need to specify the Data Source Name(DSN) in the Connection String, no need to specify the driver details and path of the file, your DSN will take care of it.
Creating a primary key in the Data Table
In this app. we use the Find() method to search a record, which requires details of the primarykey column for database tables; this is provided using a statement.
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
But as we don't have any primary key column in the M.S.Access table, we have to create a primary key column in the data table.
Ex
ds.Tables[0].Constraints.Add("pk_sno", ds.Tables[0].Columns[0], true);
Pointing to the current record in Table
After searching for a record, we have to get the index of that record so that we can show the next and previous records when we press the'>>'(next) and '<<'(previous) buttons.
Ex
rno = ds.Tables[0].Rows.IndexOf(drow);
Code
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.Odbc;
using Microsoft.VisualBasic;
namespace prash_access07
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
OdbcConnection con;
OdbcCommand cmd;
OdbcDataAdapter adapter;
DataSet ds;
int rno;
private void Form1_Load(object sender, EventArgs e)
{
con = new OdbcConnection("dsn=myaccess07dsn");
//stud.accdb->access07 filename
loaddata();
showdata();
}
void loaddata()
{
adapter = new OdbcDataAdapter("select * from student", con);
ds = new DataSet(); // student -> table name in stud.accdb file
adapter.Fill(ds, "student");
ds.Tables[0].Constraints.Add("pk_sno", ds.Tables[0].Columns[0], true); // creating primary key for Tables[0] in dataset
dataGridView1.DataSource = ds.Tables[0];
}
void showdata()
{
textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();
textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();
textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();
}
private void btnFirst_Click(object sender, EventArgs e)
{
if (ds.Tables[0].Rows.Count > 0)
{
rno = 0;
showdata();
}
else
MessageBox.Show("no records");
}
private void btnPrevious_Click(object sender, EventArgs e)
{
if (ds.Tables[0].Rows.Count > 0)
{
if (rno > 0)
{
rno--;
showdata();
}
else
MessageBox.Show("First Record");
}
else
MessageBox.Show("no records");
}
private void btnNext_Click(object sender, EventArgs e)
{
if (ds.Tables[0].Rows.Count > 0)
{
if (rno < ds.Tables[0].Rows.Count - 1)
{
rno++;
showdata();
}
else
MessageBox.Show("Last Record");
}
else
MessageBox.Show("no records");
}
private void btnLast_Click(object sender, EventArgs e)
{
if (ds.Tables[0].Rows.Count > 0)
{
rno = ds.Tables[0].Rows.Count - 1;
showdata();
}
else
MessageBox.Show("no records");
}
private void btnInsert_Click(object sender, EventArgs e)
{
cmd = new OdbcCommand("insert into student values(" + textBox1.Text + ",'" + textBox2.Text + "','" + textBox3.Text + "')", con);
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{
MessageBox.Show("record inserted");
loaddata();
}
else
MessageBox.Show("insertion failed");
}
private void btnSearch_Click(object sender, EventArgs e)
{
int n = Convert.ToInt32(Interaction.InputBox("Enter sno:", "Search", "20", 200, 200));
DataRow drow = ds.Tables[0].Rows.Find(n);
if (drow != null)
{
rno = ds.Tables[0].Rows.IndexOf(drow);
textBox1.Text = drow[0].ToString();
textBox2.Text = drow[1].ToString();
textBox3.Text = drow[2].ToString();
}
else
MessageBox.Show("Record not found");
}
private void btnUpdate_Click(object sender, EventArgs e)
{
cmd = new OdbcCommand("update student set sname='" + textBox2.Text + "',course='" + textBox3.Text + "' where sno=" + textBox1.Text, con);
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{
MessageBox.Show("Record Updated");
loaddata();
}
else
MessageBox.Show("Update failed");
}
private void btnDelete_Click(object sender, EventArgs e)
{
cmd = new OdbcCommand("delete from student where sno=" + textBox1.Text, con);
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{
MessageBox.Show("Record Deleted");
loaddata();
}
else
MessageBox.Show("Deletion failed");
}
private void btnClear_Click(object sender, EventArgs e)
{
textBox1.Text = textBox2.Text = textBox3.Text = "";
}
private void btnExit_Click(object sender, EventArgs e)
{
this.Close();
}
}
}