I have a need in a new
program I am creating to create an ACCESS DB. I am programming in C# using Visual Studio
Pro 2012.
I am using InstallShield
LE for my installation process. If you
are having problems getting InstallShield LE working on Visual Studio I did a
little write up on it here http://www.whiteboardcoder.com/2013/02/visual-studio-pro-2012-install-shield-le.html
First a few Gotchas
Before I get into the
programming I want to list a few gotchas that I had to deal with. I hope this saves you some programming time
before you start.
I have a 64 bit version
Win 7 running on my Box. I have Office
2010 installed and running as 32-bit programs.
If you want to check if your office suite is 32-bit or 64-bit check this
guide http://www.howtogeek.com/howto/24259/beginner-discover-if-youre-running-the-32-or-64-bit-version-of-office-2010/ [1]
By Default my Visual
Studio 2012 C# program is set to compile to Any CPU (I am guessing 32-bit or 64-bit). As a result if I try to compile my program
and connect to a database I get this error.
The error "The
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."
Is caused by the program trying to find a 64 bit connection and not finding it,
since in my case there is only a 32-bit one.
There seems to be two
ways to fix this.
The first way is to go
to http://www.codeproject.com/Tips/370922/Microsoft-Access-Database-Engine-2010-Redistributa [2] download and install this program that will
then be able to handle 64-bit connection and 32-bit connections.
The second way involves
fixing Platform target of your program.
From Visual Studio 2012
select Build--> Configuration Manager
Change "Any
CPU" to x86 and click Close. This forces the issue and uses the 32-bit connection
type.
This is the solution I
chose, as for my client it's one more thing they need to install before they
can use the software.
Programmatically Creating an ACCESS DB
I am starting with a
basic "Hello World" Program I made before. (if you need help with getting that working
here is the details http://www.whiteboardcoder.com/2013/02/visual-studio-pro-2012-install-shield-le.html
I gleamed a lot of
information on how to get this done from this site. http://stackoverflow.com/questions/4778548/how-to-create-microsoft-access-database-in-c-sharp-programmatically [3]
First you need to add a
Reference for "Microsoft ADO Ext 2.8 for DDL and Security"
Right click on
References and select Add Reference.
Select Com, then Type
Libraries. In the search bar enter
"Microsoft ADO"
Check box 2.8 and click
OK
Now do the same thing
for Microsoft ActiveX Data Objects 2.8 Library.
Now it's been added to
your references
I am editing a button
click. So that when the button is
clicked it will create a new ACCESS database file called NEW_Test_DB.accdb on
the Desktop
Here is my code
|
using System;
using
System.Collections.Generic;
using
System.ComponentModel;
using
System.Configuration;
using System.Data;
using System.Data.OleDb;
using
System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using
System.Windows.Forms;
namespace HelloWorld
{
public partial class Form1 : Form
{
private int increment = 0;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
String db_name = "NEW_Test_DB.accdb";
String connectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data"
+ @"
Source=C:\Users\patman\Desktop\" + db_name;
//Create the Database
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create(connectionString);
//Now Close the database
ADODB.Connection con =
cat.ActiveConnection as ADODB.Connection;
if (con != null)
con.Close();
MessageBox.Show("Database
'"
+ db_name + "'
Created");
}
}
}
|
Running the Program creates a
table on my Desktop called NEW_Test_DB.accdb
Create Database with password protection
Now I want to create an ACCESS database file with password
protection. Below is the code I used to
do that
|
using System;
using
System.Collections.Generic;
using
System.ComponentModel;
using
System.Configuration;
using System.Data;
using System.Data.OleDb;
using
System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using
System.Windows.Forms;
namespace HelloWorld
{
public partial class Form1 : Form
{
private int increment = 0;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
String db_name = "NEW_Test_DB.accdb";
String password = "easy_pass";
String connectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data"
+ @"
Source=C:\Users\patman\Desktop\" + db_name
+ ";Jet
OLEDB:Database Password=" + password + ";";
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create(connectionString);
//Now Close the database
ADODB.Connection con =
cat.ActiveConnection as ADODB.Connection;
if (con != null)
con.Close();
MessageBox.Show("Database
'"
+ db_name + "'
Created");
}
}
}
|
This is the only part that has been updated to add a
password.
Now if you open the database. You will now be required to type in the
password to get access to the database.
Create the Database and add Tables to it
Here is my code that creates a table after the ACCESS
database file is created.
|
using System;
using
System.Collections.Generic;
using
System.ComponentModel;
using
System.Configuration;
using System.Data;
using System.Data.OleDb;
using
System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using
System.Windows.Forms;
namespace HelloWorld
{
public partial class Form1 : Form
{
private int increment = 0;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
String db_name = "NEW_Test_DB.accdb";
String password = "easy_pass";
String connectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data"
+ @"
Source=C:\Users\patman\Desktop\" + db_name
+ ";Jet
OLEDB:Database Password=" + password + ";";
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create(connectionString);
//Now Close the database
ADODB.Connection con =
cat.ActiveConnection as ADODB.Connection;
if (con != null)
con.Close();
MessageBox.Show("Database
'"
+ db_name + "'
Created");
//Create Table
String tableName = "NEW_TABLE";
String createSQL = "CREATE TABLE
"
+ tableName + "("
+ "id INTEGER
IDENTITY(1,1) PRIMARY KEY NOT NULL,"
+ " first_name
varchar (255),"
+ " last_name
varchar (255)"
+ ")";
OleDbConnection conn =
new OleDbConnection(connectionString);
OleDbCommand dbCmd = new OleDbCommand();
try
{
conn.Open();
MessageBox.Show(createSQL);
dbCmd.Connection = conn;
dbCmd.CommandText =
createSQL;
dbCmd.ExecuteNonQuery();
MessageBox.Show("Table
Created");
}
catch (OleDbException exp)
{
MessageBox.Show("Database
Error:"
+
exp.Message.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
}
|
This creates the Table NEW_TABLE with three columns, ID which auto increments, first_name (varchar), and last_name (varchar)
Create the Database and add Tables to it and insert data into the table
Here is my code that creates a password protected ACCESS
database, then adds a table to it, then
inserts some data into that table.
|
using System;
using
System.Collections.Generic;
using
System.ComponentModel;
using
System.Configuration;
using System.Data;
using System.Data.OleDb;
using
System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using
System.Windows.Forms;
namespace HelloWorld
{
public partial class Form1 : Form
{
private int increment = 0;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
String db_name = "NEW_Test_DB.accdb";
String password = "easy_pass";
String connectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data"
+ @"
Source=C:\Users\patman\Desktop\" + db_name
+ ";Jet OLEDB:Database
Password=" + password + ";";
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create(connectionString);
//Now Close the database
ADODB.Connection con =
cat.ActiveConnection as ADODB.Connection;
if (con != null)
con.Close();
MessageBox.Show("Database
'"
+ db_name + "'
Created");
//Create Table
String tableName = "NEW_TABLE";
String createSQL = "CREATE TABLE
"
+ tableName + "("
+ "id INTEGER
IDENTITY(1,1) PRIMARY KEY NOT NULL,"
+ " first_name
varchar (255),"
+ " last_name
varchar (255)"
+ ")";
OleDbConnection conn =
new OleDbConnection(connectionString);
OleDbCommand dbCmd = new OleDbCommand();
try
{
//Open Database
Connection
conn.Open();
MessageBox.Show(createSQL);
dbCmd.Connection = conn;
dbCmd.CommandText = createSQL;
dbCmd.ExecuteNonQuery();
MessageBox.Show("Table
Created");
//---insert into the
table -----
String query = "select *
from " + tableName;
DataSet ds = new DataSet();
OleDbDataAdapter da =
new OleDbDataAdapter(query, conn);
OleDbCommandBuilder cmdB =
new OleDbCommandBuilder(da);
da.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
//Fill the DataSet
da.Fill(ds, tableName);
//--- Insert row
into Table
DataRow row =
ds.Tables[tableName].NewRow();
row["first_name"] = "Patrick";
row["last_name"] = "Bailey";
ds.Tables[tableName].Rows.Add(row);
da.Update(ds, tableName);
//Close the Database
Connection
conn.Close();
MessageBox.Show(
"Data
inserted into the Database");
}
catch (OleDbException exp)
{
MessageBox.Show("Database
Error:"
+
exp.Message.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
}
|
Opening the
Database, which requires a password, you
can see the Table named "NEW_TABLE"
and the data inserted into it.
References
[1] How Do I Know if I’m Running
32-bit or 64-bit Office 2010?
Accessed
02/2013
[2] Microsoft Access Database
Engine 2010 Redistributable
Accessed
02/2013
[3] How to create Microsoft
Access database in C# programmatically?
Accessed
02/2013
You stuff is great but what about going a little further like exporting datagridview to an access table?? I can do exporting to sql server but access database is really tricky!
ReplyDeleteHello Sir
ReplyDeleteI want following things please help me if you can make a sample project.
1) Application should be written for Windows using WPF or WinForms. It should function fully without the need for Internet access.
2) Application should run in user mode (no need for Administrator rights on the PC)
3) Application will store data in a portable file format – Access MDB possibly
4) Database files must be password protected so cannot be opened outside application
5) Database files must be stored on a shared network drive and multiple users will access it simultaneously
6) User can create new “blank” database files at anytime and store on file system
7) User can “open” databases as needed through a standard open file dialog box
8) We need a way of detecting older database versions and updating them if there is a schema change without loosing existing data
I searched more and more in the Internet On Programatically create access database using c#. This is the best. Thanks a lot...
ReplyDelete