How to create Microsoft Access database in C# programmatically?

Question

How do you create a Microsoft Access database file in C# if it does not exist yet?

Answer 1

The simplest answer is to embed an empty .mdb / .accdb file in your program and write it out to disk.

The correct answer is to use COM Interop with the ADOX library:

var cat = new ADOX.Catalog()
cat.Create(connectionString);

Remember to generate your connection strings using OleDbConnectionStringBuilder.

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

On my computer, Windows 7 sp1 Professional 64-bit, I found Microsoft ADO Ext. 2.8 for DDL and Security in C:\Program Files\Common Files\System\ado\msadox28.dll.

It is also found as a reference:

which is included as ADOX in the references

By default, columns are created as text[255]. Here are a few examples to create columns as different datatypes.

table.Columns.Append("PartNumber", ADOX.DataTypeEnum.adVarWChar, 6); // text[6]
table.Columns.Append("AnInteger", ADOX.DataTypeEnum.adInteger); // Integer 

I found this list of datatypes to create and read access database fields

Access Text = adVarWChar

Access Memo = adLongVarWChar

Access Numeric Byte = adUnsignedTinyInt

Access Numeric Integer = adSmallInt

Access Numeric Long Integer = adInteger

Access Numeric Single Precision = adSingle

Access Numeric Double Precision = adDouble

Access Numeric Replicatie-id = adGuid

Access Numeric Decimal = adNumeric

Access Date / Time = adDate

Access Currency = adCurrency

Access AutoNumber = adInteger

Access Yes / No = adBoolean

Access HyperLink = adLongVarWChar

Answer 4

You can use the CreateDatabase method in the DAO / ACE library (it's installed with Office, or available for download from here).

// using Microsoft.Office.Interop.Access.Dao;
// using static Microsoft.Office.Interop.Access.Dao.DatabaseTypeEnum;

const string dbLangGeneral = ";LANGID=0x0409;CP=1252;COUNTRY=0";

var engine = new DBEngine();
var dbs = engine.CreateDatabase(@"c:\path\to\database.accdb", dbLangGeneral, dbVersion120);
dbs.Close();
dbs = null;

Note that depending on the version of Access/Jet you want your database to support, you can use other values from the DatabaseTypeEnum enum:

• dbVersion10
• dbVersion11
• dbVersion20
• dbVersion30
• dbVersion40
• dbVersion120
• dbVersion140
• dbVersion150

Also note that you can choose to encrypt the database, or select a different collation.

NB: If you have a 64-bit machine, and want to run the code as part of a 64-bit program, you'll need the 64-bit version of the engine. If you already have the 32-bit version installed (either via Office, or via the download), you'll have to run the 64-bit installer with the /passive and /silent flags; otherwise you'll get a message that you can't install 64-bit components over previously installed 32-bit components.