How to create an Access database at runtime in C#?

Question

How can I create an Access Database at runtime in C#?

Answer 1

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.

alt text http://blog.jrpsoftware.com/content/binary/WindowsLiveWriter/CreateanAccessDatabaseinC_10DDD/AddReference_2.png

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

alt text http://blog.jrpsoftware.com/content/binary/WindowsLiveWriter/CreateanAccessDatabaseinC_10DDD/Using_2.png

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.

CatalogClass cat = new CatalogClass();  
string tmpStr;  
string filename = "Sample.MDB";   
tmpStr = "Provider=Microsoft.Jet.OLEDB.4.0;";   
tmpStr += "Data Source=" + filename + ";Jet OLEDB:Engine Type=5";  
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.

 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);

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.

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);

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

Reference: John Russell Plant - Create an Access Database in C#