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?

Elisabetta

Does it have to be access or are you looking for a file based database solution to store data? SQLite is nice and portable if that's all you need. – Keitel

Yeah I am now using SQLite on Android. – Elisabetta

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.

Explore

Hint: The "ADOX library" could be found as "Microsoft ADO Ext. 6.0 for DDL and Security" on my machine – Prog

I have an error System.Runtime.InteropServices.COMException Class not registered – Bernt

anyone know why the Class not registered happening? – Salomesalomi

@Salomesalomi Do you have an installed version of Office or Access? – Ankh

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

Magdalenamagdalene

good example! remember using ADODB; // Requires ADODB in .Net framework – Vuillard

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

Merell

Comment noted. Just following up on Zamir's comment. – Merell

Just a very late note on the fact that if you want to make an AutoNumber column you have to set the AutoIncrement value to true. This happens after creating the Catalog, where you set the ParentCatalog for the column as your Catalog, then set the column's Properties["AutoIncrement"].Value to true – Kumamoto

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.

Ankh