How to create Microsoft Access database in C# programmatically?
Solution 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
.
Solution 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
Solution 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
Solution 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.
Comments
-
LEMUEL ADANE almost 2 years
How do you create a Microsoft Access database file in C# if it does not exist yet?
-
Jim Lahman almost 12 yearsComment noted. Just following up on Zamir's comment.
-
Matthias about 8 yearsHint: The "ADOX library" could be found as "Microsoft ADO Ext. 6.0 for DDL and Security" on my machine
-
Imad Nabil Alnatsheh over 6 yearsJust 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 theParentCatalog
for the column as your Catalog, then set the column'sProperties["AutoIncrement"].Value
totrue
-
someone over 4 yearsgood example! remember
using ADODB; // Requires ADODB in .Net framework
-
reguieg younes almost 4 yearsI have an error System.Runtime.InteropServices.COMException Class not registered
-
g00n3r almost 4 yearsanyone know why the Class not registered happening?
-
Zev Spitz almost 4 years@g00n3r Do you have an installed version of Office or Access?