How to Connect to a Ms Access File (Mdb) Using C#

How to connect to a MS Access file (mdb) using C#?

The simplest way to connect is through an OdbcConnection using code like this

using System.Data.Odbc;

using(OdbcConnection myConnection = new OdbcConnection())
{
    myConnection.ConnectionString = myConnectionString;
    myConnection.Open();

    //execute queries, etc

}

where myConnectionString is something like this

myConnectionString = @"Driver={Microsoft Access Driver (*.mdb)};" + 
"Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;

See ConnectionStrings

In alternative you could create a DSN and then use that DSN in your connection string

• Open the Control Panel - Administrative Tools - ODBC Data Source
  Manager
• Go to the System DSN Page and ADD a new DSN
• Choose the Microsoft Access Driver (*.mdb) and press END
• Set the Name of the DSN (choose MyDSN for this example)
• Select the Database to be used
• Try the Compact or Recover commands to see if the connection works

now your connectionString could be written in this way

myConnectionString = "DSN=myDSN;"

Connecting to a Microsoft Access database from C#

If you haven't heard of ConnectionString, it very handy website to find correct connection string for any provider.

https://www.connectionstrings.com/

One thing to note is, not all the provider driver may be installed on your computer, so you need to install them first before using that provider.

How C# connection to Access DB without MS Access?

You don't need to install the office setup in your system, but you do need to install either the ACE or the JET engine driver with the correct bit version to use Access files in your code. The 32 bit engine driver will not work with 64 bit OS version and vice versa.

Have a look here:

for download and instruction

https://www.microsoft.com/en-gb/download/details.aspx?id=13255

for access connecetion strings

https://www.connectionstrings.com/access/

MsAccess + C# connect to database

Try

myOleDbCommand.CommandText = "SELECT * FROM [User]";

User may be a reserved word in this case.

connect and read .MDB item with C#

To simply read a single field on your database table you could use an OleDbDataReader that could loop over the result and return the field required..

var myDataTable = new DataTable();
using (var conection = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" + "data source=C:\\menus\\newmenus\\menu.mdb;Password=****"))
{
     conection.Open();
     var query = "Select siteid From n_user";
     var command = new OleDbCommand(query, conection);
     var reader = command.ExecuteReader();
     while(reader.Read())
         textBox1.Text = reader[0].ToString();

 }

if you have just one record and just one field then a better solution is the method ExecuteScalar

     conection.Open();
     // A query that returns just one record composed of just one field
     var query = "Select siteid From n_user where userid=1";
     var command = new OleDbCommand(query, conection);
     int result = (int)command.ExecuteScalar();  // Supposing that siteid is an integer

Probably I should also mention that ExecuteScalar returns null if the query doesn't find a match for the userid, so it is better to be careful with the conversion here

     object result = command.ExecuteScalar();
     if( result != null)
        int userID  = (int)result;
        .....

Connecting a MS Access (.mdb) Database to a MVC3 Web Application

string connectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;
public void InsertRow(string connectionString, string insertSQL)
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        // The insertSQL string contains a SQL statement that
        // inserts a new row in the source table.
        OleDbCommand command = new OleDbCommand(insertSQL);

        // Set the Connection to the new OleDbConnection.
        command.Connection = connection;

        // Open the connection and execute the insert command.
        try
        {
            connection.Open();
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        // The connection is automatically closed when the
        // code exits the using block.
    }
}

---

Related Topics