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

Question

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

Answer 1

This article from John Russell Plant explains how you'd do it in specific detail with code samples. There are three steps:

1. Create the catalog.
2. Create the tables.
3. Release the relevant COM objects.

Answer 2

Try:

using ADOX; //Requires Microsoft ADO Ext. 2.8 for DDL and Security
using ADODB;

public bool CreateNewAccessDatabase(string fileName)
{
  bool result = false; 

  ADOX.Catalog cat = new ADOX.Catalog();
  ADOX.Table table = new ADOX.Table();

  //Create the table and it's fields. 
  table.Name = "Table1";
  table.Columns.Append("Field1");
  table.Columns.Append("Field2");

  try
  {
    cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + "; Jet OLEDB:Engine Type=5");
    cat.Tables.Append(table);

    //Now Close the database
    ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
    if (con != null)
    con.Close();

    result = true; 
  }
  catch (Exception ex)
  {
    result = false;
  }
  cat = null;
  return result;
} 

http://zamirsblog.blogspot.com/2010/11/creating-access-database.html

Answer 3

Create a blank access database and store it in your resource files.

Now whenever you want to use it, fetch that database from your resources and copy it to wherever you want, rename it to whatever you want and execute your database setup script to create default tables and load values in them.

Answer 4

static void IF_EXISTS_DELETE_AND_CREATE(string cn)
{
    //cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =";
    //cn += AppDomain.CurrentDomain.BaseDirectory.ToString() + "test.mdb"; 
    try
    {
        OleDbConnection connection = new OleDbConnection(cn);
        object[] objArrRestrict;
        objArrRestrict = new object[] { null, null, null, "TABLE" };
        connection.Open();
        DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, objArrRestrict);
        connection.Close();
        string[] list;
        if(schemaTable.Rows.Count > 0)
        {
            list = new string[schemaTable.Rows.Count];
            int i = 0;
            foreach (DataRow row in schemaTable.Rows)
            {
                list[i++] = row["TABLE_NAME"].ToString();
            }
            for ( i = 0; i < list.Length; i++)
            {
                if(list[i] == "TEMP")
                {
                    string deletedl = "DROP TABLE TEMP";
                    using (OleDbConnection conn = new OleDbConnection(cn))
                    {
                        using (OleDbCommand cmd = new OleDbCommand(deletedl, conn))
                        {

                            conn.Open();
                            cmd.ExecuteNonQuery();
                            conn.Close();
                        }
                    }
                    break;
                }
            }
        }
        string ddl = "CREATE TABLE TEMP (USERID INTEGER NOT NULL,[ADATE] TEXT(20), [ATIME] TEXT(20))";
        using(OleDbConnection conn = new OleDbConnection(cn))
        {                    
            using(OleDbCommand cmd = new OleDbCommand(ddl, conn))
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }
    catch (System.Exception e)
    {
        string ex = e.Message;
    }
}

Answer 5

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#