Visual Studio 2012 C# Create an ACCESS DB

Posted on Tuesday, February 19, 2013



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

3 comments:

  1. 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!

    ReplyDelete
  2. Hello Sir

    I 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

    ReplyDelete
  3. I searched more and more in the Internet On Programatically create access database using c#. This is the best. Thanks a lot...

    ReplyDelete