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(); } |
Simply call CreateAccessDatabase(); to create a new database file in your application directory with the name mydb.mdb. You can tweak the method quite a bit if you want to provide the filename as a parameter to the method etc.
The two following snippets will allow you to add new users in the database (based on the table we created above) as well as retrieve data from it. This will hopefully be useful in giving you an idea how to insert and retrieve data from your newly made database.
Inserting data into your database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | public static void AddUser(string name, string surname) { string connectionString = string.Format("Provider={0}; Data Source={1}; Jet OLEDB:Engine Type={2}", "Microsoft.Jet.OLEDB.4.0", "mydb.mdb", 5); using (var con = new System.Data.OleDb.OleDbConnection(connectionString)) { con.Open(); // Open a connection to the database. string query = "INSERT INTO Users ([Name],[Surname]) VALUES (@name,@surname);"; // The query for adding a new row to our Users table. using (var command = new System.Data.OleDb.OleDbCommand(query, con)) { command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@surname", surname); command.ExecuteNonQuery(); } } } |
Usage example:
1 | AddUser("Mike", "Johnson"); |
Retrieving data from your database:
Keep in mind this is merely an example so this will only return the first row. If you have users in the database with the same name then you will need to use the OleDbDataReader class in order to read all the rows that your query will return.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | public static string GetSurname(string name) { string connectionString = string.Format("Provider={0}; Data Source={1}; Jet OLEDB:Engine Type={2}", "Microsoft.Jet.OLEDB.4.0", "mydb.mdb", 5); using (var con = new System.Data.OleDb.OleDbConnection(connectionString)) { con.Open(); // Open a connection to the database. string query = "Select Surname FROM Users WHERE name = @name;"; // The query for retriving the surname of the user with a specific name. using (var command = new System.Data.OleDb.OleDbCommand(query, con)) { command.Parameters.AddWithValue("@name", name); return (string)command.ExecuteScalar(); } } } |
Usage example:
1 2 | string surname = GetSurname("Mike"); Console.WriteLine(surname); // Prints "Johnson" |
Thank you so much :)
Please on the Solution Explorer set the References\ADOX at the Embed Interop Types from True to False in case you have errors at the ColumnClass. This is what happened to me using Visual Studio 2012.
Thanks Humberto!
Thank You So Much! Really helpful.
Is it Possible to give path for database
Thank You.
You can replace mydb.mdb with the full path if needed.
tanx
Since there were a few requests for a sample project, I’ve created one so you can take a closer look. I took the initiative of using the newer version of the files since one and a half year passed since this post was published. You can always reference the older versions if needed, I’ve tested both and it works exactly the same.
You can download the project at http://www.fluxbytes.com/?dl_name=MicrosoftAccessDatabaseSampleProject.zip
Keep in mind that you will need to recompile it as I’ve removed everything under /bin and /obj folders.
Let me know if you need further information.
I also would really appreciate a copy of the project.
Can you send me project code please? i needs like this project! Beforly Thanks a lot!
Can you send me the project files? I believe this will help me in doing my project.
I am new to C#. Appreciate your help. Thanks
<email removed for privacy reasons>
Best Regards,
Jason Wee
many thank’s.. it’s very helpfully.. search 2 days for completed this task..
thank you very much..