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?

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. — Jeff LaFay
– Jeff LaFay, Commented Jan 25, 2011 at 19:04

SLaks · Accepted Answer · 2011-01-24 04:12:28Z

20

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.

answered Jan 24, 2011 at 4:12

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matthias Over a year ago

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

reguieg younes Over a year ago

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

g00n3r Over a year ago

anyone know why the Class not registered happening?

user20416 Over a year ago

@g00n3r Do you have an installed version of Office or Access?

gliderkite · Accepted Answer · 2012-06-23 12:28:24Z

17

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

edited Jun 23, 2012 at 12:28

gliderkite

8,9287 gold badges49 silver badges82 bronze badges

answered Jan 24, 2012 at 20:01

Zamir

1992 silver badges2 bronze badges

1 Comment

someone Over a year ago

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

Community · Accepted Answer · 2020-06-20 09:12:55Z

13

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:

enter image description here

which is included as ADOX in the references

enter image description here

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 16, 2012 at 18:15

Jim Lahman

2,8072 gold badges27 silver badges22 bronze badges

2 Comments

Jim Lahman Over a year ago

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

Imad Nabil Alnatsheh Over a year ago

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

user20416 · Accepted Answer · 2019-03-01 07:13:44Z

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.

Collectives™ on Stack Overflow

How to create Microsoft Access database in C# programmatically?

4 Answers 4

4 Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related