I have a need in a new
program I am creating to talk to 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 Accessing the Database
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 am editing a button
click. So that when the button is
clicked it will read Add a table called "NEW_TABLE" to my access DB
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 connectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data"
+ @" Source=C:\Users\patman\Desktop\Test_DB.accdb";
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();
dbCmd.Connection.Close();
MessageBox.Show("
Success!");
}
catch (OleDbException exp)
{
MessageBox.Show("Database
Error:"
+ exp.Message.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
}
|
The connection string "
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\patman\Desktop\Test_DB.accdb" connects to an Access Database named
Test_DB.accdb that is on my desktop.
This program creates a new
Table in that access Database called NEW_TABLE
If you look at this table in
design view you can see that it has three fields id (that auto increments), first_name (var
char (255)) and last_name (var char (255))
Deleting a table
from a database
Here is my code for deleting a table from an
ACCESS database. It first creates a
table, uses a messageBox as a pause so
you can check the database, then deletes the table it 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 connectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data"
+ @"
Source=C:\Users\patman\Desktop\Test_DB.accdb";
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)"
+ ")";
String dropTableSQL = "DROP TABLE
"
+ tableName;
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");
MessageBox.Show(dropTableSQL);
dbCmd.Connection = conn;
dbCmd.CommandText =
dropTableSQL;
dbCmd.ExecuteNonQuery();
dbCmd.Connection.Close();
MessageBox.Show("Table
Deleted");
}
catch (OleDbException exp)
{
MessageBox.Show("Database
Error:"
+
exp.Message.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
}
|
This is the portion of code
where the database is dropped.
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
No comments:
Post a Comment