John Russell Plant - Create an Access Database in C#

Friday, January 18, 2008

I wrote some code last year to create an Microsoft Access Database, fill in some data and email it out. It never made it out to production so I thought I would share part of it here. Let's focus on creating the database in this post.

The first thing you need to do is get a COM reference to the Microsoft ADO Ext. X.X for DDL and Security. The X.X represents whatever version you happen to have on your machine. Mine used to be version 2.7, but with Visual Studio 2008, it was updated to 6.0.

Once you have added the reference, ADOX will be added to the using section of your code.

Next you will want to create the catalog for the database. Insert the filename you wish into the following string and pass it to the CatalogClass.

   1:              CatalogClass cat = new CatalogClass();

2:

   3:              string tmpStr;

   4:              string filename = "Sample.MDB";

5:

   6:              tmpStr = "Provider=Microsoft.Jet.OLEDB.4.0;";

   7:              tmpStr += "Data Source=" + filename + ";Jet OLEDB:Engine Type=5";

8:

   9:              cat.Create(tmpStr);

The next step is to create the table and columns for your database. This is a pretty straight forward operation as shown in the example below.

   1:              Table nTable = new Table();

   2:              nTable.Name = "PersonData";

3:

   4:              nTable.Columns.Append("LastName", DataTypeEnum.adVarWChar, 25);

   5:              nTable.Columns.Append("FirstName", DataTypeEnum.adVarWChar, 25);

   6:              nTable.Columns.Append("Address 1", DataTypeEnum.adVarWChar, 45);

   7:              nTable.Columns.Append("Address 2", DataTypeEnum.adVarWChar, 45);

   8:              nTable.Columns.Append("City", DataTypeEnum.adVarWChar, 25);

   9:              nTable.Columns.Append("State", DataTypeEnum.adVarWChar, 2);

  10:              nTable.Columns.Append("Zip", DataTypeEnum.adVarWChar, 9);

11:

  12:              cat.Tables.Append(nTable);

The final step is very important or you will get error when you close your application. Once the all the tables and columns have been added, you will need to release the com objects properly and in the proper order.

   1:              System.Runtime.InteropServices.Marshal.FinalReleaseComObject(nTable);

   2:              System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat.Tables);

   3:              System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat.ActiveConnection);

   4:              System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat);

That is it. You should now have a Access Database that you can write to. Hope this helps.

C Sharp | Programming

posted on Friday, January 18, 2008 3:11:18 AM (Eastern Standard Time, UTC-05:00)

Comments [2]
Related posts:
Silverlight 3 Controls - Vectorlight
RIA Services May 2009 Preview
VS 2008 & .NET 3.5 SP1 Beta
Free Software for Students
Object Initializers
dnrTV Aggregator Part 2 - LINQ to XML

Monday, March 16, 2009 8:34:11 AM (Eastern Standard Time, UTC-05:00)

Very usefull snippet. Thank you!

E. Tacheva

Thursday, January 07, 2010 3:11:42 AM (Eastern Standard Time, UTC-05:00)

Hi, I am using the same snippet.

public bool CreateDatabaseFromDataset(DataSet ds)
{
CatalogClass cat = new CatalogClass();
string tmpStr = string.Empty;
string filename = "MenuParser.mdb";
//string filename = Application.StartupPath + "MenuParser.mdb";

tmpStr = "Provider=Microsoft.Jet.OLEDB.4.0;";
tmpStr += "Data Source=" + filename + ";Jet OLEDB:Engine Type=5";

Table nTable = new Table();

nTable.Name = "PersonData";

nTable.Columns.Append("LastName", DataTypeEnum.adVarWChar, 25);

nTable.Columns.Append("FirstName", DataTypeEnum.adVarWChar, 25);

nTable.Columns.Append("Address 1", DataTypeEnum.adVarWChar, 45);

nTable.Columns.Append("Address 2", DataTypeEnum.adVarWChar, 45);

nTable.Columns.Append("City", DataTypeEnum.adVarWChar, 25);

nTable.Columns.Append("State", DataTypeEnum.adVarWChar, 2);

nTable.Columns.Append("Zip", DataTypeEnum.adVarWChar, 9);

cat.Tables.Append(nTable);

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(nTable);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat.Tables);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat.ActiveConnection);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat);

return true;
}

I am getting an error.
---------------------
Object is no longer valid.

Stack Trace:

at ADOX.Tables.Append(Object Item)
at MenuParser.CreateAccessDatabaseFromDataset.CreateDatabaseFromDataset(DataSet ds) in D:\iMenu360\MenuParser\MenuParser\CreateAccessDatabaseFromDataset.cs:line 45
at MenuParser.frmMenuParser.btnParse_Click(Object sender, EventArgs e) in D:\iMenu360\MenuParser\MenuParser\frmMenuParser.cs:line 78

Source : ADOX.Tables
----------------------
please help.

Regards,
Sourabh.

Sourabh

Name
E-mail	(will show your gravatar icon)
Home page

	Remember Me
Comment (HTML not allowed)
Enter the code shown (prevents robots):