Tag Archives: OLEDB
Creating and connecting to a Microsoft Access Database programmatically in C#
The following example aims to get you a bit more familiar as to how to create and connect to a Microsoft Access database programmatically while being able to add any type of tables you want as well are inserting and retrieving data from it.
Firstly we will need to reference two libraries in our project. Microsoft ActiveX Data Objects 2.0 Library (Interop.ADODB.dll) which we will use to ensure that the connection to the database is closed after we are done creating it, and Microsoft ADO Ext. 2.8 for DDL and Security (Interop.ADOX.dll) which is needed to be able to access the classes that are required to create our database.
After you have finished adding those two references to your project you can use the code sample below to create the database file programmatically. Feel free to modify the code to suit your needs. Also please note that setting your project to Any CPU build will cause exceptions to be thrown when creating the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | public static void CreateAccessDatabase() { string connectionString = string.Format("Provider={0}; Data Source={1}; Jet OLEDB:Engine Type={2}", "Microsoft.Jet.OLEDB.4.0", "mydb.mdb", 5); ADOX.CatalogClass catalog = new ADOX.CatalogClass(); catalog.Create(connectionString); ADOX.Table table = new ADOX.Table(); table.Name = "Users"; // Table name // Column 1 (id) ADOX.ColumnClass idCol = new ADOX.ColumnClass(); idCol.Name = "Id"; // The name of the column idCol.ParentCatalog = catalog; idCol.Type = ADOX.DataTypeEnum.adInteger; // Indicates a four byte signed integer. idCol.Properties["AutoIncrement"].Value = true; // Enable the auto increment property for this column. // Column 2 (Name) ADOX.ColumnClass nameCol = new ADOX.ColumnClass(); nameCol.Name = "Name"; // The name of the column nameCol.ParentCatalog = catalog; nameCol.Type = ADOX.DataTypeEnum.adVarWChar; // Indicates a string value type. nameCol.DefinedSize = 60; // 60 characters max. // Column 3 (Surname) ADOX.ColumnClass surnameCol = new ADOX.ColumnClass(); surnameCol.Name = "Surname"; // The name of the column surnameCol.ParentCatalog = catalog; surnameCol.Type = ADOX.DataTypeEnum.adVarWChar; // Indicates a string value type. surnameCol.DefinedSize = 60; // 60 characters max. table.Columns.Append(idCol); // Add the Id column to the table. table.Columns.Append(nameCol); // Add the Name column to the table. table.Columns.Append(surnameCol); // Add the Surname column to the table. catalog.Tables.Append(table); // Add the table to our database. // Close the connection to the database after we are done creating it and adding the table to it. ADODB.Connection con = (ADODB.Connection)catalog.ActiveConnection; if (con != null && con.State != 0) con.Close(); } |
